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:
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.
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 expressionFor 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Actually, here are the complete queries, and the second (sub-query) is erroring out, so I'm trying to isolate the line. then field.
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?
***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;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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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).
ASKER
Supposedly, there was a network error and there was a partially saved record.
Thank you all.
Thank you all.
ASKER
Open in new window
Actually, when I remove the last line I get no error.