Date/Time Format

A

AHopper

I have been working on a report based on a date. I have
resolved all the problems except one. I want to run the
report from a command button on Click event. The date
will be entered into a text box and formatted mm/dd/yy.

In the table the report is based on is a field
named "PackDate" it is a Date/Time field formatted to
mm/dd/yy entered as Now() when the record is saved, so it
includes both date and time even though the time is not
displayed. When I enter a date #09/08/2004# as the
criteria for the report it does not recognise it so it
does not select the records I want in the report.
Is there a way to enter another date field in the report
source query that would change the format (remove the
time)so I could reference it when running the report?

Do I have to add another field to the table that will
only contain the date? As mentioned above, presently I
use Now()when the record is saved.

Is it better to design the tables so they have both a
date and a time field? What would be the equivlent of Now
() to populate these fields?

Thank you the help.

Allan
 
D

Douglas J. Steele

You can use the DateValue function to remove the time from the field.
However, rather than applying a function to every row in your table, it's
probably more efficient to simply check for dates between #09/08/2004# and
#09/09/2004# (or, in general, between MyDate and DateAdd("d", 1, MyDate))
 
A

AHopper

Doug,
Does everyone have the trouble I am having understanding
how dates work? I am very frustrated. The field in the
table is set to Date/Time. I am trying first at the
report level to see what I need to use as a criteria to
get the right information, then I will try to enter that
criteria through my form command button and a text box.
So far the only way I can get any results is reenter all
the dates in the table as 09/08/2004 without the time.
When I use Between #09/02/04# And #09/08/04# in the
criteria, even though there are many records in that
range in the source table I only get the records that I
have reentered without the time.

Somehow I am going to understand how this works.

Thank you for your patience and help.

Allan
 
A

AHopper

Doug,
I finally got the report to print. It may not be the best
way to accomplish the task but in the report source I put
a DateValue field and on the table I put a combo
box "Report Date" with a DateValue field that used the
same table as the report source. I then used "Report
Date" as the criteria when I run the report from the
command button click event on my form.

Thank you again for your help. It was the piece I was
missing.
Allan
-----Original Message-----
Doug,
Does everyone have the trouble I am having understanding
how dates work? I am very frustrated. The field in the
table is set to Date/Time. I am trying first at the
report level to see what I need to use as a criteria to
get the right information, then I will try to enter that
criteria through my form command button and a text box.
So far the only way I can get any results is reenter all
the dates in the table as 09/08/2004 without the time.
When I use Between #09/02/04# And #09/08/04# in the
criteria, even though there are many records in that
range in the source table I only get the records that I
have reentered without the time.

Somehow I am going to understand how this works.

Thank you for your patience and help.

Allan
-----Original Message-----
You can use the DateValue function to remove the time from the field.
However, rather than applying a function to every row
Click to expand...
in
your table, it's
Click to expand...
 
Top