[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criter

[dcusick]

Okay, I need some serious help here... I created an App in VB6.0 sp5. The app basically returns a list of qualified people, depending on the information you select, from a MS Access 97 DB. The App has always been working fine, on my machine, on other peoples' machines in the company. However when I started installing the app on other people's Laptops outside the builiding, I started getting an error message. The laptops do not have MS Access installed. I'm using ADO 2.1 to get to the database. The error that arises is "[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression". Since it works fine on my machine and I can't duplicate the error, I have to kind of guess where the error is. The query I use to get the data is pretty big, but there's one or two lines that I suspect...

strSQL2 = strSQL2 & " OR ((EnrollmentDate Is Not Null) AND " _
            & "((Person.typeCode)=1) AND ((Person.locationID) Is Null) AND " _
            & "((DateDiff('d',Now(),cdate(month(EnrollmentDate) & '/' & day(EnrollmentDate) & '/' & year(now())))) BETWEEN -90 And " _
            & IIf(intListType = 7, 7, 30) & ")) "

This is part of the where clause of my query. Could this be the problem? could be using cdate in a DateDiff function cause problems? Any help would be greatly appreciated.. Thanks...

Doug

 

[CCLINT]

Is the db also located on the laptops, or do they link to a server?
They probably have an older newer version of the ODBC driver.

What happens when you use:

Not EnrollmentDate Is Null

Do any EnrollmentDates contain NULLs?

I would update to the OLEDB provider instead.
There is an option (a property of the provider set in the prior to opening the connection) to ignore the errors cause by NULLS in actions queries when usíng the newer provider (the older ones ignored them by default in action queries)

 

[CCLINT]

I just say something else:
What is the Date format setting in the Control Panel - Country Settings for those failing laptops, compared to yours?

 

[dcusick]

Thanks for the response... The Regional Settings are the same, so I don't think that is the issue.. As for the ODBC drivers being newer, I don't think that is the case, but I'm not sure. I have a newer version of the MDAC on my machine than the one that we install on the Laptops. So I would assume if anything I have the newer ODBC Drivers... Could this possibly be on of the issues? An older version of this application used to always work, even on the laptops. I seems as if an update somewhere knocked it out of whack...

 

[CCLINT]

Start removing potions of the sql criteria, a piece at a time, to see where the error actually is, or rather, is not anymore - this is the first most important step to take - Isolate the place where the error is happening.

Then, you might as well change to the OLEDB provider if you are using ADO. I don't use the ODBC driver for JET/ACCESS so I not sure if the mentioned property is available.

Change to the JET OLEDB 4.0 Provider to start with (make sure MDAC 2.5 or JET 4 SP 3 or higher have been intsalled before doing so. (either MDAC 2.5 must have been installed on the PC at one time or another, or if a higher version was installed first, the JET 4 Sp has been installed - newest SP being SP 7.
Then just change the connection string (plenty of examples here) if you are using ADO.

BTW, the ODBC drivers for JET, arenot in MDAC after version 2.1 and the JET OLEDB providers are not shippen in MDAC after version 2.5.

There is a property called:
Jet OLEDB:Global Partial Bulk Ops
After setting this to 1 (after setting the provider but prior to opening the connection) then bulk update NULL errors, such as on date fields, are ignored as with previous versions of JET drivers, or even the JET 3.52 provider.

 

[dcusick]

CClint,

Thanks for the responses, I really do appreciate it. What I did was uninstall my APP and then install a previous version of the app. The only real difference between the two apps is the SQL statement that I mentioned with the date calculations... The old version of the App worked just fine. For both installation packages, I used the same set up in WISE 9.02. So I don't think it is an actual driver issue or dll. It must be in my SQL statement... Thanks again for the help...

 

[CCLINT]

If the drivers on the client machine are newer, the setup program will not replace them with the older ones in the setup package...

 

[dcusick]

Wow... You'll never guess what this problem was!!! It had to do with the data and how the SQL statement was brought up... The problem arose when there was a date of 2/29/1996. When I created the date as of current year, 2/29/2003, it errored out, since 2003 is not a leap year. Unbelievable!!! Thanks for all your help CClint. It's the little things that you can never really think of!