By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,524 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

question on date functions

P: 5
Hi,

I need your help with the date. If a date is given (for example the current date), how do i work out in VBA for Access database that it would give me the last day(friday) of the previous week and the last day(friday) of the next week?

Thank you in advance
Feb 11 '08 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi,

There may be a more simple logic to this but I'm tired :) and can't think of it at the moment. However, the following code will I think give you the results you want.

Expand|Select|Wrap|Line Numbers
  1. Dim dayOfWeek As Integer
  2. Dim lastFriday As Date
  3. Dim nextFriday As Date
  4.  
  5.     dayOfWeek = Weekday(Now())
  6.  
  7.     If dayOfWeek = 6 Then
  8.         nextFriday = Now() + 7
  9.         lastFriday = Now() - 7
  10.     ElseIf dayOfWeek = 7 Then
  11.         nextFriday = Now() + 6
  12.         lastFriday = Now() - 1
  13.     Else
  14.         nextFriday = Now() + (6 - dayOfWeek)
  15.         lastFriday = Now() - (dayOfWeek + 1)
  16.     End If
  17.  
Feb 11 '08 #2

P: 5
Thank you Msquared for your reply.

I tried the code - it works with Now(). But If i changed it to a specific date, it seems to give the wrong date for last and next week.

Instead of this "dayOfWeek = Weekday(Now())", i made it - dayOfWeek = Weekday("25/02/2008")

Is it because i put in the wrong format here?

Thank you in advance


Hi,

There may be a more simple logic to this but I'm tired :) and can't think of it at the moment. However, the following code will I think give you the results you want.

Expand|Select|Wrap|Line Numbers
  1. Dim dayOfWeek As Integer
  2. Dim lastFriday As Date
  3. Dim nextFriday As Date
  4.  
  5.     dayOfWeek = Weekday(Now())
  6.  
  7.     If dayOfWeek = 6 Then
  8.         nextFriday = Now() + 7
  9.         lastFriday = Now() - 7
  10.     ElseIf dayOfWeek = 7 Then
  11.         nextFriday = Now() + 6
  12.         lastFriday = Now() - 1
  13.     Else
  14.         nextFriday = Now() + (6 - dayOfWeek)
  15.         lastFriday = Now() - (dayOfWeek + 1)
  16.     End If
  17.  
Feb 12 '08 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Thank you Msquared for your reply.

I tried the code - it works with Now(). But If i changed it to a specific date, it seems to give the wrong date for last and next week.

Instead of this "dayOfWeek = Weekday(Now())", i made it - dayOfWeek = Weekday("25/02/2008")

Is it because i put in the wrong format here?

Thank you in advance
Use the following ...

dayOfWeek = Weekday(#25 Feb 2008#)
Feb 12 '08 #4

P: 5
Hi Msquared,

I tried that but as i press "enter" key to move to the next line, it automatically turned it to

dayOfWeek = Weekday(#2/25/2008#)

This gives the wrong result.

Anything else i should try?

Thank you in advance
Feb 12 '08 #5

missinglinq
Expert 2.5K+
P: 3,532
When you hit <Enter> Access is merely displaying the date in the correct format for your PC going by its Regional Settings. Mary's in the UK and Iím guessing youíre in the USA, hence

(#25 Feb 2008#) becomes (#2/25/2008#)

Just glancing, I see no reason why Maryís code shouldnít work, regardless of the date itís fed, but this code will do the same thing:

FridayLastWeek = (dateadd("d",6-weekday(YourDate),YourDate)) -7

FridayNextWeek = (dateadd("d",6-weekday(YourDate),YourDate)) +7

Fed the date 2/25/2008 the results will be 2/22/2008 and 3/7/2008, respectively.

Welcome to TheScripts!

Linq ;0)>


Feb 12 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.