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

 

[Ally72]

As previously, Ally.

What database is being queried?

Sorry - thought I'd answered that, so maybe I misunderstood. What exactly do you mean? You mention identifying the database?

I did the SELECT TOP 20 and got 20 left aligned records like this:
2010-07-28 11:05:00.000
2010-08-17 15:10:00.000
2010-05-05 12:00:00.000 ...

So it looks like they're being converted to text.

Norie - I can access the front end of the database, but it's written by an external company so we don't have access to the back end. We were given access to the SQL side of things to make reporting easier!!

I'm not sure why when times aren't entered, this isn't flagged, but unfortunately it's not something I have control over.

 

[Ally72]

I've recently tried:

convert(datetime,stktia_dtonset,3)

which in SQL 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.

 

[Ally72]

I did try 103 at the end too and that was the same.

 

[Norie]

Why can't you do any calculations with the date if it's text?

Sure you would have to convert it to a real date somehow but it should be possible.

Excel has a fair amount of functions that should help you deal with something like that, eg DATEVALUE.

 

[Ally72]

I tried DateValue and CDate(Int(..... and every time I try it, MS Query either crashes out or I get:

'datevalue' is not a recognised built-in function name. Statement(s) could not be prepared.

When I try doing the calculation:

convert(char,stktia_dtonset,3)-convert(char,stktia_dtref,3)

I get: Operand data type char is invalid for subtract operator. Statement(s) could not be prepared.

 

[Ally72]

It appears, with some help from another forum, that this can be fixed using DateDiff!

Thanks for your help guys.

Ally