Using If-Then in Query - Can I?

[wirewendy]

We are trying to change the parameters of a query based on what day it is today.

If it is Tuesday, do this.
If it is Wednesday, do this.

What syntax do we use?

 

[tlbroadbent]

There are some options available but without more info it is impossible to say what you need to do. Let me give you some posibilities.

Use datepart("w",date()) to get the day of the week where 1=Sun, 2=Mon, 3=Tue, ...

You can use the Choose function in conjunction with datepart to determine some values.

Choose(datepart("w",date()),'Sun value','Mon value', ..., 'Sat value')

You can write a function in VBA and use the function in a query. In this case I use the Select Case statement.

Function DetermineValue ()

Select Case datepart("w",date())
Case 1
<Case 1 logic>
DetermineValue = SundaySales 'just a sample
Case 2
<Case 2 logic>
Case 3
<Case 3 logic>
.
.
.
Case Else
msgbox &quot;What? Not a day of the week!&quot;
End Select

End Function

Hope these help a little. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[Broccoli2]

Another alternative would be to use

iif(datepart("w",date())=3,"Yippee it's Tuesday","Not Tuesday today then")

but this may not exactly be the logic you want (only gives you alternatives for 'Tuesday' or 'not Tuesday'). However, you can use it direct in an SQL query.

-Rob