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. - SELECT ArcFilz.ProjectNumber,
-
IIf(Len([ProjectNumber])>=7 And [ProjectNumber] Not Like "*P*" And [ProjectNumber] Not Like "*S*",
-
IIf(CInt(Mid([ProjectNumber],3,2))>80,
-
CInt("19"+Mid([ProjectNumber],3,2)),
-
CInt("20"+Mid([ProjectNumber],3,2))),
-
1960) AS intPN
-
FROM ArcFilz;
An alternative set (less tidy as two different lengths aren't checked easily) to reflect the 7 or 9 digit spec : -
SELECT ProjectNumber,
-
intPN
-
-
FROM (SELECT ProjectNumber,
-
IIf(Len([ProjectNumber]) In(7,9) And [ProjectNumber] Like '#######*',
-
CInt('19' & Mid([ProjectNumber],3,2)) + IIf(Mid([ProjectNumber],3,2)>'80',0,100),
-
1960) AS intPN
-
FROM ArcFilz) AS subQry
-
-
WHERE intPN Between 1983 And 1987
-
-
ORDER BY intPN
14 1854
Are you trying to compare numbers to dates? I can't tell without seeing where the result is being used.
Actually I am comparing to a number. So that I could say this: - WHERE intPN BETWEEN 1984 and 1988
But I can't even do this:
without the Datatype mismatch error.
Thanks.
The only way I have been able to create an error with this is when the call to mid returns a letter.
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.
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.
NeoPa 32,556
Expert Mod 16PB
You may consider a little simplification of the code : - SELECT ArcFilz.ProjectNumber,
-
IIf(Len([ProjectNumber])>=7 And [ProjectNumber] Not Like '*[PS]*',
-
CInt('19' & Mid([ProjectNumber],3,2)) + IIf(Mid([ProjectNumber],3,2)>'80',0,100,
-
1960) AS intPN
-
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).
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 : - SELECT ProjectNumber,
-
intPN
-
-
FROM (SELECT ProjectNumber,
-
IIf(Len([ProjectNumber])>=7 And [ProjectNumber] Not Like '*[PS]*',
-
CInt('19' & Mid([ProjectNumber],3,2)) +
-
IIf(Mid([ProjectNumber],3,2)>'80',0,100,
-
1960) AS intPN
-
FROM ArcFilz) AS subQry
-
-
WHERE intPN Between 1983 And 1987
-
-
ORDER BY intPN
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:
NeoPa 32,556
Expert Mod 16PB
I'm a little confused. Maybe I need to explain myself better : - 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.
- 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.
- 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.
- 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?
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. - 1 0S9011000 Project 1
-
2 009014100 Project 2
-
3 009629000 Project 3
-
4 Project 4
-
5 010016200 Project 5
-
6 009103900 Project 6
-
7 008500700 Project 7
-
8 008806600 Project 8
-
9 008911200 Project 9
-
10 009203800 Project 10
-
11 030103112 Project 11
-
12 010203400 Project 12
-
13 020005902 Project 13
-
14 009709700 Project 14
-
15 P148500 Project 15
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.
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. - SELECT ProjectNumber,
-
intPN
-
-
FROM (SELECT ProjectNumber,
-
IIf(Len([ProjectNumber])>=7 And [ProjectNumber] Not Like '*[PS]*',
-
CInt('19' & Mid([ProjectNumber],3,2)) +
-
IIf(Mid([ProjectNumber],3,2)>'80',0,100,
-
1960) AS intPN
-
FROM ArcFilz) AS subQry
-
-
WHERE intPN Between 1983 And 1987
-
-
ORDER BY intPN
NeoPa 32,556
Expert Mod 16PB
Ah, My bad.
I left a closing parenthesis off the end of line #6.
It should have been : - IIf(Mid([ProjectNumber],3,2)>'80',0,100),
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 : -
SELECT ProjectNumber,
-
intPN
-
-
FROM (SELECT ProjectNumber,
-
IIf(Len([ProjectNumber]) In(7,9) And [ProjectNumber] Like '#######*',
-
CInt('19' & Mid([ProjectNumber],3,2)) + IIf(Mid([ProjectNumber],3,2)>'80',0,100),
-
1960) AS intPN
-
FROM ArcFilz) AS subQry
-
-
WHERE intPN Between 1983 And 1987
-
-
ORDER BY intPN
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
...
|
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...
|
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.
...
|
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...
|
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...
|
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...
|
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:...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
| |