Formatting a date field and changing it's value if null

[RacerGirl117]

Hi!

I have a form (frmSvcWarrInfoEquipSub) based on a query (qrySvcWarrInfoEquipSub). For a little history on what's going on with this here you go...

The data being pulled into table on which the query is based comes from an external (non-Access) data source via Pervasive. The software the information is coming from in Real World accounting software. When the dates come in they appear like this - 20030410.

I want to reformat the date to look like this - 4/10/30. I have the below formatting statement in one of the fields where there is a date and it works just fine - as long as there is a date in the field.
LastRepDate: CDate(Left([SV_CEQ_LAST_REPAIR_DATE],4) & "/" & Mid([SV_CEQ_LAST_REPAIR_DATE],5,2) & "/" & Right([SV_CEQ_LAST_REPAIR_DATE],2))

If there is no date (i.e. the field is null) I want the field to display "N/A". So I was going to put the following IIf statement at the end of the formatting statement
IIf(IsNull([SV_CEQ_LAST_REPAIR_DATE]),"N/A",Null)

so that in the end the complete statement looks like
LastRepDate: CDate(Left([SV_CEQ_LAST_REPAIR_DATE],4) & "/" & Mid([SV_CEQ_LAST_REPAIR_DATE],5,2) & "/" & Right([SV_CEQ_LAST_REPAIR_DATE],2)IIf(IsNull([SV_CEQ_LAST_REPAIR_DATE]),"N/A",Null))

but it keeps telling me I have invalid sytax. If I put a "," between the ) and the IIf, or a ( between those or anothe ) after the 2 and a ( before the IIf it gives me the invalid syntax message and tells me that I need to enclose the text data in quotes.

Is this combination of statements even possible? If so, what am I doing wrong here?

Thanks in advance, Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[RacerGirl117]

If I put
LastRepDate: CDate(Left([SV_CEQ_LAST_REPAIR_DATE],4) & "/" & Mid([SV_CEQ_LAST_REPAIR_DATE],5,2) & "/" & Right([SV_CEQ_LAST_REPAIR_DATE],2) Or IIf([SV_CEQ_LAST_REPAIR_DATE]=0,"N/A",[SV_CEQ_LAST_REPAIR_DATE]))

it works, but it returns 12/29/1899 in all the records. ???

If I do the two statements as separate fields, one LastRepDate and one RepDate they work fine. But I really want to avoid having to create another query just so I can get the two fields into one.

Your help would be greatly appreciated. Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[mikevh]

Your dates are coming out weird because the way you're
concat'ing the month, day, and year, you're ending up
with something like "2003/04/10" instead of "04/10/2003".
Also, it looks like you want this to be a date/time data
type per your use of CDate, but you want the blank dates
to come out as "N/A", which clearly isn't a date.
Try it as below, and let me know how it goes.

LastRepDate: iif(NZ([SV_CEQ_LAST_REPAIR_DATE],"&quot="","N/A",
Mid([SV_CEQ_LAST_REPAIR_DATE],5,2) & "/" & Right([SV_CEQ_LAST_REPAIR_DATE],2) & "/" & Left([SV_CEQ_LAST_REPAIR_DATE],4))