Help converting SQL to Access

[Mike Hughes]

I have this SQL query
select userid,
id_case,
id_screen,
dt_queried
from noldba.user_audit
where trunc(dt_queried)='10/FEB/2009' AND userid='HW4THIL'
order by DT_QUERIED

It returns this:
Userid Id ............Case Id........... Screen .............Dt Queried
HW4THIL.......... ....................NAV TO NEWS........ 2/10/2009 11:10:58 AM
HW4THIL............................... NAV TO CASE....... 2/10/2009 11:11:00 AM
HW4THIL.............................. NAV TO FNOT......... 2/10/2009 11:11:08 AM
HW4THIL ..........14076131P.......... ELOG ...............2/10/2009 11:11:11 AM

When I run this query in Access I get nothing returned. Could someone please tell me how to correct the access query? Thanks

SELECT
NOLDBA_USER_AUDIT.USERID,
NOLDBA_USER_AUDIT.ID_CASE,
NOLDBA_USER_AUDIT.ID_SCREEN,
NOLDBA_USER_AUDIT.DT_QUERIED

FROM NOLDBA_USER_AUDIT

WHERE
(((NOLDBA_USER_AUDIT.USERID)="HW4THIL") AND
((NOLDBA_USER_AUDIT.DT_QUERIED)=#2/10/2009#));

 

[Rabbie]

Remember that in Access a date without a time is equivalent to the date at 12:00:00 AM so it can get an exact match in your second condition.

Try this query and see if it works.

SELECT
NOLDBA_USER_AUDIT.USERID,
NOLDBA_USER_AUDIT.ID_CASE,
NOLDBA_USER_AUDIT.ID_SCREEN,
NOLDBA_USER_AUDIT.DT_QUERIED

FROM NOLDBA_USER_AUDIT

WHERE
(((NOLDBA_USER_AUDIT.USERID)="HW4THIL") AND
((NOLDBA_USER_AUDIT.DT_QUERIED) Between #2/10/2009 12:00:00 AM# and #2/10/2009 23:59:59#));

 

[Rabbie]

just had a better idea!

Try

SELECT
NOLDBA_USER_AUDIT.USERID,
NOLDBA_USER_AUDIT.ID_CASE,
NOLDBA_USER_AUDIT.ID_SCREEN,
NOLDBA_USER_AUDIT.DT_QUERIED

FROM NOLDBA_USER_AUDIT

WHERE
(((NOLDBA_USER_AUDIT.USERID)="HW4THIL") AND
(Datepart(NOLDBA_USER_AUDIT.DT_QUERIED)=#2/10/2009#));

 

[namliam]

Datepart(NOLDBA_USER_AUDIT.DT_QUERIED)=#2/10/2009#;

Or

Int (dt_queried) = #02/10/2009#

which is the same trunc

 

[Mike Hughes]

The first one worked! Thanks
SELECT
NOLDBA_USER_AUDIT.USERID,
NOLDBA_USER_AUDIT.ID_CASE,
NOLDBA_USER_AUDIT.ID_SCREEN,
NOLDBA_USER_AUDIT.DT_QUERIED

FROM NOLDBA_USER_AUDIT

WHERE
(((NOLDBA_USER_AUDIT.USERID)="HW4THIL") AND
((NOLDBA_USER_AUDIT.DT_QUERIED) Between #2/10/2009 12:00:00 AM# and #2/10/2009 23:59:59#));

 

[raskew]

Hi -

In the future, you might look to the DateValue() function. Example from the debug (immediate) window:

x = now()
? x
2/11/2009 9:15:30 AM 
? DateValue(x)
2/11/2009

It also works similarly with a string:

y = "2/11/2009 9:15:30"
? y
2/11/2009 9:15:30
? DateValue(y)
2/11/2009

Best Wishes - Bob