Dsum Question

[torquay]

Hope someone can help.
I have a form with a few general stats from my table "AuctionSales"
I can summarize todays and yesterdays sales ok but I am having trouble summing up this weeks sales and last week sales
I have this so far but I am missing the point somewhere

=DSum("TotalSales","AuctionSales","Year([OrderDate])"=Year(Date()) And DatePart("ww",[OrderDate],0)=DatePart("ww",Date(),0))

Thanks

 

[JANR]

Does this help:

=DSum("TotalSales","AuctionSales","[OrderDate] Between " & Date() & " And " & DateAdd("ww",-1,Date()))

JR

 

[gemma-the-husky]

what relevant fields do you have in your data, which you are trying to query?

ie week numbers, dates.

how are you phrasing the query to find a single day's details? That would help


Gemnerally all we need to do is repalce the = bit, with a between bit

 

[torquay]

Thanks for the reply

I used the code and the text box is blank? No errors just blank


[G-T-H edited - I inadvertently edited this, when intending to reply - this text has not been changed]

 

[gemma-the-husky]

Thanks for the reply

I used the code and the text box is blank? No errors just blank

The example gave will have (should have) returned details for data beween today 12/5/11 and one week prior 5/5/11 - [or maybe 6/5/11].

Do you actually have data in those data ranges?

 

[torquay]

Thanks for the reply. There is data in the date range
The table is comes from is a linked table to another db
The query in the current db is based on OrderDate, TotalSales (per order id)

 

[torquay]

I am slowly getting there
This gives me the last 7 days sales but I am still looking to get last weeks sales

=DSum("TotalSales","AuctionSales","[OrderDate] > DateAdd('ww',-1,Date())")

Any more advice?

 

[JANR]

If you go with days instead as in last 12 days then you might get your sum.

=DSum("TotalSales","AuctionSales","[OrderDate] >= DateAdd('d',-12,Date())")

So if you run it today then all sales from and including 2. may to 13. may will be summarized.

JR

 

[JANR]

Perhaps a better solution is this:

=DSum("TotalSales","AuctionSales","[OrderDate] >= DateAdd('d',-7,(Date()-Weekday(Date(),2)+1))")

This calculates the first day of this week and subtract 7 days to get first day of last week (Monday). The weekday() function uses Monday as firstday of the week (marked in RED).

JR

 

[torquay]

Thanks for the reply.
Sorry but thats not what I am looking for
Is there a way I can just get last weeks sales so if its ran today oe tomorrow (Fri or Sat) it will show last weeks sales. I could do a between but I would have to change it every day when I want it to run.
Basically there a re a bunch of fields I have on a form that will give updated sales figures, i.e, todays sales, yesterdays sales, this weeks sales (not figured that out yet) last weeks sales, current month sales, current years sales.

 

[JANR]

[OrderDate]>=Date()-Weekday(Date(),2)+1

This gives you the startdate of the current week the Date() function gives.

JR

 

[gemma-the-husky]

you will have to evaluate the week's date range (say startdate and enddate), and THEN your query can say


something like
=DSum("TotalSales","AuctionSales","[OrderDate] >between " & startdate & " and " & enddate)

but this probably won't work, because you need "#" characters round the dates, and this will resolve the dates as "US dates", not "UK dates"

SO - I would actually develop this in a stored (totals) query to test it - so you end up with a single value in the query - that you know is what you want

and then you can do a dlookup (rather than a dsum) to retrieve trhe value you want (because the query already does the dsum)