Criteria issue with a DateDiff

[Paul K]

I currently have a query that calculates the time difference between a notifed date/time and arrival date/time along with a number of additional pieces of information incuding type of structure. I have date and time as separate fields in the main table, so first have to combine them and then subtract the combined fields to come up with a response time. Here is the code:

PARAMETERS [forms].[frm date selection].[start date] DateTime, [forms].[frm date selection].[end date] DateTime;
SELECT tblIncidents.[Incident Number], tblIncidents.[Run Number], tblIncidents.Structure, tblIncidents.[Notified Date], tblIncidents.[Notified Time], tblIncidents.[Arrival Date], tblIncidents.[Arrival Time], [Notified Date] & " " & [Notified Time] AS Notified, [Arrival Date] & " " & [Arrival Time] AS Arrival, (DateDiff("N",[Notified],[Arrival])) AS Response
FROM tblIncidents
WHERE (((tblIncidents.[Run Number])=1) AND ((tblIncidents.[Notified Date]) Between ([forms].[frm date selection].[start date]) And ([forms].[frm date selection].[end date])));

From this query, I do another query that selects a particular type of structure. That works fine. Here is that code.

SELECT [q Response Time report - detail].[Incident Number], [q Response Time report - detail].Structure, [q Response Time report - detail].[Notified Date], [q Response Time report - detail].Response
FROM [q Response Time report - detail]
WHERE ((([q Response Time report - detail].Structure)="sfd"));

I then create a third query that selects some of the columns. Here is that code:

SELECT [q Response Time report - detail].[Incident Number], [q Response Time report - detail].Structure, [q Response Time report - detail].[Notified Date], [q Response Time report - detail].Response
FROM [q Response Time report - detail]
WHERE ((([q Response Time report - detail].Structure)="sfd") AND (([q Response Time report - detail].Response)>120));

My problem is when I try to put in a criteria for the response time of >120, I get the error message "Data Type mismatch in criteria expression". No matter what I do I can't seem to fix it. I've added [Response] as a parameter for Integer, but that didn't work, so I took that out. If I take out >120 as a criteria under Response I get the full list, so know my problem has something to do with the >120 but I don't know where else to look.

Thanks

[colintis]

Does it work if you changed the "N" in the DateDiff into lowercase?

Also from one of the tries you mentioned:

I've added [Response] as a parameter for Integer

What did you tried with setting the parameter as integer?

[Paul K]

Hi Colintis

I changed to "n" but no improvment. I'm not sure about your last comment, but I added a query parameter where the parameter was "Response" and the Data Type was "Integer". This was of no help either.

[NeoPa]

I'm not sure and unable to test, but I think it may be due to data in any of your [Notified Date], [Notified Time], [Arrival Date] or [Arrival Time] fields in the table. To help ensure it's not your SQL can we make the following change to your main query so that the DateTime values are DateTime values and not strings which need to be converted automatically :

PARAMETERS [forms].[frm date selection].[start date] DateTime
         , [forms].[frm date selection].[end date] DateTime;
SELECT     [Incident Number]
         , [Run Number]
         , [Structure]
         , [Notified Date]
         , [Notified Time]
         , [Arrival Date]
         , [Arrival Time]
         , CDate([Notified Date] & " " & [Notified Time]) AS [Notified]
         , CDate([Arrival Date] & " " & [Arrival Time]) AS [Arrival]
         , DateDiff("n",[Notified],[Arrival]) AS [Response]
FROM       [tblIncidents]
WHERE    (([Run Number]=1)
  AND     ([Notified Date] Between [forms].[frm date selection].[start date]
                               And [forms].[frm date selection].[end date]));

[TheSmileyCoder]

Have you tried modifyiung your query clause to:

WHERE ((([q Response Time report - detail].Structure)="sfd") AND ((clng([q Response Time report - detail].Response))>120));

[Paul K]

Hi NeoPa

Thanks for your support. I added the CDate to both of the entries but no change. In addition, I tried the addition of CLng to the criteria line as TheSmileyOne suggested but that didn't help either. I've tried both of these solutions independently and together in different combinations, but still no positive results. I don't know if it helps or make any difference, but when I get the "Data mismatch..." error box, all of the cells in the query change to #Name. Any other suggestions would be apprecited as I'm still at a loss even after playing around with it for a few more hours.

Thanks

[Paul K]

I FIGURED IT OUT!!! I went back and looked at the source data and I had one line in my main table that had an incident number and nothing else. I think it may have happened when I was earlier balancing all of the data and it got entered by error. I deleted that row and all is now fine. I knew it had to be something stupid I did!! Thanks for all of your suggestions. If nothing else, I have learned some new tricks and thinks to look at, as well as how to post properly!

Thanks again.

[TheSmileyCoder]

Its nice to hear you got it sorted, and appreciate that you take the time to return and "close" your thread.

[NeoPa]

Paul, remember this one!

It will come back from time-to-time and bite you on the bum if you forget it. In most cases you can code your SQL defensively to handle missing values, but it all really depends on how inappropriate the data is.

Next time you see a query that works, until you change your WHERE clause in some way, consider the data as a potential issue. As I said in post #4 the problem is almost certainly related to the data in those four fields (even though most of the data in that record is missing it would not have been a problem had all those particular fields been ok).

Well done for finding and fixing BTW :)