Hi Scott
Thank you for your reply.
Firstly I am very new with VBA and in fact this is my first attempt at anything constructive.
The Data Type is indeed Date/Time but the format is dd/mm/yyyy. The field I am using is "Date Created", there is a separate field for "Time Created" but for the purposes of this project the time is irrelevant.
I would be extremely grateful for any help you can give me, I am running out of permutations to try and perhaps you might also be able to point me in the right direction for some basic principles.
Many thanks
Bill
In your query design view grid, go to the criteria for the [Date Created] field. Put this into the box: Yesterday(Date())
Then save the query and close it. Next press Alt+F11 to bring up the VBA editor window. Click the Insert menu, and choose Module. In the new module window that comes up, paste this code in:
-
Option Explicit
-
-
Public Function Yesterday(MyDay As Date) As String
-
-
Dim MyDate As Date
-
-
If WeekDay(MyDay) = 2 Then 'If MyDay is Monday
-
MyDate = MyDay - 3 'Subtract 3 days
-
ElseIf WeekDay(MyDay) = 1 Then 'If MyDay is Sunday
-
MyDate = MyDay - 2 'Subtract 2 days
-
Else
-
MyDate = MyDay - 1 'If any other day subtract 1 day
-
End If
-
-
Yesterday = Format(MyDate, "dd/mm/yyyy")
-
-
End Function
You should delete the line numbers as they just confuse the issue. Click the Debug menu, click Compile. Save changes, go back to the database window and run your query.
If you have any problems, let me know!
Regards,
Scott
3
