MS Query - Dates and INT functions

[Ally72]

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 but to convert the date/time fields to date only, because unfortunately, the users of the original database have omitted to put the time in many of the records of one of the fields, which in Excel we would get around by using the INT function.

This is how we did it in the original Excel s/sheet which got over the date formatting problem:

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

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

</PRE>
Doing part of it in MS Query to return some and then the rest of the calculation in Excel is not really an option because we are trying to show errors, and this would be misleading as the users would then have a long list which might or might not have errors.
I hope this all makes sense and would be extremely grateful for any help!

I'm using Excel 2003.

Thank you!

Ally

 

[Fazza]

Hi, Ally

I don't understand the set up enough to be specific, sorry. One can use INT in the SQL - it should be just like Access. It really helps to post sample data, btw.

These sort of things should be fine,

INT(date_field)
WHERE INT(Referral_Date) - INT(First_Contact_Date) < 0
and for an empty cell, WHERE somefield Is Null
or two empty cells, WHERE onefield Is Null AND anotherfield Is Null

or in the SELECT clause,
SELECT IIF(field_H4 Is Null OR field_G4 Is Null, Null, IIF(INT(H4)-INT(G4)<0,'Referral', Null) AS [Final Field Name]

Again, if you get stuck, do it in Access and copy & paste the SQL; it should be the same.

HTH

 

[Ally72]

Thank you - just about to go out but will have a look tomorrow.

:D

 

[Fazza]

OK.

 

[Ally72]

I'm now getting "INT is not a recognised built-in function name. Statements could not be prepared."

I've just tried posting images of the errors etc, but I can only see the option of Insert picture which asked for a URL. I'd saved images and tried dragging them into a browser but that just took me back to MS Paint. Is there a way of displaying jpg / bmps etc please!?

 

[Fazza]

I think images of the errors are not too important.

What database is being queried?

Is the particular field numeric or text? What happens when you simply query the particular field. Say a simple query like,

SELECT TOP 20 your_date_field_name
FROM your_table_name

Are the results left justified (text) or right justified (numeric)?

 

[Norie]

Ally

Where is the data coming from an where is it going?

Also I don't quite understand the bit about something being 'misleading'.

What would be misleading and how/why would it be?

 

[Ally72]

Thank you both for coming back.

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.

What I'm trying to achieve is to find the errors, examples such as where Date of Onset is AFTER Date of Referral (ie onset should come before); where Carotid Doppler (type of treatment) is Indicated but the Received field is Null; Carotid Doppler Received but no Date for this entered - that sort of thing.

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.

What I mean by misleading is (and totally understand how confusing this must be the other end without seeing what I'm looking at!!) - 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.

Does this make sense?!

The fields I'm having problems with are Date / Time fields. These are set up in the database as [DD/MM/YYYY hh:mm] but not all of the end users input the time part of these fields if they don't feel this is important (!!) which then means that when I subtract one from the other it gives an error because the non-entered time field returns 00:00 and the other field could have had entered [DD/MM/YYYY 15:25] which throws up an error when in these particular cases, they're not. So I need to basically convert these to just DD/MM/YYYY fields, and ignore the time part. In Excel we did this by using INT.

I hope all this is clear.

Thank you!

 

[Fazza]

As previously, Ally.

What database is being queried?

Is the particular field numeric or text? What happens when you simply query the particular field. Say a simple query like,

SELECT TOP 20 your_date_field_nameFROM your_table_name

Are the results left justified (text) or right justified (numeric)?

Addressing questions like these can help.

When you know the database, you can find out what functions to use. INT doesn't work, but you can determine what does. Options that come to mind other than INT are VAL and CDate or even CLng - I offer these from my perspective of querying Excel files, they may be no help whatsoever to you. Hopefully they convey the idea of using other functions. I can't help though with your unidentified database; suggest you google for help once you identify the database, and find out what functions you can use. regards, Fazza

 

[Norie]

Can you access the database where the data is held?

If you can I would recommend you create your queries there and then import them to Excel.

Another thing I would recommend, especially if this is going to be a long-running problem, is to fix the database.

What I mean is make sure dates are entered where required and are valid etc.

In most databases you can set the properties of columns/fields to do that - it's not an uncommon thing.

You could even introduce a default date where appropriate.