IIF Statement Syntax for Multiple Conditions

[Chris_Cline]

Newby here, Having an issue trying to get the correct syntax on this statement with multiple conditions. Below is the code and the results. The dates are being calculated correctly. My intent was for the period end date field for one-time payments to read "One Time Payment". Instead I Get #ERROR, I have tried different arrangements of where to place the final condition to no avail. Any help would be appreciated.


PeriodEndDate:
IIf([Lease].[BILLING FREQUENCY]="MONTHLY",(DateAdd("m",1,[EFF DATE])-1),
IIf([Lease].[BILLING FREQUENCY]="QUARTERLY",(DateAdd("q",1,[EFF DATE])-1),
IIf([Lease].[BILLING FREQUENCY]="SEMI-ANNUAL",(DateAdd("m",6,[EFF DATE])-1),


IIf([Lease].[BILLING FREQUENCY]="ANNUAL",(DateAdd("yyyy",1,[EFF DATE])-1),"One Time Payment"))))

BILLING FREQUENCY	PeriodEndDate
MONTHLY	2/28/2021
ANNUAL	1/31/2022
MONTHLY	2/28/2021
ONE-TIME
ANNUAL	2/6/2022
QUARTERLY	6/30/2021
SEMI-ANNUAL	7/31/2021
ANNUAL	2/11/2022
MONTHLY	2/28/2021
ANNUAL	2/3/2022
ANNUAL	2/11/2022
ANNUAL	2/22/2022
ANNUAL	2/26/2022
ANNUAL	1/31/2022
ANNUAL	2/26/2022
MONTHLY	2/28/2021

[ranman256]

Put all this on the form. User picks the Billing Frequency.
It set the End Date textbox.
the query does the rest:

[Micron]

First thing that comes to mind is that there is at least 1 record where frequency is ANNUAL but [EFF Date] is Null.

[Gicu]

It might be that you are trying to force a string into a date field. Can you try to wrap the DateAdds in Format()which will convert them to text as well:

IIf([Lease].[BILLING FREQUENCY]="MONTHLY",Format((DateAdd("m",1,[EFF DATE])-1),"MM/DD/YYYY"),
IIf([Lease].[BILLING FREQUENCY]="QUARTERLY",Format((DateAdd("q",1,[EFF DATE])-1),"MM/DD/YYYY"),
IIf([Lease].[BILLING FREQUENCY]="SEMI-ANNUAL",Format((DateAdd("m",6,[EFF DATE])-1),"MM/DD/YYYY"),
IIf([Lease].[BILLING FREQUENCY]="ANNUAL",Format((DateAdd("yyyy",1,[EFF DATE])-1),"MM/DD/YYYY"),"One Time Payment"))))

Cheers,

[Micron]

Format function returns a string, so all formatted results would be strings? Yet only the last one fails even though all results go into the same field.

[Gicu]

Can you try to wrap the DateAdds in Format()which will convert them to text as well:

I should have said "string"
I was trying to convert all to a uniform data type; maybe leave it the way it was and use #12/31/1900# instead of "One Time Payment" and see what happens.
@micron not sure I got your first suggestion right but if an ANNUAL would fail because of a Null date how would it show as ONE TIME #ERROR.

Cheers,

[Welshgasman]

I am really confused as to why anyone would try and put a string into a date field?

[Chris_Cline]

Thanks,
This worked. Curious as to how else this could have been written to make this work:
IIf([Lease].[BILLING FREQUENCY]="MONTHLY",(DateAdd("m",1,[EFF DATE])-1),
IIf([Lease].[BILLING FREQUENCY]="QUARTERLY",(DateAdd("q",1,[EFF DATE])-1),
IIf([Lease].[BILLING FREQUENCY]="SEMI-ANNUAL",(DateAdd("m",6,[EFF DATE])-1),
IIf([Lease].[BILLING FREQUENCY]="ANNUAL",(DateAdd("yyyy",1,[EFF DATE])-1),
IIf([Lease].[BILLING FREQUENCY]="ONE-TIME","One Time Payment","other")))))

[Chris_Cline]

It is intended to go into an invoice for rentals on RR property

Typically rent payments are for a certain period of time whether monthly or other
Sometimes there are lump sum payments that cover a perpetual occupancy in this case I want the field that shows the period end date to state "One Time Payment" as there is no end date.

[Gicu]

I used to get the #error in a form bound to a query in which I was calculating the age of a student then binging the textbox on the form to the calculated field in the query. I was able to solve it by simply moving the same calculation to the control source property of the rextbox on the form (so not having the control bould to the query field but doing the calculation in the form for the current record).

I would be curious to see what happens if you attempt the same (I assume your invoice is a report), use your initial formula in the control source of the textbox that displays the end date.

Cheers,
Vlad

[Micron]

Not sure what you mean by "how would it show as ONE TIME #ERROR."
The reported error is "#ERROR" ?

IMO it would only take one record where eff date is null where the frequency value is "ANNUAL" and the result would be #ERROR. Perhaps it is something else, but how difficult is it to look at the table field or create a test query to look for Null in said field?

[Gicu]

My intent was for the period end date field for one-time payments to read "One Time Payment". Instead I Get #ERROR, I have tried different arrangements of where to place the final condition to no avail.

@micron I think the OP said that the #ERROR shows up for the "One Time" frequency record(s) , not for "Annual" one(s). And in his example only the One Time record had an empty date, so I don't know how you get to Annual as you suggested in your post.

Cheers,

[Micron]

IIf([Lease].[BILLING FREQUENCY]="ANNUAL",(DateAdd("yyyy",1,[EFF DATE])-1),"One Time Payment"))))

My take was:
Table is Lease; field is [BILLING FREQUENCY] value being tested is "ANNUAL"; choice is to be either [EFF DATE] or "One Time Payment" based on value.
There are no "One Time Frequency" values in the records. I saw the posted data as a result not the source data, and where the frequency output is "One Time" [EFF DATE] (if that's what PeriodEndDate represents) the date is missing, hence I suggested the Null issue. You and I are looking at things differently and I guess I missed the fact that a solution was accepted so I'm done here.

[ssanfu]

Shouldn't have spaces in object names.
([BILLING FREQUENCY] --> Better would be BillingFrequency
[EFF DATE] --> better would be EffDate

Use Camel Case instead of all Caps

Post #8

<snip> Curious as to how else this could have been written to make this work:

Consider

SELECT CASE [Lease].[BILLING FREQUENCY]
    Case "MONTHLY"
        PeriodEndDate = DateAdd("m",1,[EFF DATE])-1
    Case "QUARTERLY"
        PeriodEndDate = DateAdd("q",1,[EFF DATE])-1
    Case "SEMI-ANNUAL"
        PeriodEndDate = DateAdd("m",6,[EFF DATE])-1
    Case "ANNUAL" 
        PeriodEndDate = DateAdd("yyyy",1,[EFF DATE])-1
    Case "ONE-TIME"
        PeriodEndDate = "One Time Payment"
    Case Else
        PeriodEndDate = "Other"
End Select

Post #9

It is intended to go into an invoice for rentals on RR property

So somewhere you have a RenterID? Would like to see the rest of the code.
Normally do not use [Lease].[BILLING FREQUENCY] nomenclature. (at least I don't.)


But...you seem to have what you want...... good luck with your project.