Date Format

I have a database with a date field which I have set to "short date". On my
input form, I set the default to date(), also formated to "short date". Then
I use a query to select records by date. However, the date keeps showing up
as a general date with the date and time and the query doesn't select the
records for the date when it is inputed as a short date. Using the criterion
"[set date to review] & *" works, but selects dates not wanted (eg 7/2
selects 7/2/2009 and also 10/7/2009).

I have tried to use a "mm/dd/yy" format, but it keeps putting in the date as
a general format.

How can I force the short date into the database?

Did you set the format for the field in your query to short date by selecting
the date field, right click to bring up the properties window and then in the
format drop down the list and select short date.

If that does not work, it could be how the date is formated within the
actual table, check that becasue I believe it will override the others
Jacqueline

"Orlan" wrote:

I have a database with a date field which I have set to "short date". On my
input form, I set the default to date(), also formated to "short date". Then
I use a query to select records by date. However, the date keeps showing up
as a general date with the date and time and the query doesn't select the
records for the date when it is inputed as a short date. Using the criterion
"[set date to review] & *" works, but selects dates not wanted (eg 7/2
selects 7/2/2009 and also 10/7/2009).

I have tried to use a "mm/dd/yy" format, but it keeps putting in the date as
a general format.

How can I force the short date into the database?

On Thu, 15 Oct 2009 09:58:23 -0700, Orlan
wrote:

I have a database with a date field which I have set to "short date". On my
input form, I set the default to date(), also formated to "short date". Then
I use a query to select records by date. However, the date keeps showing up
as a general date with the date and time and the query doesn't select the
records for the date when it is inputed as a short date. Using the criterion
"[set date to review] & *" works, but selects dates not wanted (eg 7/2
selects 7/2/2009 and also 10/7/2009).

I have tried to use a "mm/dd/yy" format, but it keeps putting in the date as
a general format.

How can I force the short date into the database?

A date is not stored as a string, so the & * makes no sense here. A Date
(regardless of format!) is stored as a Double Float number, a count of days
and fractions of a day (times) since midnight, December 30, 1899. So there is
no such thing as "a short date" or "a long date" in the table; there's just a
date, which can be displayed any way you wish.

Rather than a criterion of

LIKE [Set date to review] & "*"

try simply using = [Set date to review]; Access is quite smart enough to
interpret 7/2 to #07/02/2009#, using the current year if the user doesn't
specify it.
--

John W. Vinson [MVP]

Right now I have everything set to short date, the table, the query, the form
and the report. Unless I go into the table and retype the date as a short
date, the query will not recognize the record.

"Jacqueline" wrote:

Did you set the format for the field in your query to short date by selecting
the date field, right click to bring up the properties window and then in the
format drop down the list and select short date.

If that does not work, it could be how the date is formated within the
actual table, check that becasue I believe it will override the others
Jacqueline

"Orlan" wrote:

I have a database with a date field which I have set to "short date". On my
input form, I set the default to date(), also formated to "short date". Then
I use a query to select records by date. However, the date keeps showing up
as a general date with the date and time and the query doesn't select the
records for the date when it is inputed as a short date. Using the criterion
"[set date to review] & *" works, but selects dates not wanted (eg 7/2
selects 7/2/2009 and also 10/7/2009).

I have tried to use a "mm/dd/yy" format, but it keeps putting in the date as
a general format.

How can I force the short date into the database?

Unless I go into the table and retype the date as a short date, the query
will not recognize the record.
Your first post said "I have a database with a date field..." but if you
truly have a DateTime datatype field it makes no difference as the data is
stored a decimal number where the decimal fraction is representing that part
of a day to indicate time.
You must have a text field that you applied a format to. A format controls
only the display of the location you are viewing. Formating a table does not
control how the data is stored, retrived, or displayed in a form or report.

Open your table in design view and check the property of your field to see
the DataType.

--
Build a little, test a little.


"Orlan" wrote:

Right now I have everything set to short date, the table, the query, the form
and the report. Unless I go into the table and retype the date as a short
date, the query will not recognize the record.

"Jacqueline" wrote:

Did you set the format for the field in your query to short date by selecting
the date field, right click to bring up the properties window and then in the
format drop down the list and select short date.

If that does not work, it could be how the date is formated within the
actual table, check that becasue I believe it will override the others
Jacqueline

"Orlan" wrote:

I have a database with a date field which I have set to "short date". On my
input form, I set the default to date(), also formated to "short date". Then
I use a query to select records by date. However, the date keeps showing up
as a general date with the date and time and the query doesn't select the
records for the date when it is inputed as a short date. Using the criterion
"[set date to review] & *" works, but selects dates not wanted (eg 7/2
selects 7/2/2009 and also 10/7/2009).

I have tried to use a "mm/dd/yy" format, but it keeps putting in the date as
a general format.

How can I force the short date into the database?

On Thu, 15 Oct 2009 12:44:01 -0700, Orlan
wrote:

Right now I have everything set to short date, the table, the query, the form
and the report. Unless I go into the table and retype the date as a short
date, the query will not recognize the record.

The Format of the date is *COMPLETELY IRRELEVANT* to how it is stored or
searched.

The table does *NOT* contain "07/14/2009". The table contains 40008.00000000
(because that's how many days had elapsed since midnight, December 30, 1899).

But depending on how the data was *entered* into the table - which you have
not said! - it might instead contain 40008.375000000 - equivalent to
#07/14/2009 09:00:00 AM#. Setting the format on the table (or anyplace else)
does not remove the 9AM portion, it just conceals it from view; and 40008.375
is in fact not equal to 40008.000, so your search isn't retrieving the data!

If the data in the table in fact contains a time portion (which you can verify
by removing the format and looking at the table datasheet), you can use a
criterion of

= CDate([Enter date to search]) AND DateAdd("d", 1, CDate([Enter date to search]))

to pick up all the records starting at midnight on the entered date up through
the last instant before the next midnight.
--

John W. Vinson [MVP]