Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Remove formatting from blank cells

Status
Not open for further replies.

KristieLee1

Technical User
Jul 13, 2009
76
US
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!!!
 
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
 
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.

 
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
 
[BirthMonth] [!]+[/!] "/" [!]+[/!] [BirthDay] [!]+[/!] "/" [!]+[/!] [BirthYear] AS DOB

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top