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:
I basically need to try and translate this into MS Query.
So far I have:
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
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:
Code:
=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:
Code:
ReferralDate-FirstContactDate in the criteria field ... with <0 in the criteria valueDoing 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
