date mismatch in query..do not know how to solve

  • Thread starter Thread starter tcek
  • Start date Start date
T

tcek

i have a query where the criteria statement has [date#1] equal to [date#2-1].
[date#1] is in a table and formatted as mm/dd/yyyy. date #2 is formatted as
date and time. how do i format date#2 to match the date#1 mm/dd/yyyy format?
 
i have a query where the criteria statement has [date#1] equal to [date#2-1].
[date#1] is in a table and formatted as mm/dd/yyyy. date #2 is formatted as
date and time. how do i format date#2 to match the date#1 mm/dd/yyyy format?
Click to expand...

The format is completely irrelevant. A Date/Time value is stored as a number,
a count of days and fractions of a day since an arbitrary start point. A date
without a time is stored as an integer number and corresponds to midnight at
the beginning of that date.

If Date#1 contains a time portion and you want to match it to a Date#2 which
does not, use a criterion of
= [Date#2] AND < DateAdd("d", 1, [Date#2])
Click to expand...

to find all times during that 24 hour span.

John W. Vinson [MVP]
 
tcek said:
i have a query where the criteria statement has [date#1] equal to [date#2-1].
[date#1] is in a table and formatted as mm/dd/yyyy. date #2 is formatted as
date and time. how do i format date#2 to match the date#1 mm/dd/yyyy format?
Click to expand...


Note that the display format of a value does not necessarily
have much to do with the actual value.

If the **value** in date1 does not contain a time part and
the value in date2 does have a time part, then use a
calculated field with the expression:
DateValue(date2)
and use date1 as the criteria.
 
If both fields are true DateTime fields the formatting does not matter.
In one position you have [date#2-1] and other places [date#2]. If this is
not a typo in the posting then that is a problem. If you meant to subtract
one day from [date#2] then it should be [date#2] -1.

The title of your post is 'date mismatch ...' If this is not a typo then
you have problem have other problem with your data as I have not seen an
error message of date mismatch but data mismatch.
 
Back
Top