More help for Trim of Mailing Address

M

Maria

Here is the syntax. Which is working wonderfully (thanks to Ofer & John)
....unless the field is empty. If field is empty the query runs but puts
#Error in each field that is empty.

MAILING_ADDRESS_3: IIf(InStr([Mailing_Address3],"
")=0,[Mailing_Address3],Trim(Left([Mailing_Address3],InStr(1,[Mailing_Address3],"
"))) & " " & Trim(Mid([mailing_address3],InStr(1,[Mailing_Address3]," ")+1)))

Can someone help me fix this? Thanks.

Maria
 
J

John Spencer

IIF(Mailing_Address3 is Null, Null,
IIF(InStr([Mailing_Address3]," ")=0,[Mailing_Address3],
Trim(Left([Mailing_Address3],InStr(1,[Mailing_Address3]," ")))
& " " & Trim(Mid([mailing_address3],InStr(1,[Mailing_Address3]," ")+1))))

Just Add
IIF( Mailing_Address3 is Null, Null,
to the beginning of your statement - just before the IIF and then add an
additional closing parentheses at the very end of the statement.
 
M

Maria

Thanks Jeff. I received an error adding exactly below to the syntax. But
that statement allowed me to "read" the line to figure out what it is doing
so I was able to make it work. I, unfortunately, have a mental block
whenever I see InStr. :)

Thanks so much for the help and teaching!

Maria

Jeff L said:
Try this:

iif(IsNull(Mailing_Address3), "", The statement you already have)


Here is the syntax. Which is working wonderfully (thanks to Ofer & John)
...unless the field is empty. If field is empty the query runs but puts
#Error in each field that is empty.

MAILING_ADDRESS_3: IIf(InStr([Mailing_Address3],"")=0,[Mailing_Address3],Trim(Left([Mailing_Address3],InStr(1,[Mailing_Address3],"
"))) & " " & Trim(Mid([mailing_address3],InStr(1,[Mailing_Address3]," ")+1)))

Can someone help me fix this? Thanks.

Maria
Click to expand...
Click to expand...
 
Top