Combine 2 functions for one field in Query Design View

[nggman]

Is there a way to combine 2 different functions for the same field (column) in Query Design View? (Access 2016) For example, I have this for my discharge date :
Discharge Date: DateValue([RAH_ATD_TO_DT]). I want to add a second function to this same statement. I want to add the Nz function so I do not get #Error if the field is blank. I can accomplish this in 2 separate fields (columns) but I want to do it in 1. Thank you.

[andy49]

This shouldn’t cause any problems. What have you attempted so far?

Nz(datevalue([rah_atd_to_dt],whatever you want your null value to be)

[June7]

Andy, DateValue will error if the field is Null or empty string or any string that can't be converted to a date.


What do you want to show if the field is Null?

Why do you need DateValue? Why isn't the value already a date?

DateValue(Nz([RAH_ATD_TO_DT], "1/1/1900"))

or

DateValue(Nz([RAH_ATD_TO_DT], Date()))

[nggman]

No, the field contains the time also. That is why I am using DateValue. I want the field to be blank if there is no date.

[June7]

Then:

IIf([RAH_ATD_TO_DT] Is Null, Null, DateValue([RAH_ATD_TO_DT]))

[nggman]

Worked great!! Thank you!!

[June7]

Keep in mind IIf() in SQL is different from IIf() in Access and VBA. The expression would still error in Access or VBA because all parts must be able to be evaluated, otherwise the entire expression will return error.

So in Access and VBA would have to:

IIf(IsNull([RAH_ATD_TO_DT]), Null, DateValue(Nz([RAH_ATD_TO_DT], Date())))