Report By Date

[DS]

I have to run a report for each days sales, the problem is that each
"Day" starts one day and ends the next. I now have the date of creation
for each record this doesn't seem to be enough, because that doesn't
clarify which day the record belongs, today or yesterday! Are there
any suggestions on how I might do this?
Thanks
DS

 

[Allen Browne]

How do you determine which date it is?

If you have a date/time field named SaleDateTime, and you need to subtract
10 hours from the SaleDateTime field to get it into the correct date, type
this into the Field row in query design:
EffectiveDate: DateValue(DateAdd("h", -10, [SaleDateTime]))

You can then create the report based on this EffectiveDate field.

 

[DS]

How do you determine which date it is?

If you have a date/time field named SaleDateTime, and you need to
subtract 10 hours from the SaleDateTime field to get it into the correct
date, type this into the Field row in query design:
EffectiveDate: DateValue(DateAdd("h", -10, [SaleDateTime]))

You can then create the report based on this EffectiveDate field.

This is tough, Allen. I'm trying to do this with an open code so that
the end times can be changed. So here is the scenario in further
detail.

The business opens at 9:00 AM on Monday and closes at 1:00 AM on
Tuesday. Lets say Monday is April 1 then Tuesday will be April 2.
So if I want to run the report for April 1 I will lose the April 2 info.

Now when I go to run Tuesdays report, April 2 I will get the info from
Mondays business (the after midnite part) as well as Tuesdays.

So I'm thinking maybe I need to put an end date on the report based on a
Master Table that will te me when a business day begins and ends? Which
is great, but what if I change my business hours what happens to the old
reports? There has to be some solution to this? Perhaps a Master File
Number? But how would I do my searches then?
Any help is appreciated.
Thanks
DS

 

[Allen Browne]

If you just subtract 1 hour in the query, then the sales between midnight
and 1am will be reported as in the previous day?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

How do you determine which date it is?

If you have a date/time field named SaleDateTime, and you need to
subtract 10 hours from the SaleDateTime field to get it into the correct
date, type this into the Field row in query design:
EffectiveDate: DateValue(DateAdd("h", -10, [SaleDateTime]))

You can then create the report based on this EffectiveDate field.

This is tough, Allen. I'm trying to do this with an open code so that the
end times can be changed. So here is the scenario in further
detail.

The business opens at 9:00 AM on Monday and closes at 1:00 AM on Tuesday.
Lets say Monday is April 1 then Tuesday will be April 2.
So if I want to run the report for April 1 I will lose the April 2 info.

Now when I go to run Tuesdays report, April 2 I will get the info from
Mondays business (the after midnite part) as well as Tuesdays.

So I'm thinking maybe I need to put an end date on the report based on a
Master Table that will te me when a business day begins and ends? Which
is great, but what if I change my business hours what happens to the old
reports? There has to be some solution to this? Perhaps a Master File
Number? But how would I do my searches then?
Any help is appreciated.
Thanks
DS

 

[DS]

If you just subtract 1 hour in the query, then the sales between
midnight and 1am will be reported as in the previous day?

Thanks Allen,
I set-up a table that holds the business hours of the establishment for
each day. The start day and end day are then added to each sale and
then I can do a query based on the start day.
Thanks
DS