Formatting date fields

[BobC]

I could not seem to find the format syntax.
I want to format a date field to display '------' when there is no date
to print in a record.
Can this be done?

 

[Evi]

If no-one has a better suggestion then you can create a second field in your
query with iif(IsNull(MyDate),Format(MyDate,"dd/mm/yy"),"----"). Show this
field for display purposes and keep the real field for sorting and
calculations.
Evi

 

[Douglas J. Steele]

Actually, the Format property allows for up to 4 different formats to be
specified (one for positive values, one for negative values, one for values
of zero and one for values of Null). Since "no date" must correspond to a
Null value, try the following Format:

yyyy\-mm\-dd;;;\-\-\-\-

(yes, there are three semi-colons in a row there)

 

[BobC]

A neat approach!
Thanks!
Bob

If no-one has a better suggestion then you can create a second field in your
query with iif(IsNull(MyDate),Format(MyDate,"dd/mm/yy"),"----"). Show this
field for display purposes and keep the real field for sorting and
calculations.
Evi

 

[Evi]

Use Douglas's suggestion, it will be better. It won't change your date field
into text.
Evi

 

[Evi]

Doug, what do the \ between the dashes do?
Evi

Actually, the Format property allows for up to 4 different formats to be
specified (one for positive values, one for negative values, one for values
of zero and one for values of Null). Since "no date" must correspond to a
Null value, try the following Format:

yyyy\-mm\-dd;;;\-\-\-\-

(yes, there are three semi-colons in a row there)

 

[Douglas J. Steele]

\ is an escape character. It means that whatever follows is printed
literally.

In the case of yyyy\-mm\-dd, if you were to put yyyy-mm-dd, you'd actually
get whatever was defined as the date separator character in Regional
Settings in place of the -. For the last bit, it simply means you get ----.

 

[Evi]

Thanks Doug. That's great.
Evi

\ is an escape character. It means that whatever follows is printed
literally.

In the case of yyyy\-mm\-dd, if you were to put yyyy-mm-dd, you'd actually
get whatever was defined as the date separator character in Regional
Settings in place of the -. For the last bit, it simply means you get ----.