Joining Dates in Access

[Sarah]

I have a "New Start Time" in my query that is calculated from "Est Run
Start"

Est Run Start = 10/27/2009 6:45:00 PM
New Start Time: FormatDateTime([Start Time],2) = 10/27/2009

I am trying to join "New Start Time" with a table "Week Of Look Up" to
field "Date" so that I can show "Week of" in my query.

Example:
I want to join "New Start Time" = 10/27/2009 to "Date" = 10/27/2009
and get "Week of" = October 26 - Nov 1

Unfortunetly I haven't been able to find a format for "New Start Time"
that will match "Date".

 

[KARL DEWEY]

This should do it --
Format([New Start Time],"yyyymmdd") = Format([Date],"yyyymmdd")

You should not use field named 'Date' as it is a reserved word and may cause
you problems.

 

[Marshall Barton]

I have a "New Start Time" in my query that is calculated from "Est Run
Start"

Est Run Start = 10/27/2009 6:45:00 PM
New Start Time: FormatDateTime([Start Time],2) = 10/27/2009

I am trying to join "New Start Time" with a table "Week Of Look Up" to
field "Date" so that I can show "Week of" in my query.

Example:
I want to join "New Start Time" = 10/27/2009 to "Date" = 10/27/2009
and get "Week of" = October 26 - Nov 1

Unfortunetly I haven't been able to find a format for "New Start Time"
that will match "Date".

The first problem is because you are using Format to conver
the date to a text string. If you want to remove the time
part of a date/time value, then use DateValue([Start Time])

You can get the date of the week starting on Monday by
using:
ws = DateAdd("d", 1-WeekDay([Start Time], 2), [Start Time])

and the week ending date with:
we = DateAdd("d", 6, ws)

So the week of expression could be:
"Week Of " & Format(ws, "mmmm d) & " - " & Format(we, "mmm
d")