Date formating

[Guest]

For some reason I can't seem to get the format command correct. I want to
only place the date in the record, but what I get is the date and time. When
I go to select by date nothing shows up because the time is in there also.
I've tried using the format function put get an overflow error everytime.
Hopefully someone can point me in the correct direction
Thanks for any help
Tom

 

[Rick Brandt]

For some reason I can't seem to get the format command correct. I want to
only place the date in the record, but what I get is the date and time. When
I go to select by date nothing shows up because the time is in there also.
I've tried using the format function put get an overflow error everytime.
Hopefully someone can point me in the correct direction
Thanks for any help
Tom

What are you using to assign the value? If you use Now() you get the date and
time. If you use Date() you get the date only (actually time of midnight).

 

[Allen Browne]

Tom, formatting the date expression is not the best idea. Can we help you
get the data right?

As Rick says, use =Date() instead of =Now() if you want the date only.
If you want to fix up your existing data so it contains the date only:
1. Create a query into this table.
2. Change it to an Update query (Update on Query menu).
3. Drag the date field into the grid. We'll assume its called MyDate.
4. In the Update row under this field, enter:
DateValue([MyDate])
5. In the Criteria row under this field, enter:
Is Not Null
6. Run the query.

If you want to retrieve the records without losing the time component, ask
for records greater than or equal to this date, and less than the next date,
e.g.:

 

[Guest]

Having similar problem. I have a table whose records are built as the data
is moved from one desk to another. The field is set up as date/time, short
date format. There is no default value. Then I created a form that fills
that field with today's date whenever a checkbox is clicked. In the form,
the field format is short date and the default value is Date(). But as Rick
originally indicated, I am getting date & time in the table, which of course
causes my query to fail. I'm sure that I'm missing a very simple step - this
can't be as difficult as I'm making it. Thanks in advance for your help.

Tom, formatting the date expression is not the best idea. Can we help you
get the data right?

As Rick says, use =Date() instead of =Now() if you want the date only.
If you want to fix up your existing data so it contains the date only:
1. Create a query into this table.
2. Change it to an Update query (Update on Query menu).
3. Drag the date field into the grid. We'll assume its called MyDate.
4. In the Update row under this field, enter:
DateValue([MyDate])
5. In the Criteria row under this field, enter:
Is Not Null
6. Run the query.

If you want to retrieve the records without losing the time component, ask
for records greater than or equal to this date, and less than the next date,
e.g.:

= Date() And < (Date() + 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

What are you using to assign the value? If you use Now() you get the date
and time. If you use Date() you get the date only (actually time of
midnight).