IIf statement with a Nz

[Todd]

All
Need a little more help.
I have the following in a field to determine what date to drive the
computation from.
It is not working correctly though.
1_90_Day_refresher:
IIf(Nz([PC_Function1])=0,Format(DateAdd("d",90,[line_check_out]),"dd-mmm-yy"))
I have three fields.
Line_Check_out, for when someone checks out.
PC_Function1, for when someone needs a PC refresher test.
These fill the 1_90_Day_refresher depending on what they have done.
When they check out, this date will initially drive the first 90 day
refresher.
After they have requalled, this will drive their 90 day refresher.
So, initially, PC_Function1 will be blank, so the checkout date will drive
the new date for the refresher date.

Currently, I only get a date in the field 1_90_Day_refresher if a date is in
PC_Function1 field. If anything is in the Line_Checkout field, nothing is
computed for the 1_90_Day_refresher date.
Any help would be appreciated.

Todd

 

[Todd]

Had to make a fix to my statement below.

 

[Allen Browne]

If you use Format(), Access will treat the results as Text (not date/time),
so the results won't filter/sort correctly.

If you intend the field to be a date, try an expression like this:
CVDate(IIf([PC_Function1] Is Null Or [PC_Function1] = 0,
[line_check_out] + 90), Null))

What that does is to check for either Null or zero (without calling the VBA
IsNull() function because JET knows about nulls.) It adds the 90 days if the
condition is not, or returns Null if not, and finally type-casts to a date.

The results should right-align (as a date), not left-align (like text.)

 

[Todd]

Allen

Thanks for your assistance. The idea you posted below did not work for me.
Maybe I was not clear enough on what I need the query to do.

If pc_Function1 is blank, add 90 days to Line_Check_out date.
If PC_Function1 is not blank, add 90 days to this date.

These are both feeding the 1_90_Day_refresher depending on the criteria above.

This one is kicking my butt at the moment.

Thanks

If you use Format(), Access will treat the results as Text (not date/time),
so the results won't filter/sort correctly.

If you intend the field to be a date, try an expression like this:
CVDate(IIf([PC_Function1] Is Null Or [PC_Function1] = 0,
[line_check_out] + 90), Null))

What that does is to check for either Null or zero (without calling the VBA
IsNull() function because JET knows about nulls.) It adds the 90 days if the
condition is not, or returns Null if not, and finally type-casts to a date.

The results should right-align (as a date), not left-align (like text.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

All
Need a little more help.
I have the following in a field to determine what date to drive the
computation from.
It is not working correctly though.
1_90_Day_refresher:
IIf(Nz([PC_Function1])=0,Format(DateAdd("d",90,[line_check_out]),"dd-mmm-yy"))
I have three fields.
Line_Check_out, for when someone checks out.
PC_Function1, for when someone needs a PC refresher test.
These fill the 1_90_Day_refresher depending on what they have done.
When they check out, this date will initially drive the first 90 day
refresher.
After they have requalled, this will drive their 90 day refresher.
So, initially, PC_Function1 will be blank, so the checkout date will drive
the new date for the refresher date.

Currently, I only get a date in the field 1_90_Day_refresher if a date is
in
PC_Function1 field. If anything is in the Line_Checkout field, nothing is
computed for the 1_90_Day_refresher date.
Any help would be appreciated.

Todd

 

[Allen Browne]

Okay, I'm not sure what 'did not work' means. Error? No answer? Wrong
answer?

If pc_Function1 is a date also, perhaps you want:
IIf([pc_Function1] Is Null, [Line_Check_out] + 90, [pc_Function1] + 90)

 

[Todd]

Allen

This one works.
The last one had an error with something about the function and arguments.
I have it wrote down at home, and left it there.

Thanks for your help.

Todd

Okay, I'm not sure what 'did not work' means. Error? No answer? Wrong
answer?

If pc_Function1 is a date also, perhaps you want:
IIf([pc_Function1] Is Null, [Line_Check_out] + 90, [pc_Function1] + 90)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

Thanks for your assistance. The idea you posted below did not work for
me.
Maybe I was not clear enough on what I need the query to do.

If pc_Function1 is blank, add 90 days to Line_Check_out date.
If PC_Function1 is not blank, add 90 days to this date.