Date/Time Format

  • Thread starter Thread starter AHopper
  • Start date Start date
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
 
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))
 
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
 
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...
 
Back
Top