question on date functions

[associates]

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

[MMcCarthy]

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.

[code=vb]
Dim dayOfWeek As Integer
Dim lastFriday As Date
Dim nextFriday As Date

dayOfWeek = Weekday(Now())

If dayOfWeek = 6 Then
nextFriday = Now() + 7
lastFriday = Now() - 7
ElseIf dayOfWeek = 7 Then
nextFriday = Now() + 6
lastFriday = Now() - 1
Else
nextFriday = Now() + (6 - dayOfWeek)
lastFriday = Now() - (dayOfWeek + 1)
End If
[/code]

[associates]

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.

[code=vb]
Dim dayOfWeek As Integer
Dim lastFriday As Date
Dim nextFriday As Date

dayOfWeek = Weekday(Now())

If dayOfWeek = 6 Then
nextFriday = Now() + 7
lastFriday = Now() - 7
ElseIf dayOfWeek = 7 Then
nextFriday = Now() + 6
lastFriday = Now() - 1
Else
nextFriday = Now() + (6 - dayOfWeek)
lastFriday = Now() - (dayOfWeek + 1)
End If
[/code]

[MMcCarthy]

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#)

[associates]

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

[missinglinq]

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(YourDat e),YourDate)) -7

FridayNextWeek = (dateadd("d",6-weekday(YourDat e),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)>