Help with DateAdd("d",-1,Date() feature or similar (1 Viewer)

ahdlotcd

New member
Local time
Today, 07:15
Joined
Feb 12, 2020
Messages
2
Good morning from the UK!

I have been trying to build a query for a little while using the DateAdd("d",-1,Date() function, but I'm starting to think this is not the right function for my task.

Essentially, I'm trying to return values from a set period, i.e. if today's date is the 12/02/20 (it is at time of writing), I would like to return the previous days data, however I require the function to ignore weekends and other non trading days. Therefore, in the example today's date was the 10/02/20, the query would return values from the 07/02/20 rather than look for values from the 09/02/20.

Now obviously, I could change the code to say -3, but I would like this to be 'once written it's done' function. Furthermore, I would like to have further queries, that return data for the last 10 days, 20 days etc.

I have tried creating an additional table that would only contain the specific dates, and linked that to my main "Sales Data" table, but to no avail.

I'd be grateful to anyone that can help. Screenshot attached.
 

Attachments

isladogs

CID Moderator
Staff member
Local time
Today, 07:15
Joined
Jan 14, 2017
Messages
12,598
You could use DateAdd in conjunction with an IIf function.
However I would suggest you create a user defined function to do this
 

ahdlotcd

New member
Local time
Today, 07:15
Joined
Feb 12, 2020
Messages
2
Thanks for the quick reply.
Could you give me a further steer on how you would go about that?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:15
Joined
Feb 19, 2013
Messages
11,863
whilst doing that - you may also need to take into account bank holidays
 

isladogs

CID Moderator
Staff member
Local time
Today, 07:15
Joined
Jan 14, 2017
Messages
12,598
Lots of possible methods e.g.
SQL:
IIf(Format(Date(),"ddd")="Mon", Date-3, Date-1)
However that won't allow for public holidays.
In order to handle those, you may be better to set up a lookup table tblCalDates with 3 fields
CalID (autonumber PK), CalDate (DateTime), WorkingDay (Boolean)

Populate with all dates for 2020 and check the final field for all working days.
You could then use a DMax expression to get the most recent date before today where WorkingDay =True.
Something like this
SQL:
DMax("CalDate", "tblCalDates", "CalDate < Format(Date, "mm/dd/yyyy") And WorkingDay=True")
 

isladogs

CID Moderator
Staff member
Local time
Today, 07:15
Joined
Jan 14, 2017
Messages
12,598
Lots of other possible methods as well ...
Dmax ... really? Noooo !
LOL.
I did say there were lots of possible approaches.
If its in a query, I would definitely not use a domain function.
Feel free to suggest an alternative
 

ebs17

Member
Local time
Today, 08:15
Joined
Feb 7, 2020
Messages
64
I did say there were lots of possible approaches.
If you wanted to optimize:
In a well-thought-out query, some would not group around 100 fields after their contents were only reproduced by JOINs, but leave grouping and aggregation in the "01 New Sales Table" table and link this query with the other tables.

Since there is a calendar table visible, you don't need fields such as month, year etc. again in the data table.

Eberhard
 

isladogs

CID Moderator
Staff member
Local time
Today, 07:15
Joined
Jan 14, 2017
Messages
12,598
Forgot to say earlier , you could also use a query based on tblCalDates, filter for dates less than current date, sort in descending order with most recent date first and then get first record only. Air code for that

SQL:
SELECT TOP 1 CalDate FROM tblCalDates WHERE CalDate < Date AND WorkingDay=True ORDER BY CalDate DESC
If done as SQL you would again need to format as mm/dd/yyyy
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:15
Joined
Feb 19, 2002
Messages
28,631
You need to create a function that given a date returns the previous work day. The function will need a table of holidays and also recognize weekend days. Then use the function in your query:

Where SomeDate = LastWorkDate(Date())

I passed in today's date but do whatever you need.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom