Query

[mardimus]

I have a query that has three columns, Date1, Date2, and Date3. What I want to do is use an IF statement to create a new column based on the greater value of two of those dates out of the three. In my query design would I use a "display text", IIF, etc. Please share your knowledge.

Here is the logic....

IF(Date2="",IF(Date1="",Date3,Date1),Date2)

So the output of the statement would create a new column. Column 1 - Date1, Column 2 - Date2, Column 3 - Date3, Column 4 - output of statement!

Thanks for looking.

 

[mardimus]

This is the statement I first used in the builder...
Past Due RFU: IF([Cur RFU]="",IF([Pla RFU]="",[Act RFU],[Pla RFU]),[Cur RFU])

I got an error that the If expression is not defined.

So I changed the statement to: Past Due RFU: IIF([Cur RFU]="",IIF([Pla RFU]="",[Act RFU],[Pla RFU]),[Cur RFU])

And it runs but I have errors in the fields or blank fields.

 

[Joe4]

And it runs but I have errors in the fields or blank fields.

Note that Null and blank values are handled differently in Access.
But I think that would just return unexpected results and not errors.
Can you post some data examples and expected results?

 

[mardimus]

Thanks for the response. I am still a little new to access but so far its been great learning. Here is a pic of the query output.

Note that Null and blank values are handled differently in Access.
But I think that would just return unexpected results and not errors.
Can you post some data examples and expected results?

 

[Joe4]

Try this instead:

Past Due RFU: IIf(IsNull([Cur RFU]),IIf(IsNull([Pla RFU]),[Act RFU],[Pla RFU]),[Cur RFU])

 

[mardimus]

The null was the key:

Great success. Thanks for the help. It is much appreciated. Cheers.

Try this instead:

Past Due RFU: IIf(IsNull([Cur RFU]),IIf(IsNull([Pla RFU]),[Act RFU],[Pla RFU]),[Cur RFU])

 

[Joe4]

Glad to help!

I think "" works with String (Text) values, but not dates and numbers. That is where ISNULL comes in handy.

 

[mardimus]

Yes, and this is where I am learning. I even tried Null, but not IsNull. Thanks for the help again. Saved me a lot of time and I learned something very useful. Cheers.

Glad to help!

I think "" works with String (Text) values, but not dates and numbers. That is where ISNULL comes in handy.

 

[Joe4]

Another little tip while we are on the topic of Nulls. A real helpful function is NZ, which you can use to convert a Null to a value. If the field is not Null, it just returns the value. See: MS Access: Nz Function

 

[mardimus]

Thank you for the link, it is most helpful. I will play with it or use it on the cells where I do not have a value. There were a few in the DB. Thanks

Another little tip while we are on the topic of Nulls. A real helpful function is NZ, which you can use to convert a Null to a value. If the field is not Null, it just returns the value. See: MS Access: Nz Function