Trying to use these three criteria in an IIF statement

[bmal]

Here are the criteria I am using:

Current Week: DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())

Current Month: Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())

Previous Month: Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1


I am able to successfully set one of the above as a criteria just fine.

I am stalled on trying to combine two or more of the above in an iif statement. Here is what I 'think it should look like:

Funded_Date_Period: IIF([funded_date] = "Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1)", "PrevMonth", IIF([funded_Date]= "Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())", "CurrentMonth", IIF([funded_date]="DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())", "CurrentWeek")

In other words, in a separate field, I would like to print a period name such as above.

I believe I am wrong using = or like, but I have not been able to find any reference to connect these long criteria together in one query expression.

My workaround is to use a union query. That works, but it is a little clumsy due to some outer joins which require a separate query. If that is my only solution, I will run with it. However, I thought I would seek guidance here first.

Thanks in advance.

 

[plog]

It's time to move your logic to a function inside a Module. Instead of trying to jam all those comparisons into one line, create a custom function, pass it [Funded_Date] and use as many lines as you need to determine what the result should be. I'll start you out:

Function IsCurrentWeek(FD)
' returns True or false based on if FundedDate (FD) is in current week
 
Dim ret As Boolean
ret=true
' default return value is true, if fails any of below tests, its set to false
 
If (DatePart("ww", FD) <> DatePart("ww", Date()) ret=false
 
IsCurrentWeek = ret
 
End Function

Paste the above into a module, add more of your logic and then use it in a query by using this code:

CurrentWeek: IsCurrentWeek([Funded_Date])

 

[bmal]

Thanks, plog, for getting me started.

I will continue this over in the Modules forum.

 

[plog]

Why? You should be able to complete this on your own from here.

 

[bmal]

You're right, I should.

But I honestly never write modules. I am ignorant about them. All my work in tables/queries/macros/forms/reports.

The more I read and practice with modules the more proficient I will be.

I took what you started and expanded upon it in that area in a quoted post.