Datatype mismatch when ordering or searching criteria in field

I am working in Access 2007 attempting to grab 2 characters from a text field named ProjectNumber. After determining what 2 digits to add in front to make it a year, I need to do that. I have done that in the code below to create a value for "intPN". I then need that value to become a number so that I can compare it to a range of years (1983-1987). The code below returns the 4 character year as intended. But I get a Datatype mismatch in criteria expression error whenever I try to order or use a critera (where) based on that field. I have been playing with this code for 2 days and am ready to go postal if the Datatype mismatch error pops up too many more times. So any help would really be appreciated. It is probably something really simple and I am too thick to see it. Thanks.

re: Datatype mismatch when ordering or searching criteria in field

Are you trying to compare numbers to dates? I can't tell without seeing where the result is being used.

re: Datatype mismatch when ordering or searching criteria in field

Actually I am comparing to a number. So that I could say this:

But I can't even do this:

without the Datatype mismatch error.

Thanks.

re: Datatype mismatch when ordering or searching criteria in field

The only way I have been able to create an error with this is when the call to mid returns a letter.

re: Datatype mismatch when ordering or searching criteria in field

That is interesting. I've tried it on a different computer and get the same results whenever I try to order or filter the data by the intPN field. So I think I've ruled out the Access application as the problem. There is no problem with the ProjectNumber field, just intPN.

re: Datatype mismatch when ordering or searching criteria in field

You cannot use the alias (intPN) of a formula from your SELECT clause in either the ORDER BY or WHERE clauses in Jet SQL.

If you try to design this in the QBE window you will find it easier. Access will use the formula in the ORDER BY & WHERE clauses for you.

re: Datatype mismatch when ordering or searching criteria in field

You may consider a little simplification of the code :
See ANSI Standards in String Comparisons for an explanation of the filtering.

If you were to post some example data it may be possible to simplify it further (or direct it more precisely maybe).

re: Datatype mismatch when ordering or searching criteria in field

You could of course, encapsulate the complicated formula into a subquery (See Subqueries in SQL) and thereby make including it in the WHERE and ORDER BY clauses much more straightforward :

re: Datatype mismatch when ordering or searching criteria in field

I really appreciate your help. I don't know how to do this without using the alias. To simplify what I am doing you could have a table with just an auto ID field and a ProjectNumber field (text) and a Project Name field (text) with the data in the attached spreadsheet:

Attached Files

ArcFilz_SampleData.zip (6.9 KB, 3 views)

re: Datatype mismatch when ordering or searching criteria in field

I'm a little confused. Maybe I need to explain myself better :

1. I just want some example data posted of the sorts of things that could be found in the [ProjectNumber] field. An explanation of the form it takes wouldn't hurt if you can too.
2. It's not necessary to do it as an attachment (in fact it's far better not), but if it must be then 2007 format is not very standard. I myself don't have it, so I can't even open what you've attached.
3. The example data needn't be overly voluminous. 20 or 30 lines would be more than enough. Even fewer if the format is quite standard.
4. I'm curious that you say you don't know how to do it without the aliases. I thought I'd posted an example (using a subquery) that showed exactly how you could. Does that not work for you?

re: Datatype mismatch when ordering or searching criteria in field

I really do appreciate your help. I did try the subquery, but got an error in Access when I tried it. "Syntax error in FROM clause". I have not done a lot with subqueries. Sorry about the Access 2007 thing. Here is the data. As you can see some of the project numbers are shorter, some have letters in them, and some are even blank. These do not fit the pattern and I want to basically set the year of all those to 1960 as there will be no filters looking for those dates since they are before our company was formed. So basically I am throwing them out for now, but I might look at them in a different way later. Also any number pair that is over 80 should have a 19 apended to the front and lower numbers should have a 20 appended before them. That is how I am dealing with the 20th and 21st century issues. I hope this is clearer.

re: Datatype mismatch when ordering or searching criteria in field

Would it be true to say that all the valid [ProjectNumber]s are 9 digits long and with the YY value at positions #3 & #4? If so then we can simplify the selection somewhat. Let me know.

Could you post the actual SQL you tried that gave the error "Syntax error in FROM clause" please. I've rechecked what I posted and I can't see anything wrong there.

re: Datatype mismatch when ordering or searching criteria in field

All the valid YY values are in positions #3 & #4, but some valid Project Numbers may have only 7 digits. So they would be either 7 or 9 digits in length. I don't think any have 8.

Thanks again.

Here is the code. I just copied and pasted it into the Access SQL window.

re: Datatype mismatch when ordering or searching criteria in field

Ah, My bad.

I left a closing parenthesis off the end of line #6.

It should have been :

re: Datatype mismatch when ordering or searching criteria in field

An alternative set (less tidy as two different lengths aren't checked easily) to reflect the 7 or 9 digit spec :