Grouping Problem in Totals Query

  • Thread starter Thread starter Connell Giacomini
  • Start date Start date
C

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?
 
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
 
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.
 
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
 
Back
Top