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.

Expand|Select|Wrap|Line Numbers

 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.

Expand|Select|Wrap|Line Numbers

 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 "ProjectNumber" 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:

Expand|Select|Wrap|Line Numbers

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

 
<%  

    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~

Datatype mismatch when ordering or searching field criteria

re: Datatype mismatch when ordering or searching field criteria

re: Datatype mismatch when ordering or searching field criteria

re: Datatype mismatch when ordering or searching field criteria

re: Datatype mismatch when ordering or searching field criteria

re: Datatype mismatch when ordering or searching field criteria

re: Datatype mismatch when ordering or searching field criteria

re: Datatype mismatch when ordering or searching field criteria

re: Datatype mismatch when ordering or searching field criteria

re: Datatype mismatch when ordering or searching field criteria

Similar ASP / Active Server Pages bytes

/bytes/development

/bytes/it

/bytes/about