Grouping Problem in Totals Query

[Connell Giacomini]

My query has three fields; WorkDate, Part#, PartQty.

I want to sum the total qty for each part# within a specified start & end
WorkDate, as specified in the parameter. The summation works, but does not
group by the work date field. So instead of :
1/11/07 #1445 Qty 6
the result comes out as:
1/11/07 #1445 Qty 2
1/11/07 #1445 Qty 3
1/11/07 #1445 Qty 1

I appears that the Group By doesn't work for the date field. Is there a
special technique to handle this?

 

[Rob Parker]

Perhaps your dates are not the same. If the date field has been
generated/stored via a =Now() expression, the time component may well be
different (in fact, I'd be surprised if it was the same). If you view your
underlying data in a table/query in which the format of the field is set to
show both date and time, you will find any differences. And, to prevent
this, use an =Date() expression, which sets the time component to 12:00:00
AM (it's actually the decimal portion of a number which represents date/time
as days/decimal days since 30 Dec 1899).

HTH,

Rob

 

[Connell Giacomini]

Your answer is absolutely correct! I changed the WorkDate field from =Now()
to =Date() and the query calculates correctly. My next question would be -
is there a method of updating the existing records (about 6,000) to
eliminate the time part of the field? Thanks much for your help.

 

[Rob Parker]

You can run an update query to change the existing data; something like:

UPDATE MyTableName SET MyDateTimeFieldName =
DateValue([MyDateTimeFieldName ])
WHERE MyDateTimeFieldName=IsDate([MyDateTimeFieldName]);

The WHERE clause will prevent errors if the field does not contain a valid
datetime value.

HTH,

Rob