Select by alternative date if one is null

[CW]

I have a query that selects shipments by their arrival dates (field is
"ArrvlDate") and then displays a number of fields.
Where the arrival date has not been entered for one reason or another, I
need it to use the departure date instead (DepDate").
I have tried various permutations using IsNull in the SQL but no good so
far... your suggestions, please!
Thanks
CW

 

[Stefan Hoffmann]

hi,

I have a query that selects shipments by their arrival dates (field is
"ArrvlDate") and then displays a number of fields.
Where the arrival date has not been entered for one reason or another, I
need it to use the departure date instead (DepDate").
I have tried various permutations using IsNull in the SQL but no good so
far... your suggestions, please!

Use

Nz([ArrvlDate], [DepDate])

in your query.


mfG
--> stefan <--

 

[CW]

Thanks Stefan that's nice and easy
CW

hi,

I have a query that selects shipments by their arrival dates (field is
"ArrvlDate") and then displays a number of fields.
Where the arrival date has not been entered for one reason or another, I
need it to use the departure date instead (DepDate").
I have tried various permutations using IsNull in the SQL but no good so
far... your suggestions, please!

Use

Nz([ArrvlDate], [DepDate])

in your query.


mfG
--> stefan <--

 

[John Spencer]

And if you want to use IIF then the expression would be

IIF(ArrvlDate is Null,DepDate,ArrvlDate)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks Stefan that's nice and easy
CW

hi,

I have a query that selects shipments by their arrival dates (field is
"ArrvlDate") and then displays a number of fields.
Where the arrival date has not been entered for one reason or another, I
need it to use the departure date instead (DepDate").
I have tried various permutations using IsNull in the SQL but no good so
far... your suggestions, please!

Use

Nz([ArrvlDate], [DepDate])

in your query.


mfG
--> stefan <--