IF statement in Query Design View Criteria.

[TallMan]

Hello All,

Could someone help me with a query question? The error message I am getting is "Undefined Function "IF" in expression"

I am trying to build an if statement in the criteria section on field [Pay_date].

Is it possible to do this? My IF statement is below:

DtTest: if(Weekday(Date())="6",[Main_PPY_TBL]![Pay_Date]=Day(Date())+8 or ",[Main_PPY_TBL]![Pay_Date]=Day(Date())+9,[Main_PPY_TBL]![Pay_Date]=Day(Date())+7)

The Pay date field is a day field, so it is anywhere from 1 to 31. Basically I need the query to pull, "if the day the query is ran is on Friday then pull two additional days, where are day(date())+8 and day(Date())+9, if it is any other day then just pull day(date())+7.

I am not sure if I can enter this into the criteria section of the field. I have also tried entering it in the Field sectiona as well.

Thanks for everything!

TallMan

 

[MarkK]

Immediate If is IIF()

 

[TallMan]

Sorry I forgot to mention I tried both IFF and IF....

So it is possible to build the IF statement in the actual field value of the same field you are trying to determine the IF statement from?

 

[boblarson]

IF doesn't work in queries.

IIF (two I's) is the one you would want in a query.

And the field would be the PayDate field and this would be the criteria.

 

[TallMan]

Got it to work but I can only pull the first day(date()) + 8, it is not pulling both the day(date()) + 8 and the day(date()) + 9. I tried putting an OR between the two.
Any ideas?

Thanks for your help guys!

 

[boblarson]

Well, the way you're doing it you can't really get more than one value at a time. So, if you need multiples, you might be looking at maybe using code to modify the query's where clause before you open it.

 

[TallMan]

Thanks Bob,

My intention is to build this piece of the code within the query itself, once I bring this into the VBA "SQL String" I am going to be adding some other pieces. I am not sure how to put them all togather in vba. I was hoping I could get the "day level" done in the query and then pull the month level into the actual VBA...


An example of what I am trying to do.

I have a text box that pulls the month in a number Titled month_NBR

IF me.month_NBR = "9" Then

SELECT Main_PPY_TBL.AutoNBR, Main_PPY_TBL.Account, Main_PPY_TBL.Amount, Main_PPY_TBL.PPM_Code, Main_PPY_TBL.Pay_Date, Main_PPY_TBL.Pay_Month
FROM Main_PPY_TBL
WHERE (((Main_PPY_TBL.Pay_Date)=IIf(Weekday(Date())="6",(Main_PPY_TBL.Pay_Date)=Day(Date())+8 Or (Main_PPY_TBL.Pay_Date)=+9)));"

obviously I am not formatting the sql as I wound in the code window, I am simply trying to give you an idea of what I am doing. Again I am not sure how to tell it......

If the month is this and the day is 6 then


I thought it would be a bit simpler to build the one iif in the query...

Thoughts?

 

[boblarson]

Actually, perhaps a function would be good in this case:

Function GetCriteria() As String
Dim strCrit As String
 
Select Case WeekDay(Date)
Case 6
    strCrit = "[Main_PPY_TBL].[Pay_Date] IN(" & Day(Date)+8 & "," &  Day(Date)+9 & ")"
Case Else
    strCrit = ""
End Select
 
GetCriteria = strCrit
 
End Function

And then you can use a QDF to replace the Where clause (using Access MVP Armen Stein's ReplaceWhereClause code which is in the basSQL module here.)

 

[TallMan]

Thank You Bob-

I will look into this. Looks a little complicated but I think I got it!