Query returning unexpected error

Hi Experts,

I have a Access application that is connecting the SQL Server Express 2008 R2.

They use this application every day, through the day. Where I, as the developer, have a copy of the database from March 2014.

Now, when the client is trying to generate a report, which they do on a weekly basis, the following error comes up:

Run-time error '3464':

Data type mismatch in criteria expression

Open in new window


For this application, all queries are in the UI. I am not using stored procedures, triggers, etc., all I am storing within SQL server is my tables.

The report uses a date range, so given that I have a March copy of the database, I am using January 1 to 31, 2014 for my criteria. So, I did a count of the number of records within this date range, and my database copy returned 512 records. I backed up my version of the database, and restored a fresh version in my environment - the record count is also 512, so I am working relatively with the same data.

However, if I run the report for January 1 to 31  against my March database, I get no errors, but when I ran the same Jan 1-31 against today's database, I get the above error. This is on my environment, so code changes are not possible by third party. Database changes are, but highly doubtful.

Even last week's report range, which worked, no longer does.

What could it be? My only suspicion that I am using Express, which I know the database limit is 1 GB, but under database property I see that the size is 90.50 MB, but space available is 0.91MB... Could that be something?

Also, it maybe unrelated, but what is number of users under the same page?

Any ideas would be appreciated.
APD TorontoSoftware DeveloperAsked:
Who is Participating?
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.

Eric ShermanAccountant/DeveloperCommented:
Generally that error message is pretty straightforward meaning that the criteria for a column or columns does not match the data type of the underlying fields.  That's what I would check first.  Also check to make sure the data type of each pair of joined fields in the query is the same.

Would need to see your application connected to SQL in order to truly investigate your problem.

ET
0

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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>For this application, all queries are in the UI
For starters, show us the queries behind this UI.

>Data type mismatch in criteria expression
Break into any code window and do a Tools:Options, Break on all errors.
Then run your report again, and when it errors out it should show a dialog box that allows you to go into Debug mode, right to the line that throws the error.

Data Type mismatch means somewhere tried to compare an orange to a banana, or in this case a string to a date, number to date, date to number, whatever.  Since this only happens based on user-entered date range, I'm going to go out on a limb here and say that somebody designed a column as a text field, where it really holds numbers, and finally someone put a non-number value into it.

>but space available is 0.91MB.
Nothing to do with space available.
0
APD TorontoSoftware DeveloperAuthor Commented:
I have 2 very long queries, but with cutting code, I have isolated to the following code:

SELECT fldBookLoc
FROM tblReservations

