Datatype mismatch when ordering or searching field criteria

[rscheinberg]

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.

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;

[CroCrew]

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?

CroCrew~

[rscheinberg]

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.

Thanks.

[CroCrew]

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,
CroCrew~

[rscheinberg]

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.

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

[CroCrew]

So the first two numbers from the field "ProjectNum ber" is the end part of a year? Both the records above are for the year 2001?

[rscheinberg]

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.

[CroCrew]

So is it the 1st and 2nd characters that are the last 2 digits of a year or the 3rd and 4th characters?

[rscheinberg]

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:

ID             PROJECTNUMBER            PROJECTNAME
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
 

[CroCrew]

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 “ProjectBeganDa te” 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.

<%  
    Set adoCon = Server.CreateObject("ADODB.Connection")  
    adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Relative path to your Access database")   
    Set rsData = Server.CreateObject("ADODB.Recordset")  
 
    SQL = "SELECT ID, ProjectNumber, ProjectName FROM ArcFilz"
    rsData.CursorType = 2  
    rsData.LockType = 3  
    rsData.Open SQL, adoCon  
 
    Do Until (rsData.EOF) 
        If (IsNumeric(rsData("ProjectNumber").value)) Then
            If (Right(DatePart("yyyy",Date), 2) > Right(Left(rsData("ProjectNumber").value, 4), 2)) Then
                ProjectBeganDate = Left(DatePart("yyyy",Date), 2) & Right(Left(rsData("ProjectNumber").value, 4), 2)
            Else
                ProjectBeganDate = (Left(DatePart("yyyy",Date), 2) - 1) & Right(Left(rsData("ProjectNumber").value, 4), 2)
            End If
        Else
            ProjectBeganDate = "1960"
        End If
 
        Response.Write("Is " & rsData("ProjectName").value & " (ProjectNumber = " & rsData("ProjectNumber").value & ") between the range of years (1983-1987)? <b>" & ProjectBeganDate & " - ")
        If ((ProjectBeganDate >= 1983) AND (ProjectBeganDate <= 1987)) Then
            Response.Write("Yes")
        Else
            Response.Write("No")
        End If
        Response.Write("</b> <br />")
 
        rsData.MoveNext 
    Loop 
%>  
 

Hope this helps,
CroCrew~