By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,856 Members | 2,004 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,856 IT Pros & Developers. It's quick & easy.

Datatype mismatch when ordering or searching criteria in field

P: 11
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT ArcFilz.ProjectNumber,
  2.        IIf(Len([ProjectNumber])>=7 And [ProjectNumber] Not Like "*P*" And [ProjectNumber] Not Like "*S*",
  3.            IIf(CInt(Mid([ProjectNumber],3,2))>80,
  4.                CInt("19"+Mid([ProjectNumber],3,2)),
  5.                CInt("20"+Mid([ProjectNumber],3,2))),
  6.            1960) AS intPN
  7. FROM   ArcFilz;
Oct 9 '09 #1

✓ answered by NeoPa

An alternative set (less tidy as two different lengths aren't checked easily) to reflect the 7 or 9 digit spec :
Expand|Select|Wrap|Line Numbers
  1. SELECT   ProjectNumber,
  2.          intPN
  3.  
  4. FROM    (SELECT ProjectNumber,
  5.                 IIf(Len([ProjectNumber]) In(7,9) And [ProjectNumber] Like '#######*',
  6.                     CInt('19' & Mid([ProjectNumber],3,2)) + IIf(Mid([ProjectNumber],3,2)>'80',0,100),
  7.                     1960) AS intPN
  8.          FROM   ArcFilz) AS subQry
  9.  
  10. WHERE    intPN Between 1983 And 1987
  11.  
  12. ORDER BY intPN

Share this Question
Share on Google+
14 Replies


Expert 100+
P: 1,287
Are you trying to compare numbers to dates? I can't tell without seeing where the result is being used.
Oct 9 '09 #2

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

Expand|Select|Wrap|Line Numbers
  1. WHERE intPN BETWEEN 1984 and 1988
But I can't even do this:

Expand|Select|Wrap|Line Numbers
  1. ORDER BY intPN
without the Datatype mismatch error.

Thanks.
Oct 9 '09 #3

Expert 100+
P: 1,287
The only way I have been able to create an error with this is when the call to mid returns a letter.
Oct 9 '09 #4

P: 11
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.
Oct 9 '09 #5

NeoPa
Expert Mod 15k+
P: 31,314
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.
Oct 9 '09 #6

NeoPa
Expert Mod 15k+
P: 31,314
You may consider a little simplification of the code :
Expand|Select|Wrap|Line Numbers
  1. SELECT ArcFilz.ProjectNumber,
  2.        IIf(Len([ProjectNumber])>=7 And [ProjectNumber] Not Like '*[PS]*',
  3.            CInt('19' & Mid([ProjectNumber],3,2)) + IIf(Mid([ProjectNumber],3,2)>'80',0,100,
  4.            1960) AS intPN
  5. FROM   ArcFilz
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).
Oct 9 '09 #7

NeoPa
Expert Mod 15k+
P: 31,314
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   ProjectNumber,
  2.          intPN
  3.  
  4. FROM    (SELECT ProjectNumber,
  5.                 IIf(Len([ProjectNumber])>=7 And [ProjectNumber] Not Like '*[PS]*',
  6.                     CInt('19' & Mid([ProjectNumber],3,2)) +
  7.                     IIf(Mid([ProjectNumber],3,2)>'80',0,100,
  8.                     1960) AS intPN
  9.          FROM   ArcFilz) AS subQry
  10.  
  11. WHERE    intPN Between 1983 And 1987
  12.  
  13. ORDER BY intPN
Oct 9 '09 #8

P: 11
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
File Type: zip ArcFilz_SampleData.zip (6.9 KB, 54 views)
Oct 9 '09 #9

NeoPa
Expert Mod 15k+
P: 31,314
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?
Oct 9 '09 #10

P: 11
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.
Expand|Select|Wrap|Line Numbers
  1.  1  0S9011000   Project 1
  2.  2  009014100   Project 2
  3.  3  009629000   Project 3
  4.  4              Project 4
  5.  5  010016200   Project 5
  6.  6  009103900   Project 6
  7.  7  008500700   Project 7
  8.  8  008806600   Project 8
  9.  9  008911200   Project 9
  10. 10  009203800   Project 10
  11. 11  030103112   Project 11
  12. 12  010203400   Project 12
  13. 13  020005902   Project 13
  14. 14  009709700   Project 14
  15. 15  P148500     Project 15
Oct 9 '09 #11

NeoPa
Expert Mod 15k+
P: 31,314
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.
Oct 9 '09 #12

P: 11
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT   ProjectNumber, 
  2.          intPN 
  3.  
  4. FROM    (SELECT ProjectNumber, 
  5.              IIf(Len([ProjectNumber])>=7 And [ProjectNumber] Not Like '*[PS]*', 
  6.                  CInt('19' & Mid([ProjectNumber],3,2)) + 
  7.                  IIf(Mid([ProjectNumber],3,2)>'80',0,100, 
  8.                  1960) AS intPN 
  9.          FROM   ArcFilz) AS subQry 
  10.  
  11. WHERE    intPN Between 1983 And 1987 
  12.  
  13. ORDER BY intPN
Oct 9 '09 #13

NeoPa
Expert Mod 15k+
P: 31,314
Ah, My bad.

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

It should have been :
Expand|Select|Wrap|Line Numbers
  1.                  IIf(Mid([ProjectNumber],3,2)>'80',0,100),
Oct 9 '09 #14

NeoPa
Expert Mod 15k+
P: 31,314
An alternative set (less tidy as two different lengths aren't checked easily) to reflect the 7 or 9 digit spec :
Expand|Select|Wrap|Line Numbers
  1. SELECT   ProjectNumber,
  2.          intPN
  3.  
  4. FROM    (SELECT ProjectNumber,
  5.                 IIf(Len([ProjectNumber]) In(7,9) And [ProjectNumber] Like '#######*',
  6.                     CInt('19' & Mid([ProjectNumber],3,2)) + IIf(Mid([ProjectNumber],3,2)>'80',0,100),
  7.                     1960) AS intPN
  8.          FROM   ArcFilz) AS subQry
  9.  
  10. WHERE    intPN Between 1983 And 1987
  11.  
  12. ORDER BY intPN
Oct 9 '09 #15

Post your reply

Sign in to post your reply or Sign up for a free account.