WHERE ((fldTripType='T') Or (fldTripType='N')) AND (fldCommStat='O') AND (fldArchived=False) AND (DateValue([tblReservations].[fldPickupDate]) + TimeValue(NZ([tblReservations].[fldPickupTime], #00:00#)) BETWEEN #01/01/2014 7:00:00 AM# AND #01/31/2014 7:00:00 PM#)

GROUP BY fldBookLoc, fldAgent, fldTripType, fldCommStat

Open in new window


Actually, when I remove the last line I get no error.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

APD TorontoSoftware DeveloperAuthor Commented:
Actually, here are the complete queries, and the second (sub-query) is erroring out, so I'm trying to isolate the line. then field.  

***main query***

SELECT DISTINCT [tblReservations].[fldUserInit] & ' ' & ([tblReservations].[fldReservID]) & ' ' & [tblProducts].[fldDisplayAs] AS [Booking #], 
[tblAgents].[fldFName] & ' ' & Left([tblAgents].[fldLName],1) & '.' AS Agent, 
tblLocations.fldLocation, tblReservations.fldPickupDate, tblReservations.fldPickupTime, 
tblReservations.[fldPickupDate] & ' ' & tblReservations.[fldPickupTime] AS [DateTime], 
tblReservations.fldLName, tblReservations.fldRoomNo, tblReservations.fldFamQty, 
tblReservations.fldAdultQty, tblReservations.fldSenQty, tblReservations.fldStudQty, 
tblReservations.fldChildQty, tblReservations.fldInfQty, tblReservations.fldDeposit, 
tblReservations.fldCommStat, tblReservations.fldTripType, qryBal.fldCommBal,
  IIf([tblReservations].[fldFamQty]>0,[tblReservations].[fldFamQty]*[tblReservations].[fldFamComm],0) 
  +IIf([tblReservations].[fldAdultQty]>0,[tblReservations].[fldAdultQty]*[tblReservations].[fldAdultComm],0)
  +IIf([tblReservations].[fldSenQty]>0,[tblReservations].[fldSenQty]*[tblReservations].[fldSenComm],0) 
  +IIf([tblReservations].[fldStudQty]>0,[tblReservations].[fldStudQty]*[tblReservations].[fldStudComm],0) 
  +IIf([tblReservations].[fldChildQty]>0,[tblReservations].[fldChildQty]*[tblReservations].[fldChildComm],0) 
  +IIf([tblReservations].[fldInfQty]>0,[tblReservations].[fldInfQty]*[tblReservations].[fldInfComm],0) 
  -IIf([tblReservations].[fldDeposit]>0,[tblReservations].[fldDeposit],0) AS [$ Comm Owing] 

FROM tblAgents, tblLocations, qryCommBalancesDateTime AS qryBal,
  tblReservations INNER JOIN tblProducts ON tblReservations.fldProductID = tblProducts.fldProdID 

WHERE (tblReservations.fldCommStat = 'O') AND ((tblReservations.fldTripType = 'T') 
  Or (tblReservations.fldTripType = 'N')) AND (tblReservations.fldArchived = False) 
  AND (tblReservations.fldAgent = [tblAgents].[fldAgentID]) 
  AND (tblReservations.fldAgent = [qryBal].[fldAgent]) 
  AND (tblReservations.fldBookLoc = [tblLocations].[fldLocID]) 
  AND (tblReservations.fldBookLoc = [qryBal].[fldBookLoc]) 
  AND (tblReservations.fldTripType = [qryBal].[fldTripType]) 
  AND (DateValue([tblReservations].[fldPickupDate]) 
  + TimeValue(NZ([tblReservations].[fldPickupTime], #00:00#)) 
  BETWEEN #07/28/2014 7:00:00 AM# AND #08/04/2014 7:00:00 PM#)  AND (fldCommBal >= 0);


***qryCommBalancesDateTime (sub-query)***

SELECT fldBookLoc, fldAgent, fldTripType, fldCommStat, 
Sum(IIf([fldFamQty]>0,[fldFamQty]*[fldFamComm],0) 
+IIf([fldAdultQty]>0,[fldAdultQty]*[fldAdultComm],0)
+IIf([fldSenQty]>0,[fldSenQty]*[fldSenComm],0)
+IIf([fldStudQty]>0,[fldStudQty]*[fldStudComm],0)
+IIf([fldChildQty]>0,[fldChildQty]*[fldChildComm],0)
+IIf([fldInfQty]>0,[fldInfQty]*[fldInfComm],0)
-IIf([fldDeposit]>0,[fldDeposit],0)) AS fldCommBal
FROM tblReservations

WHERE ((fldTripType='T') Or (fldTripType='N')) 
AND (fldCommStat='O') 
AND (fldArchived=False) 
AND (DateValue([tblReservations].[fldPickupDate]) + TimeValue(NZ([tblReservations].[fldPickupTime], #00:00#)) 
BETWEEN #01/01/2014 7:00:00 AM# AND #01/31/2014 7:00:00 PM#)

GROUP BY fldBookLoc, fldAgent, fldTripType, fldCommStat
ORDER BY fldBookLoc, fldAgent, fldTripType DESC;

Open in new window


I understand that this may be if the user entered letters instead of numbers, but I am very thorough in my data validation, and this has been in place for 3 years. However, even if something slipt by, why would it affect past date ranges? In any case, how would I isolate this if it is indeed data?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You may have a Null value in one of those IIF fields:

SELECT fldBookLoc, fldAgent, fldTripType, fldCommStat, 
Sum(IIf([fldFamQty]>0,[fldFamQty]*[fldFamComm],0) 
+IIf([fldAdultQty]>0,[fldAdultQty]*[fldAdultComm],0)
+IIf([fldSenQty]>0,[fldSenQty]*[fldSenComm],0)
+IIf([fldStudQty]>0,[fldStudQty]*[fldStudComm],0)
+IIf([fldChildQty]>0,[fldChildQty]*[fldChildComm],0)
+IIf([fldInfQty]>0,[fldInfQty]*[fldInfComm],0)
-IIf([fldDeposit]>0,[fldDeposit],0)) AS fldCommBal
FROM tblReservations

Open in new window


Try wrapping them in Nz:

SELECT fldBookLoc, fldAgent, fldTripType, fldCommStat,
Sum(IIf(Nz([fldFamQty],0)>0,[fldFamQty]*[fldFamComm],0)
+IIf(Nz([fldAdultQty],0)>0,[fldAdultQty]*[fldAdultComm],0)
etc etc

Or you may have NULL values in one of the fields in the Criteria fields - check your table for NULL values in fldPickupDate and fldPickupTime, fldCommStats, etc.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also:
In any case, how would I isolate this if it is indeed data?
IMO, you run the queries in SQL Server Management Studio, which bypasses Access to determine if it's Access or the Data. You would have to change the query to accommodate SQL language - for example, the Date Delimiters in SQL are single quotes, and Yes/No fields are actually Bit fields, so you don't use False or True, but rather 0 or <>0 (or -1).
0
APD TorontoSoftware DeveloperAuthor Commented:
Supposedly, there was a network error and there was a partially saved record.

Thank you all.
0
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 SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.