Remove formatting from blank cells

[KristieLee1]

Hello. How do I remove date formatting from cells that are blank?
I have date of birth, formatted as mm/dd/yyyy. If the dob is blank, then the / / marks are still showing up. Anyone know how I can remove the / / from those folks that have no dob? Thank you!!!

 

[traingamer]

Is your data stored as a date or is it text that you are reformatting for your query?

I suspect the latter. If so, can you provide the SQL for the column in question?

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[KristieLee1]

As text.

Here's the code:

SELECT Cremains_Table.Gender, Cremains_Table.BirthMonth, Cremains_Table.BirthDay, Cremains_Table.BirthYear, [BirthMonth] & "/" & [BirthDay] & "/" & [BirthYear] AS DOB
FROM Cremains_Table;


The the month, day, and year of the dob are in their own columns, so I've put them all in one with the / /. However, in places where there is no dob, I don't want the / / to show up.
Was thinking I could do if dob is null then no format...but don't know how to do it. Thank you.

 

[MajP]

This really should be in a single field as a date datatype, not stored in three separate fields. This will lead to a lot of problems and work arounds such as this.

IIf(IsNull([BirthYear]), "", [BirthMonth] & "/" & [Birthday] & "/" & [BirthYear]) AS DOB

 

[PHV]

[BirthMonth] [!]+[/!] "/" [!]+[/!] [BirthDay] [!]+[/!] "/" [!]+[/!] [BirthYear] AS DOB

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

PHV,
If those fields are numeric I think that does not work. Only if they are string fields can you use that trick. Basically it returns a type mismatch.