Query to capture records over the last 15 working days

[mattP]

I would like to set up a query that captures records entered from the previous 15 working days, not including today?

My table records the date entered but how do I tell it to gather 15 "working days" worth of data, up to yesterday or the previous day.?

Additionally I will be setting up excel to automatically pull in this data when a workbook is opened, and create charts from it.

Once again any help you can offer is greatly appreciated.

Thanks

MattP

 

[PARSO]

Try this

>Date()-15 And <Date()

 

[ColinEssex]

when you say "working days" presumably you mean not including saturday or sundays?

Col

 

[mattP]

Colin, yes I need to exclude Saturdays and Sundays, I have tried the following code in the query criteria for "Date Entered":

<=Date()-0 And >=Date()-15

However this does not giveme 15 working days of data, because the weekends are blank it just misses those days out, preferably I would like ot to show 15 workign days or 3 weeks.

Inicidentally, the -0 is there to make sure it does not include todays data, however if I set this to -1 then it also missed yesterdays data and if I did not include it then I got todays data.

Thanks
MattP

 

[razorking]

I have done something similar to this and just decided to create a table with entry for all of the working days. There is some data entry involved but not an overwhelming task.

I am sure there is a more graceful solution and would be interested in learning it myself. But if you need something as a temporary fix to buy some time or something you might think about the above.

See example:

 

[ColinEssex]

This code (albeit a bit long winded) will do what you need.

Create a form and put 2 TextBoxes on it (StartDate and EndDate) add a command button without the wizard. In the OnClick of the command button put this code

Dim z As Date, y As Date, a As Integer

a = 0
y = Date - 1

If Format(y, "dddd") = "Sunday" Then
y = Date - 3
End If

z = y
a = 1

Do Until a = 15
If Format(z, "dddd") = "Saturday" Or Format(z, "dddd") = "sunday" Then
a = a
z = z - 1
Else
a = a + 1
z = z - 1
End If
Loop

Me.StartDate = z
Me.EndDate = y

Basically what it does is checks yesterdays date wasn't a Sunday, if it was then take 3 days off todays date to give Fridays date - so now becomes the last date in your range.

(a) = your 15 days loop and (z) loops back to the value of (a) but if (z) is a weekend it skips it and carries on till (a) = the 15 days

Should you want to exclude public holidays as well then you'll need to make a table of the holiday dates and bring that into the loop

The 2 dates for your query are now popped in the textBoxes for you to use.

Col

 

[mattP]

Colin,

Thanks for the code, but I really need to do this without a form, I need excel to grab the data out of the query, without the user actually opening the DB or running a form. Is there any other coding that coudl be added to teh query itself ? or a module to run ?

I appreciate your advice though.

Many thanks

MattP

 

[mattP]

ColinEssex,

Thanks for the code, but I really need to do this without a form, I need excel to grab the data out of the query, without the user actually opening the DB or running a form. Is there any other coding that coudl be added to teh query itself ? or a module to run ?

I appreciate your advice though.

Many thanks

MattP

 

[mresann]

The Weekday function can also be used to prevent weekend days (but not holidays) from being included. You can decrement loop back from the current day, and check each day with the weekday function to determine the day.

As an example,

Weekday(datCurrentDate) would return:

1 if Sunday
2 if Monday
3 if Tuesday
4 if Wednesday
5 if Thursday
6 if Friday
7 if Saturday

However, there is an optional long that can be passed that lets you set up the FIRST number. So to make the calculation easier, do this:

Weekday(datCurrentdate, vbSaturday )

NOW the algorythm is as follows:

1 if Saturday
2 if Sunday
3 if Monday
4 if Tuesday
5 if Wednesday
6 if Thursday
7 if Friday

Basically, the procedure would be like this:

If Weekday(datCurrentdate, vbSaturday ) > 2 Then
'Current date is weekday,
Else
'Current date is Saturday or Sunday
End if

Hope this helps you in the right direction.

 

[neileg]

Colin,

Thanks for the code, but I really need to do this without a form, I need excel to grab the data out of the query, without the user actually opening the DB or running a form. Is there any other coding that coudl be added to teh query itself ? or a module to run ?

I appreciate your advice though.

Many thanks

MattP

If you turn this code into a function you can use the function in your query.