473,320 Members | 1,870 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.
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

14 1854
ChipR
1,287 Expert 1GB
Are you trying to compare numbers to dates? I can't tell without seeing where the result is being used.
Oct 9 '09 #2
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
ChipR
1,287 Expert 1GB
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
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
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, 81 views)
Oct 9 '09 #9
NeoPa
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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

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

Similar topics

2
by: Stephen Briley | last post by:
For some reason, my posts are scrubbed as attachments. Lets hope that sending from the yahoo account works. I'm new to Python and I'm trying to do some database work with MS Access, but I can't...
2
by: Steve Briley | last post by:
I'm new to Python and I'm trying to do some database work with MS Access, but I can't seem to get around a "datatype mismatch error".  Here's an example table that I'm working with... ...
3
by: Laurel | last post by:
this is driving me crazy. i need to use a form control as a criteria in a select query, and the control's value is set depending upon what a user selects in an option group on the form. the query...
1
by: Ken | last post by:
I wrote a function to use in queries that takes a date and adds or subtracts a certain length time and then returns the new value. There are times when my function needs to return Null values. ...
33
by: Geoff Jones | last post by:
Hiya I have a DataTable containing thousands of records. Each record has a primary key field called "ID" and another field called "PRODUCT" I want to retrieve the rows that satisy the following...
6
by: rn5a | last post by:
I am inserting records in a MS-Access database table. The data type of one of the columns named *OrderDate* in the DB table is Date/Time. This is the SQL query I am using to insert the records in...
2
by: chirag1989 | last post by:
I m havin an error of datatype mismatch actual here i m askin user to input the code in text box and then searchin the record havin that code in database the problem is the code field Bnum is of...
0
by: Snoopy33 | last post by:
I have a query that builds a date from a text field with the date imput as yyyymmdd in the query, so i have to piece it together in my expression as follows: exp:...
9
by: rscheinberg | last post by:
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.