Query the date only and not the time portion of a column

[hansjhamm]

I have a linked Excel sheet that I run this query against, however; I
need to narrow the data down by the date also. It is a date/time stamp.
I have tried Format(fix(...., and other date configurations and it
returns no data...
What I would like is this:
CallSummaryMTD.Call Start Time=DMInStoreMonthQuery.Call Start Time
where it would look at the date example: 10-25-06 and not 10-25-06
11:15AM. The dates would be the same but not neccessarily the time.
My current query is:

SELECT DMInStoreMonthQuery.Rep, DMInStoreMonthQuery.[Store #],
DMInStoreMonthQuery.[Call Start Time], DMInStoreMonthQuery.[Call End
Time], DMInStoreMonthQuery.[Call Duration],
DMInStoreMonthQuery.[II-IO], CallSummaryMTD.Rep, CallSummaryMTD.[Store
#], CallSummaryMTD.[Call Start Time], CallSummaryMTD.[Call End Time],
CallSummaryMTD.[Call Duration], CallSummaryMTD.[II-IO],
CallSummaryMTD.[Call Start Time], DMInStoreMonthQuery.[Call Start Time]
FROM CallSummaryMTD INNER JOIN DMInStoreMonthQuery ON
CallSummaryMTD.[Store #] = DMInStoreMonthQuery.[Store #]
WHERE (((DMInStoreMonthQuery.Rep)<>[CallSummaryMTD].[Rep]) AND
((DMInStoreMonthQuery.[Store #])=[CallSummaryMTD].[Store #]) AND
((CallSummaryMTD.[II-IO])>0) AND ((CallSummaryMTD.Grouping)="Call") AND
((CallSummaryMTD.[Call Start Time])=Format(Fix([CallSummaryMTD.Call
Start Time]),"Short Date")) AND ((DMInStoreMonthQuery.[Call Start
Time])=Format(Fix([DMInStoreMonthQuery.Call Start Time]),"Short
Date")));


Thanks for the help


Hans

 

[kingston via AccessMonster.com]

Try the function FormatDateTime(yourData, vbShortDate) or you can reconstruct
the date by using these functions: Month(yourData), Day(yourData), Year
(yourData).

I have a linked Excel sheet that I run this query against, however; I
need to narrow the data down by the date also. It is a date/time stamp.
I have tried Format(fix(...., and other date configurations and it
returns no data...
What I would like is this:
CallSummaryMTD.Call Start Time=DMInStoreMonthQuery.Call Start Time
where it would look at the date example: 10-25-06 and not 10-25-06
11:15AM. The dates would be the same but not neccessarily the time.
My current query is:

SELECT DMInStoreMonthQuery.Rep, DMInStoreMonthQuery.[Store #],
DMInStoreMonthQuery.[Call Start Time], DMInStoreMonthQuery.[Call End
Time], DMInStoreMonthQuery.[Call Duration],
DMInStoreMonthQuery.[II-IO], CallSummaryMTD.Rep, CallSummaryMTD.[Store
#], CallSummaryMTD.[Call Start Time], CallSummaryMTD.[Call End Time],
CallSummaryMTD.[Call Duration], CallSummaryMTD.[II-IO],
CallSummaryMTD.[Call Start Time], DMInStoreMonthQuery.[Call Start Time]
FROM CallSummaryMTD INNER JOIN DMInStoreMonthQuery ON
CallSummaryMTD.[Store #] = DMInStoreMonthQuery.[Store #]
WHERE (((DMInStoreMonthQuery.Rep)<>[CallSummaryMTD].[Rep]) AND
((DMInStoreMonthQuery.[Store #])=[CallSummaryMTD].[Store #]) AND
((CallSummaryMTD.[II-IO])>0) AND ((CallSummaryMTD.Grouping)="Call") AND
((CallSummaryMTD.[Call Start Time])=Format(Fix([CallSummaryMTD.Call
Start Time]),"Short Date")) AND ((DMInStoreMonthQuery.[Call Start
Time])=Format(Fix([DMInStoreMonthQuery.Call Start Time]),"Short
Date")));

Thanks for the help

Hans

 

[Granny Spitz via AccessMonster.com]

What I would like is this:
CallSummaryMTD.Call Start Time=DMInStoreMonthQuery.Call Start Time
where it would look at the date example: 10-25-06 and not 10-25-06
11:15AM. The dates would be the same but not neccessarily the time.

One way is to join on the date value of a date/time column like this:

SELECT *
FROM tbl1 INNER JOIN tbl2 ON tbl1.ADate = DateValue(NZ(tbl2.BDate, #1/1/1900#)
);

Use a date that won't occur as the default whenever BDate is null.