First and Last Order Week

[Supe]

I want to set up a report that will show the first week that a customer
ordered a product and the lastest week they ordered it. That dates on the
report I receive are set up like the examples below:

12/30/2007 - 01/05/2008
01/06/200/ - 01/12/2008

I tried using a MIN and MAX command, but when I do that it shows the week
that starts with 01/ as the MIN and the week that starts with 12/ as the MAX
which would be correct numerically.

Is there a way to do this by invoice week?

 

[Duane Hookom]

Can you tell us something about your data/tables? You mention "customer",
"product", and "week" but show us only a value that might be one or more
fields of unknown data type.

 

[Supe]

Each time a customer orders a product a new line appears for that order. The
first column would be the invoice week they ordered the item. The one week
date range (ex: 12/30/2007 - 01/05/2008) is listed in one field as a text.
The rest of the fields are item data(UPC, description, Item Code...) and the
customer information(Store #, Name, Address, City, State, Zip). All in
separate fields.

The finished report would show the first week period that the product was
ordered and that last week that the product was ordered for each customer.
Is it possible to get a first and last week the way the
invoice week is formatted in my table?

 

[Evi]

Is 'One week date range' also a text field in your table?
Evi

 

[Duane Hookom]

You should be able to create a totals query that groups by customer and
product with Min and Max of CDate(Left([Your Field], 10)).