Sales Year To Date From End Of Previous Week AND For Last Year

0
votes

I am trying to write a query in Access to poll year to date sales records to build a report. I think I need to write a VBA module to calculate these dates.

I need a date range for Year to Date from Last Saturday (ex. from 1/1/2015 to 10/24/2015 for this week's report). I also need to poll the records from Last Year To Date (ex. from 1/1/2014 to 10/25/2014 for this week's report).

Any suggestions on how I can query this date range?

Thanks!

2
vbams-access

2 Answers

0
votes

Consider using a temporary table such as a YTDDates of two fields (ID, SalesDate) that holds the date range which can be cleaned out per needed use. Then join your sales data to this table for daily reporting.

VBA can run a loop through all days from beginning (1/1/2015) to end (10/24/2015) and iteratively append to temp table using the DateAdd() function.

Dim db As Database
Dim startDate As Date, endDate As Date, d As Date
Dim diff As Integer, i as Integer

Set db = CurrentDb()

startDate = #1/1/2015#
endDate = #10/24/2015#    
diff = DateDiff("d", startDate, endDate)

' CLEAN OUT PRIOR DATE RECORDS
db.Execute "DELETE FROM YTDDates", dbFailOnError

For i = 0 To diff

    d = DateAdd("d", i, startDate)
    db.Execute "INSERT INTO YTDDates ([SalesDate]) VALUES (#" & d & "#);", dbFailOnError

Next i

Set db = Nothing
0
votes

Instead of a temp table I would just use a formula in your query using a BETWEEN clause on your date field with a formula like this:

vba.DateSerial(Year(Now()),Month(Now()),Day(Now())-Weekday(Now(),vbSunday))

The Weekday function returns an integer from Sunday (vbSunday). So on a Monday it will return '2' and on Saturday it will return '7' which will give you the previous week's Saturday date.

For getting the beginning of the year date you could use a function like this:

vba.DateSerial(Year(Now()),1,1)