Adding a dash to a date

[sifuhall]

In my report I want to add a dash to a date field, but only when it has data so it will read like this:
- 05/16/2018


I have tried setting the control source to:


iif(isnull(EndDate]), "", " - " & [EndDate]

but that gives an error.

[RayMilhon]

You forgot to close the IIF statement

IIF(isNull(Enddate]),"","-" & [Enddate])

[CJ_London]

but that gives an error.

fairly simple answer this time but in the future, if you get an error, tell us what the error is - number and description. You'll get more focussed and faster help that way

[Minty]

If EndDate is a stored date field you won't be able to.
The dash wouldn't be an acceptable part of the date, or would turn it into a date somewhere near a black hole event horizon.

[CJ_London]

good point - you could use the format function

IIF(isNull(Enddate]),"",format([Enddate]," - dd/mm/yyyy"))

[sifuhall]

good point - you could use the format function

IIF(isNull(Enddate]),"",format([Enddate]," - dd/mm/yyyy"))

Thanks everyone for all the help.

Using this returns:
#Type!

[Minty]

If you are doing this in the report you'll need an = at the front.

= IIF(isNull(Enddate]),"",format([Enddate]," - dd/mm/yyyy"))

I'd probably create this in the query personally

DisplDate : IIF(isNull(Enddate]),"",format([Enddate]," - dd/mm/yyyy"))

[sifuhall]

Great idea!

Sometimes the best answers are the simplest and I'm trying to make this way more difficult than it needs to be.

Thank you!