Date format problem?

[Jonathan]

I feel like the answer is staring me in the face.

I have a table with a date field where some of the data is imported from an
external source in general date format with full date and time data. At other
times the date field is hand entered with only the mm/dd/yyyy data.

Now I am building a query to power a search form using the date field as a
criteria (using the Between function). In the date field there are, let's
say, five instances of 7/31/2008 with full time data, and only one instance
of 7/31/2008 where it was hand entered and thus has no time data. When I
enter the search term in my form "7/31/2008" I am only finding the one record
with no time data. On my search form, the unbound fields are set to short
date with an input mask. I've spent an afternoon trying to understand the
issue but can't seem to puzzle it out, yet it seems stupidly simple. Changing
display formats in the query or search form doesn't work, and so far, trying
to adjust the search entry data using calculations such as DateValue isn't
working.

Ideas?

P.S. I am running Access 2000

Thanks for any help,

Jonathan

 

[fredg]

I feel like the answer is staring me in the face.

I have a table with a date field where some of the data is imported from an
external source in general date format with full date and time data. At other
times the date field is hand entered with only the mm/dd/yyyy data.

Now I am building a query to power a search form using the date field as a
criteria (using the Between function). In the date field there are, let's
say, five instances of 7/31/2008 with full time data, and only one instance
of 7/31/2008 where it was hand entered and thus has no time data. When I
enter the search term in my form "7/31/2008" I am only finding the one record
with no time data. On my search form, the unbound fields are set to short
date with an input mask. I've spent an afternoon trying to understand the
issue but can't seem to puzzle it out, yet it seems stupidly simple. Changing
display formats in the query or search form doesn't work, and so far, trying
to adjust the search entry data using calculations such as DateValue isn't
working.

Ideas?

P.S. I am running Access 2000

Thanks for any help,

Jonathan

If your criteria is, let's say,
Between 7/1/2008 and 7/31/2008
Then, if the field contains a time value, anything on 7/31/2008 after
midnight will not be within the query criteria. After all, 7/31/2008
08:10 AM is later that 7/31/2008 00:00 AM (Midnight). That's why the
hand entered date show up (if no time value is entered, the Time value
is 0 (midnight).

To return records where there is a time value included, always add 1
day to the criteria, i.e.
Between 7/1/2008 and 8/1/2008
or you could simply add a time value of 11:59:59 PM to the criteria:
Between 7/1/2008 and 7/31/2008 23:59:59

If you were to use a parameter query, and have the query prompt for
the wanted date range, then use
Between [EnterStartDate] and DateAdd("d",1,[EnterEndDate])

In this case, the user would enter 7/1/2008 and 7/31/2008 when
prompted.

 

[Jonathan]

Fred,

Thanks for the reply. Right as I got your reply I solved it using the method
you suggested of adding a time value of 11:59:59 pm to the second date
parameter. Thanks for the explanation!

Jonathan

I feel like the answer is staring me in the face.

I have a table with a date field where some of the data is imported from an
external source in general date format with full date and time data. At other
times the date field is hand entered with only the mm/dd/yyyy data.

Now I am building a query to power a search form using the date field as a
criteria (using the Between function). In the date field there are, let's
say, five instances of 7/31/2008 with full time data, and only one instance
of 7/31/2008 where it was hand entered and thus has no time data. When I
enter the search term in my form "7/31/2008" I am only finding the one record
with no time data. On my search form, the unbound fields are set to short
date with an input mask. I've spent an afternoon trying to understand the
issue but can't seem to puzzle it out, yet it seems stupidly simple. Changing
display formats in the query or search form doesn't work, and so far, trying
to adjust the search entry data using calculations such as DateValue isn't
working.

Ideas?

P.S. I am running Access 2000

Thanks for any help,

Jonathan

If your criteria is, let's say,
Between 7/1/2008 and 7/31/2008
Then, if the field contains a time value, anything on 7/31/2008 after
midnight will not be within the query criteria. After all, 7/31/2008
08:10 AM is later that 7/31/2008 00:00 AM (Midnight). That's why the
hand entered date show up (if no time value is entered, the Time value
is 0 (midnight).

To return records where there is a time value included, always add 1
day to the criteria, i.e.
Between 7/1/2008 and 8/1/2008
or you could simply add a time value of 11:59:59 PM to the criteria:
Between 7/1/2008 and 7/31/2008 23:59:59

If you were to use a parameter query, and have the query prompt for
the wanted date range, then use
Between [EnterStartDate] and DateAdd("d",1,[EnterEndDate])

In this case, the user would enter 7/1/2008 and 7/31/2008 when
prompted.