468,510 Members | 1,631 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,510 developers. It's quick & easy.

Datatype mismatch when ordering or searching field criteria

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 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, 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
  2. FROM ArcFilz;
Oct 9 '09 #1
9 1910
564 Expert 512MB
Hello rscheinberg,

Are you trying to do all this within Microsoft Access? Or is the database just in Microsoft Access and the front end is in Classic ASP?

Oct 9 '09 #2
The database is actually in Access and the front end will be in ASP. I couldn't get the code to work in ASP so I went directly to the database and am building a query there to get it right before I go back to the ASP page. I have a suspicion that it has something to do with the attempt to convert the text strings to integers, but I am at a loss.

Oct 9 '09 #3
564 Expert 512MB
Hello rscheinberg,

Can you tell us the tables that you’re using and the fields within the tables too? Also, if you could provide two rows of data from the tables so we can understand what is in them.

I believe that we can help you,
Oct 9 '09 #4
The only table I am accessing is called ArcFilz. And the only field I am calling is ProjectNumber, which is a text field. However here are 2 lines of data from the entire table. Thank you again for looking at this.

Expand|Select|Wrap|Line Numbers
  1. ID    ArchiveType    Container    ProjectNumber    ProjectName    Office    Location    ShelfNumber    FileType    Files    Department    ProjectManager    CheckedOut    CheckedOutDate    ReturnDate    Missing    Destroyed    Deleted    DeleteNote    Deleter    Archived    ArchivedBy    Entered    EnteredBy    ReviewDate
  2. 12172    Dead Files    Z-9999    010213300    Schilling Farms Lot 1 Site Plan-Greene    Memphis, TN    Toyota Plaza (5)        Binders, Guidelines, Maps    Test    Residential    05877                    09/23/04    9/23/2004    This was a test item.    lrk-net\tuckerd                    
  3. 13252    Dead Files    T2107    01P194600        Memphis, TN    Toyota Plaza (B)        Project Files    forms submitted by Rose Pollion                                            8/17/2005    05543    8/17/2005    05674    
Oct 9 '09 #5
564 Expert 512MB
So the first two numbers from the field "ProjectNumber" is the end part of a year? Both the records above are for the year 2001?
Oct 9 '09 #6
That is correct. The 3rd and 4th characters in the project number represent the last 2 digits of the year. Actually, the second row of data (with the "P" in it) does not follow that rule, so I am making those display as 1960 (before any of our projects began) to get them out of the mix. I am doing this with the IIF() statement. But you are getting the idea of what I am trying to do. Once I have the 2 characters from the project number, I am sticking either 19 or 20 in front of them depending on the year (using another IIF() statement) and then converting that string to a number using CInt(). That should give me a year in the yyyy format as intPN. The query does all that correctly. What I would do next is query for all records where intPN was between two numbers, say 1982 and 1988.

But even when I try to simply order by or filter the data in that field (intPN) in any way I get the Datatype mismatch error. I don't get an error if I am ordering or filtering by the ProjectNumber field itself.

It's curious.

Thanks again.
Oct 9 '09 #7
564 Expert 512MB
So is it the 1st and 2nd characters that are the last 2 digits of a year or the 3rd and 4th characters?
Oct 9 '09 #8
The 3rd and 4th characters. But I am testing for only those project numbers that have 7 or more characters and do not have letters in them. Those projects do not follow the pattern. In some records the project number is even blank. Here is some sample data that will show you what I have:
Expand|Select|Wrap|Line Numbers
  1. ID             PROJECTNUMBER            PROJECTNAME
  2. 1              0S9011000                 Project 1
  3. 2              009014100                 Project 2
  4. 3              009629000                 Project 3
  5. 4                                        Project 4
  6. 5              010016200                 Project 5
  7. 6              009103900                 Project 6
  8. 7              008500700                 Project 7
  9. 8              008806600                 Project 8
  10. 9              008911200                 Project 9
  11. 10             009203800                 Project 10
  12. 11             030103112                 Project 11
  13. 12             010203400                 Project 12
  14. 13             020005902                 Project 13
  15. 14             009709700                 Project 14
  16. 15             P148500                   Project 15
Oct 9 '09 #9
564 Expert 512MB
Hello rscheinberg,

Sorry for not getting back to you earlier with something but, I don’t really understand why you’re doing all this. To me it seems that you should have a field within your database that has the value of “ProjectBeganDate” if you’re going to hit the database many times querying for a project began date within a text field.

With that said maybe this can help you with the solution you’re looking for.

Expand|Select|Wrap|Line Numbers
  1. <%  
  2.     Set adoCon = Server.CreateObject("ADODB.Connection")  
  3.     adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Relative path to your Access database")   
  4.     Set rsData = Server.CreateObject("ADODB.Recordset")  
  6.     SQL = "SELECT ID, ProjectNumber, ProjectName FROM ArcFilz"
  7.     rsData.CursorType = 2  
  8.     rsData.LockType = 3  
  9.     rsData.Open SQL, adoCon  
  11.     Do Until (rsData.EOF) 
  12.         If (IsNumeric(rsData("ProjectNumber").value)) Then
  13.             If (Right(DatePart("yyyy",Date), 2) > Right(Left(rsData("ProjectNumber").value, 4), 2)) Then
  14.                 ProjectBeganDate = Left(DatePart("yyyy",Date), 2) & Right(Left(rsData("ProjectNumber").value, 4), 2)
  15.             Else
  16.                 ProjectBeganDate = (Left(DatePart("yyyy",Date), 2) - 1) & Right(Left(rsData("ProjectNumber").value, 4), 2)
  17.             End If
  18.         Else
  19.             ProjectBeganDate = "1960"
  20.         End If
  22.         Response.Write("Is " & rsData("ProjectName").value & " (ProjectNumber = " & rsData("ProjectNumber").value & ") between the range of years (1983-1987)? <b>" & ProjectBeganDate & " - ")
  23.         If ((ProjectBeganDate >= 1983) AND (ProjectBeganDate <= 1987)) Then
  24.             Response.Write("Yes")
  25.         Else
  26.             Response.Write("No")
  27.         End If
  28.         Response.Write("</b> <br />")
  30.         rsData.MoveNext 
  31.     Loop 
  32. %>  
Hope this helps,
Oct 12 '09 #10

Post your reply

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

Similar topics

2 posts views Thread by Stephen Briley | last post: by
2 posts views Thread by Steve Briley | last post: by
33 posts views Thread by Geoff Jones | last post: by
6 posts views Thread by rn5a | last post: by
reply views Thread by Snoopy33 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.