Date format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,

I have a table with a date/time (together) field. In a query I'm trying to
group on date and count another field. What I want the query to show is
something like this

Date Count
5/01/2005 100
5/02/2005 57
5/03/2005 321

However, since my field has date and time together it shows the same date
multiple times, because time is there to.

Is there a way to get this to work? I have been trying to format the field
to only show the date, but nothing seems to work.

Thanks for any help
 
Mark,

Formatting only affects what you see, without actually changing the
data, therefore it won't o the trick. What you need is some means to
extract the date only, dropping the time part within the scope of your
query (so you don't lose the stored time data). Assuming your date/time
field is called MyDate, you can use a calculated field with the
following expression in the query:

DDate: DateValue([MyDate])

which wil return the date part only, so you can group on; just
substitute MyDate with the actual field name.
Note: DDate as an alias above is not a typo! It is intended to
differentiate from Date, which is an Access reserved keyword and should
not be used as a field name or control name or alias; you can use
anything instead, as long as you avoid reserved keywords.

HTH,
Nikos
 
Thanks!

That worked great.... I spent about 3 hours on this lastnight and finally
just used a report to group it for me.

Nikos Yannacopoulos said:
Mark,

Formatting only affects what you see, without actually changing the
data, therefore it won't o the trick. What you need is some means to
extract the date only, dropping the time part within the scope of your
query (so you don't lose the stored time data). Assuming your date/time
field is called MyDate, you can use a calculated field with the
following expression in the query:

DDate: DateValue([MyDate])

which wil return the date part only, so you can group on; just
substitute MyDate with the actual field name.
Note: DDate as an alias above is not a typo! It is intended to
differentiate from Date, which is an Access reserved keyword and should
not be used as a field name or control name or alias; you can use
anything instead, as long as you avoid reserved keywords.

HTH,
Nikos
Hello all,

I have a table with a date/time (together) field. In a query I'm trying to
group on date and count another field. What I want the query to show is
something like this

Date Count
5/01/2005 100
5/02/2005 57
5/03/2005 321

However, since my field has date and time together it shows the same date
multiple times, because time is there to.

Is there a way to get this to work? I have been trying to format the field
to only show the date, but nothing seems to work.

Thanks for any help
Click to expand...
Click to expand...
 
Back
Top