MS Query - Dates and INT functions

[Ally]

Hi

I am using MS Query to return data into Excel. I know Access but am quite new to MS Query and keep finding vast differences between the two!! I've looked on the help but it doesn't seem to be of any!

I have two date fields which are formatted as dd/mm/yyyy hh:mm - and I need to take one from the other to pick out negatives as errors, but I need to convert the date/time fields to date only, because one of the fields has all the times unentered, (ie, they're all set to 00:00:00). For example, where:

Date of Onset Date of Referral
09/06/2010 10:00 23/06/2010 00:00 OK because Onset < Referral
28/07/2010 07:30 26/07/2010 00:00 Not OK because Onset > Referral
26/06/2010 10:00 26/06/2010 00:00 This should be OK because they are effectively the same date but because there's a time in the Onset Date field it causes an error. This is why I need to convert it.

In Excel we got around this by using the INT function.

ie:

=IF(OR(H4="",G4=""),"",IF(INT(H4)-INT(G4)<0,"Referral Before 1st Contact",""))

When I've tried to use INT in MS Query, I get "INT is not a recognised function".

I basically need to try and translate this into MS Query.
So far I have:

ReferralDate-FirstContactDate in the criteria field ... with <0 in the criteria value

which still returns all those that should be excluded but because it still looks at the time part of the field returns a lot more.

I've also tried:

convert(datetime,stktia_dtonset,3)

which in SQL I am told, should convert it to just DD/MM/YYYY but it's returning exactly the same as without any convert function.

and:

convert(char,stktia_dtonset,3)

This returns the dates as just dates which is great, but because it converts it to a string, I then can't do any calculations with it.

I'm doing one page per error showing just the necessary fields, with a summary page at the beginning and hyperlinks to jump to the page where there are errors next to a number (a count formula) which counts the number of errors returned.

Because I'm doing a summary page at the beginning, with the intention of the end user only needing to jump to a page where there are actual errors, if I was to then filter this down by doing calculations in Excel, it might say on the summary page that there are 4 errors, but when they went to the page with the errors on they could potentially be faced with 200 patient records and they then have to look at my Excel based formulas to work out which ones are in actual fact errors. Therefore, I need to try and do this all in MS Query so it only returns the ACTUAL errors.

The database I'm querying is an SQL based database for Stroke and TIA patients which holds information on their admissions, scans, type of attack - lots of dates etc. I'm doing this via Excel > External Query and refining my query in MS Query.

I hope this all makes sense and would be extremely grateful for any help!

I'm using Excel 2003.

Thank you!

Ally