MS Access Report Stopped Working

Hi Experts,

I have date range report in MS Access that stopped working today.  The database is MSSQL 2008 R2, and on my local machine I have a backup copy  from March 2015.

The weird thing is if I run a date range between Feb 8 to Feb 14 against my March 2015 copy it works, but it does not work if I run it against today's copy. What could it be?

The error I am getting is  3464 -Data type mismatch in criteria expression

Thank you
APD TorontoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
The error type 'Data Type Mismatch' is returned when an expression tries to compare two different data types, such as #12/31/2010# > 'banana', or 'Tom Jones' = 42, or 5 = True.

Did a user enter a non-date value in an 'Enter a date here' input box?
Did any of the data types in the source table(s) change?
Is there a column that is Text where the values are really a date, number, or Yes/No, the query has an expression that converts to date/whatever, and a user finally entered a value such as 'banana' that cannot convert to date/whatever?
Oh I dunno.  Gremlins?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
APD TorontoSoftware DeveloperAuthor Commented:
I'm very cautios about validating input, and this maybe happens twice a year, but how do I pinpoint the problematic record(s)(/field(s)
Jim HornMicrosoft SQL Server Data DudeCommented:
Unfortunately that error message does not tell you the column that threw the error.
First try re-running it with entering correctly-formatted dates to see if that's the problem.
Then go into the report's RecordSource sql, then one-by-one eliminate columns until you figure out the one that threw the error.

Then tell us if it's as I stated above a char/nvarchar column that really should be a date, number, whatever.

Also, you can use WHERE ISDATE(column_name) = 0, ISNUMERIC, etc. and run the query to flush out any values that cannot convert to a valid date, number, etc.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

APD TorontoSoftware DeveloperAuthor Commented:
To be honest, before I noticed that sometime the problem is outside of the searched date range
John TsioumprisSoftware & Systems EngineerCommented:
those "hidden" problematic fields are a bit of a nuisance to find them .
My best advice is to run the query directly on MSSQL  Management Studio and if it keeps "insisting" then the solution is "divide and conquer"...
I mean try to isolate the problematic records by splitting the records in half (half before, half after)...and you keep splitting until you find the culprit.
Jeffrey CoachmanMIS LiasonCommented:
Post the code or expression where the criteria parameter is generated.
For example is the parameter in a query, ..or is it built in code?

...and just for fun:
Using the linked table manager, try refreshing the table links
Run the compact repair utility
Jeffrey CoachmanMIS LiasonCommented:
I'm confused.

You accepted 3 different solutions, it is not clear what the ultimate solution was here.

Please clarify..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.