Re: Date format problem?

Tech-Archive recommends: Speed Up your PC by fixing your registry

On Thu, 14 Aug 2008 10:25:01 -0700, Jonathan wrote:

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.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.

Relevant Pages

  • Re: Criteria Compare dates using between and dates from other years are included
    ... The Format() function returns a String. ... You are perform a string comparison ... Hence the dates criteria do not work as you ... If this is an Append query, JET expects the structure below (where you ...
    (comp.databases.ms-access)
  • Re: Collecting Data Via Email
    ... formats (criteria is a date the data being updated/collected is number). ... Test your criteria for unlike data format. ... sent from a select query that has calculated date fields. ... Also if it is from outlook then access expects the field ...
    (microsoft.public.access.externaldata)
  • Re: Cant use dd/mm/yyyy format
    ... date format. ... > correct if the former is numeric data type and the latter a Date/Time. ... >> criteria I was trying to code, then opened the form bound to the query. ...
    (microsoft.public.access.formscoding)
  • Re: filtering by month
    ... Steve S ... Would you post the SQL of the query? ... you suggestion along with the criteria. ... The reason to Format the dates is to discard the day of the ...
    (microsoft.public.access.queries)
  • re: Query a database by DRW
    ... On page 3 of the wizard, click More Options, Criteria, Add. ... field you want to query by, select Use This Search Form ...
    (microsoft.public.frontpage.client)