Query to determine value on report

[enginerdUNH]

Hi,



I have a form which has three date fields for the user to enter the date they enrolled in one of three different classes. The report that i created based on this form has a field which will take whichever field has a date entered into it and display that on the report. The issue that i am having so far is that the expression I'm using is giving me an #Error message when i run the report. The formula I'm using is as follows Iif(IsNull(Date1),Iif(IsNull(Date2),Iif(IsNull(Dat e3),"",Date3),Date2),Date1). I am wondering if someone can help me figure out why this error message is appearing and how to fix it or how to turn structure a query that will do the same function as the expression I'm trying to use and then i will use the query in the report instead. Any suggestions are greatly appreciated.

[ranman256]

put the IIF in the query.
put everything you can in the query and let the report display the query field.
(instead of doing calculations in the report)

[enginerdUNH]

put the IIF in the query.
put everything you can in the query and let the report display the query field.
(instead of doing calculations in the report)

Thanks ranman, so i have created a query which uses the userID, and the three date fields. The last column has the same expression that I gave above and I still get the same #Error message. Trying to debug why this is occurring.

[Gicu]

Iif(IsNull(Date1),Iif(IsNull(Date2),Iif(IsNull(Dat e3),"",Date3),Date2),Date1) Hope it is not just a spelling error, you have a space in Dat e3 (in red). You should wrap the field names in square brackets.

Cheers,
Vlad

[enginerdUNH]

Iif(IsNull(Date1),Iif(IsNull(Date2),Iif(IsNull(Dat e3),"",Date3),Date2),Date1) Hope it is not just a spelling error, you have a space in Dat e3 (in red). You should wrap the field names in square brackets.

Cheers,
Vlad

Thanks Vlad for your response. I do have the field name in square brackets. I’m sorry I should have included those in my post when I posted the expression. Even so I’m still getting that error message

[Gicu]

you might have some bad data. Try to filter out the nulls in each of the three fields then sort them first ascending then descending and visually look for exceptions.
Vlad

[enginerdUNH]

you might have some bad data. Try to filter out the nulls in each of the three fields then sort them first ascending then descending and visually look for exceptions.
Vlad

Right now each of those fields for the record I am trying to run the query off of is blank meaning there is no data in them. They can’t be bad data if there is nothing in the field.

[Gicu]

I see, just replace the empty string ("") with Null in your expression, as I think you are trying to force a date field to accept a string.

Iif(IsNull(Date1),Iif(IsNull(Date2),Iif(IsNull(Dat e3),NULL,Date3),Date2),Date1) instead if Iif(IsNull(Date1),Iif(IsNull(Date2),Iif(IsNull(Dat e3),"",Date3),Date2),Date1)

Cheers,
Vlad

[enginerdUNH]

I see, just replace the empty string ("") with Null in your expression, as I think you are trying to force a date field to accept a string.

Iif(IsNull(Date1),Iif(IsNull(Date2),Iif(IsNull(Dat e3),NULL,Date3),Date2),Date1) instead if Iif(IsNull(Date1),Iif(IsNull(Date2),Iif(IsNull(Dat e3),"",Date3),Date2),Date1)

Cheers,
Vlad

Thanks Vlad!! This worked!

[Micron]

you have a space in Dat e3

This is what happens to your post when you don't use code tags; i.e. the forum inserts a space after 50 characters. Probably at the 100 point as well, but I've never pushed that far, and long ago stopped posting in excess of 50 characters on a code line without code tags.

[enginerdUNH]

This is what happens to your post when you don't use code tags; i.e. the forum inserts a space after 50 characters. Probably at the 100 point as well, but I've never pushed that far, and long ago stopped posting in excess of 50 characters on a code line without code tags.

Micron, as I mentioned before, I don’t have a space in the actual expression and the actual expression does have square brackets whereas I didn’t include them here for simplification. The error which was determined by vlad was as simple as replacing the “” part of the code with Null

[Micron]

I understand that
Merely trying to inform all about the behavior of the forum when code tags are not used.