IIf statement help

[smiley0q0]

ok, i have a table with [Date] & [Amount] i need to pull some specific dates. for example, i need to pull 2/16/01, if 2/16/01 is a sun or sat then i need to pull the friday before. there are no dates in my table for the weekend so i have been trying to use this formula:
IIf(DateAdd("m",-3,Date())-1 Is Null,(DateAdd("m",-3,Date())-2),(IIf(DateAdd("m",-3,Date())-2 Is Null,(DateAdd("m",-3,Date())-3),DateAdd("m",-3,Date())-1)))

but it does not work right. it works if the date is a sat, but not a sun. is there a better way to do this? i am doing this in the criteria of a query.
the reason i subtract one to start off with is because i always need the day befores info. and i need the same day for the past 12 months. so i need
3/16/01
2/16/01
1/16/01
12/16/00 etc.

any help would be greatly appreciated.

Thanks
Jared

 

[Katerine]

Hi smiley0q0,
Try the following:

DateAdd("d",IIf(Format(Date(),"ddd&quot = "Sat",-1,IIf(Format(Date(),"ddd&quot = "Sun",-2,0)),Date())

Hope that helps (please use with caution.. it's off the top of my head and I haven't tested it).

 

[DougP]

I like to use a "between" for that instead.

=Between LastMonday() AND LastFriday()

so LastMonday()
and
LastFriday()
are functions that determine dates are not on a Sunday or Saturday

---------------
Function LastMonday ()

Year1 = Year(Now)
Month1 = Month(Now)
Day1 = Day(Now)
'Debug.Print Year1, Month1, Day1
LastWeek = Format(Now, "WW&quot - 1

DayOWeek = Format(Now, "w&quot
'Debug.Print LastWeek, DayOWeek
Select Case DayOWeek
Case 1
Num = 6
Case 2
Num = 7
Case 3
Num = 8
Case 4
Num = 9
Case 5
Num = 10
Case 6
Num = 11
Case 7
Num = 12
End Select
LastMonday1 = DateSerial(Year1, Month1, Day1 - Num)
LastMonday2 = LastMonday1
Debug.Print "LastMonday1 "; LastMonday1
LastMonday = LastMonday1
End Function
------------------
Function LastFriday ()
Year1 = Year(Now)
Month1 = Month(Now)
Num3 = 4
LastFriday2 = DateSerial(Year1, Month1, Val(Format$(LastMonday2, "dd&quot) + Num3)
Debug.Print "LastFriday2 "; LastFriday2
LastFriday = LastFriday2
End Function
-------------------
Put the following variable "LastMonday2 " in the Declarations area of the Module.
like so

Global LastMonday2 DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[dainamae]

I've used the Weekday function in the past.

IIf(Weekday([date])=1,DateAdd("d",[date],-2),IIf(Weekday([date])=7,DateAdd("d",[date],-1),DateAdd("d",[date],-1)))

 

[smiley0q0]

AWESOME!!! THANK YOU!!! Katerine, i used yours, it was a bit shorter than dainamae's and Doug, i'm not quite into vba, but i saved your code, so i could use it in the future when i am a little more comfortable with it.
thank you again. i really appreciate it.