RacerGirl117
Technical User
Hi! I posted this in the Microsoft Access: Queries forum, but thought I’d post it here as well.
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 the table on which the query is based comes from an external (non-Access) data source via Pervasive. The software the information is coming from is 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?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
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 the table on which the query is based comes from an external (non-Access) data source via Pervasive. The software the information is coming from is 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?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
I was thinking that the formatting should be done first, but I guess it makes more sense this way. You want to assign any values before you try and format it. Makes sense.