1. Registered User
Join Date
Mar 2005
Posts
16

hello all,

i am trying to find the the last friday of the present month. I am using the following which gives me the last day of each month:

DateSerial(Year(Date()), Month(Date()) + 1, 0)

Hope someone can help, maybe im going about it wrong with the above!

Thanks for looking

iso

2. Computer Monkey
Join Date
May 2005
Posts
1,191
The following works, but I'm not sure if there is a more effective way. If you don't need to do this calc A LOT (querying thousands of records or something), it'll be fine.
Code:
```Dim dateLastFriday As Date
dateLastFriday = DateSerial(Year(Date), Month(Date) + 1, 0)

While Not Weekday(dateLastFriday) = vbFriday
Wend```

3. Registered User
Join Date
May 2004
Location
New York State
Posts
1,178
Try this:

Dim LastDay As Date, LastFriday As Date
LastFriday = LastDay - Weekday(LastDay ) + IIf(Weekday(LastDay ) >= 6, 6, -1)
Hope this helps,

Sam
Last edited by Sam Landy; 06-20-07 at 15:50.

4. Computer Monkey
Join Date
May 2005
Posts
1,191
Originally Posted by Sam Landy
Dim LastDay As Date, LastFriday As Date
LastFriday = LastDay - Weekday(LastDay ) + IIf(Weekday(LastDay ) >= 6, 6, -1)
Won't you have to use the DateAdd function to add days? I didn't think you could just add Date formats with number formats.

5. Registered User
Join Date
May 2004
Location
New York State
Posts
1,178
Copy and paste it into your Immediate window and keep changing the value of DateVariable. It works.

I think that dates are stored in Access as numbers, with the whole number(number to the left of the decimal) as the date, and the number to the right of the decimal as the time. In Excel it's definitely like that, so I assume in Access as well.

Sam

6. Registered User
Join Date
Mar 2005
Posts
16
Hello Sam & Nick and thanks for both of your replies,

Where do i put the code? Im just learning really.

Many Thanks

iso

7. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Someone wrote a very similar problem in this thread a few days ago. The link in there provides a nice and easy way(s) to calculate something similar to what you want. I advise you read it (it's fairly easy to follow) and see if you can understand the logic that is involved. Post any questions back here if you want

Next comes the question of where (or how) do you want this calculation to be displayed...

8. Registered User
Join Date
May 2004
Location
New York State
Posts
1,178
Originally Posted by iso96
Hello Sam & Nick and thanks for both of your replies,

Where do i put the code? Im just learning really.

Many Thanks

iso
Don't have a clue. You didn't show us any of your code.

Sam

9. Registered User
Join Date
Mar 2005
Posts
16
Sam,

Clearly i meant the code you wrote! But never mind ive sorted it thanks for all your help.

iso

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•