Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

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.
0
APD Toronto
Asked:
APD Toronto
3 Solutions
 
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
 
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 TorontoAuthor 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
APD TorontoAuthor 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 TorontoAuthor Commented:
Supposedly, there was a network error and there was a partially saved record.

Thank you all.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now