Nested iif statements to set date fields

[dascooper]

I'm trying to set a date using the following formula:

ratebase_date: iif([formula_rate] = "False", [rate_case-date],
iif(month([planned_isd])<6,
date(year([planned_isd]),6,1),date(year([planned_isd]+1),6,1))

If the logic field is false, it pulls the data from a table.

If the logic field is true, it looks to see if the date is before June. If
yes, it returns 6/1 of that year. Otherwise, it returns 6/1 of the next
year.

I keep getting an error that there are the wrong number of arguments, but I
can't seem to get it right. Any help would be much appreciated

 

[KARL DEWEY]

Try this --
ratebase_date: iif([formula_rate] = "False", [rate_case-date],
iif(month([planned_isd]) <6,
date(year([planned_isd]),6,1),date(year([planned_isd])+1,6,1))

 

[Ofer Cohen]

If the formula_rate field is Yes/No field, remove the double quote, and
replace the Date Function with DateSerial

Try:

iif([formula_rate] = False, [rate_case-date],
iif(month([planned_isd])<6,
DateSerial(year([planned_isd]),6,1),DateSerial(year([planned_isd]+1),6,1))

 

[Ofer Cohen]

Sorry, in the second option the +1 should be outside the brackets

iif([formula_rate] = False, [rate_case-date],
iif(month([planned_isd])<6,
DateSerial(year([planned_isd]),6,1),DateSerial(year([planned_isd])+1,6,1))

--
Good Luck
BS"D

If the formula_rate field is Yes/No field, remove the double quote, and
replace the Date Function with DateSerial

Try:

iif([formula_rate] = False, [rate_case-date],
iif(month([planned_isd])<6,
DateSerial(year([planned_isd]),6,1),DateSerial(year([planned_isd]+1),6,1))


--
Good Luck
BS"D

I'm trying to set a date using the following formula:

ratebase_date: iif([formula_rate] = "False", [rate_case-date],
iif(month([planned_isd])<6,
date(year([planned_isd]),6,1),date(year([planned_isd]+1),6,1))

If the logic field is false, it pulls the data from a table.

If the logic field is true, it looks to see if the date is before June. If
yes, it returns 6/1 of that year. Otherwise, it returns 6/1 of the next
year.

I keep getting an error that there are the wrong number of arguments, but I
can't seem to get it right. Any help would be much appreciated

 

[dascooper]

Thanks - I see the change in the +1 year field. I am still getting the wrong
number of arguments error though.

Try this --
ratebase_date: iif([formula_rate] = "False", [rate_case-date],
iif(month([planned_isd]) <6,
date(year([planned_isd]),6,1),date(year([planned_isd])+1,6,1))

--
KARL DEWEY
Build a little - Test a little

I'm trying to set a date using the following formula:

ratebase_date: iif([formula_rate] = "False", [rate_case-date],
iif(month([planned_isd])<6,
date(year([planned_isd]),6,1),date(year([planned_isd]+1),6,1))

If the logic field is false, it pulls the data from a table.

If the logic field is true, it looks to see if the date is before June. If
yes, it returns 6/1 of that year. Otherwise, it returns 6/1 of the next
year.

I keep getting an error that there are the wrong number of arguments, but I
can't seem to get it right. Any help would be much appreciated

 

[dascooper]

Perfect! Thanks a bunch!!

Sorry, in the second option the +1 should be outside the brackets

iif([formula_rate] = False, [rate_case-date],
iif(month([planned_isd])<6,
DateSerial(year([planned_isd]),6,1),DateSerial(year([planned_isd])+1,6,1))

--
Good Luck
BS"D

If the formula_rate field is Yes/No field, remove the double quote, and
replace the Date Function with DateSerial

Try:

iif([formula_rate] = False, [rate_case-date],
iif(month([planned_isd])<6,
DateSerial(year([planned_isd]),6,1),DateSerial(year([planned_isd]+1),6,1))


--
Good Luck
BS"D

I'm trying to set a date using the following formula:

ratebase_date: iif([formula_rate] = "False", [rate_case-date],
iif(month([planned_isd])<6,
date(year([planned_isd]),6,1),date(year([planned_isd]+1),6,1))

If the logic field is false, it pulls the data from a table.

If the logic field is true, it looks to see if the date is before June. If
yes, it returns 6/1 of that year. Otherwise, it returns 6/1 of the next
year.

I keep getting an error that there are the wrong number of arguments, but I
can't seem to get it right. Any help would be much appreciated

 

[dascooper]

The first part of the query works - I've added a bit more and am having
ongoing problems:

ratebase_date:
IIf([formula_rate]=False,IIf([planned_isd]>[rate_case-date],DateSerial(2099,1,1),[rate_case-date]),IIf(Month([planned_isd])<6,DateSerial(Year([planned_isd]),6,1),DateSerial(Year([planned_isd])+1,6,1)))

The part I added is the iif right after the false check. It is designed to
compare two dates, returning a default of 1/1/2099 or a valid value back.
The code runs, but the ones that return a false all show up as #error. Any
help would be appreciated.

 

[dascooper]

Never mind - I had a table design issue that once fixed resolved all my other
problems. Thanks for the help

The first part of the query works - I've added a bit more and am having
ongoing problems:

ratebase_date:
IIf([formula_rate]=False,IIf([planned_isd]>[rate_case-date],DateSerial(2099,1,1),[rate_case-date]),IIf(Month([planned_isd])<6,DateSerial(Year([planned_isd]),6,1),DateSerial(Year([planned_isd])+1,6,1)))

The part I added is the iif right after the false check. It is designed to
compare two dates, returning a default of 1/1/2099 or a valid value back.
The code runs, but the ones that return a false all show up as #error. Any
help would be appreciated.

I'm trying to set a date using the following formula:

ratebase_date: iif([formula_rate] = "False", [rate_case-date],
iif(month([planned_isd])<6,
date(year([planned_isd]),6,1),date(year([planned_isd]+1),6,1))

If the logic field is false, it pulls the data from a table.

If the logic field is true, it looks to see if the date is before June. If
yes, it returns 6/1 of that year. Otherwise, it returns 6/1 of the next
year.

I keep getting an error that there are the wrong number of arguments, but I
can't seem to get it right. Any help would be much appreciated