Query returns blank value to use different column

[cstickman]

Hello Everybody,

I need some assistance with a query for a report. I have googled a few things, but not sure if I am asking for the right thing. I need a report query to check a column for a date. If the date is blank then I need it to take the value from another column. So the one column is entered in when the task is completed, but if it is not completed then it needs to take the projected date which is in another column. The column name that it needs to check first is final report sent, if no date is in that column then it needs to check final report due date. I need it to display just two columns which I can figure out. It will also be ran from a date range within a form, but I also think I have that covered as well. It is just this goofy query that I need help with. Thanks!!

 

[James Deckert]

in an empty column in the query do something like
ColName: IIF(isnull([DateField]), [FieldWhenDateIsBlank], [DateField])
I'm having a hard time understanding you explanation, but hopefully this will get you on the right path.

 

[cstickman]

Hi James!!

Thanks for the quick reply, but the code you have listed, where do I put it?

 

[obeylele]

as he said copy paste below code in empty column as in attached pic

ColName: IIF(isnull([DateField]), [FieldWhenDateIsBlank], [DateField])

 

[cstickman]

Thanks!! Not even sure how I missed that

 

[Galaxiom]

Alternatively:

Nz([datefield],[Otherfield])

or

IIf([datefield] Is Null, [Otherfield], [datefield])

 

[cstickman]

Hey Guys,
Ok I did the following code in a column with other fields selected to view as well. So here is what I have
ID (checked to show)
TA (checked to show)
Final Report Due Date (checked to show)
Final Report Sent (checked to show)
ColName: IIF(IsNull([Final Report Sent]),[Final Report Due Date],[Final Report Sent]) (checked to show) And this in the criteria >=[Forms]![adminreport]![txtstartdate] AND <=[Forms]![adminreport]![txtenddate]

On my form the user can select a date range. When I click on the run report which has vba coding DoCmd.OpenReport "completedreport", AcViewPreview

It wants me to enter in a ID Parameter Value. I just put in 1 and it worked, but why is it asking for a parameter Value on the first column?