Issue with removing special characters where original data length varies

[mkdrep]

I thought I had solved my initial problem of removing the apostrophe character for each zip code field. Most of the fields have data that reads '12345-1234'. I want to remove each (') character and the "-1234" so I end up with a zipcode of 12345.

I created 2 queries for this process. I first run a query with the following statement "Left([ZIP],Len([ZIP])-6)". That gets rid of the "-1234' ".
The I run the second query with this statement "Right([ZIP],Len([ZIP])-6)".

These 2 queries work perfectly if the original zipcode is " '12345-1234' ", but if it is " '12345' ", the entire zipcode is deleted. :banghead:

I have attached 3 (.jpg)'s to show you what I am talking about.

All help is appreciated!

 

[Isskint]

Use Instr() function to find the - then its just a mid() function job

Dim lngHyphen As Long
Dim strCleaned As String

lngHyphen = InStr([Zip], "-")
strCleaned = Mid([Zip], 2, lngHyphen - 2)

 

[namliam]

mid(yourzip,2, instr(1,Yourzip, "-")-2)

I think that should work !

 

[Isskint]

Much tidier namliam

 

[mkdrep]

mid(yourzip,2, instr(1,Yourzip, "-")-2)

I think that should work !

Still getting the same result. Please look at the (2) .jpg's I attached, the original and Step 2. As you can see when I enter your suggested command Mid([ZIP],2,InStr(1,[ZIP],"-")-2) in the Update Field of the query, the result of the query is perfect IF the original zipcode was '12345-1234', however, if the original zipcode was '12345', the entire zipcode is deleted.:banghead:

This issue, I think is the the original field length can vary between (12) characters and (7) characters. I want the end result to be the same where I get a "clean" zipcode ...i.e. 12345 How do I modify the command to get that result in all cases....

Thank you for your continued help.

 

[jdraw]

Try:

iif(Len(zip) = 12,Mid([ZIP],2,InStr(1,[ZIP],"-")-2),Mid(zip,2,5))

 

[mkdrep]

Try:

iif(Len(zip) = 12,Mid([ZIP],2,InStr(1,[ZIP],"-")-2),Mid(zip,2,5))

Thank you for the suggestion, however, when I run the query with this statement
iif(Len(zip) = 12,Mid([ZIP],2,InStr(1,[ZIP],"-")-2),Mid(zip,2,5))
the result is every ZIP field is update to "ip" for some reason, as per attached (.jpg)

 

[namliam]

iif(instr(1,Yourzip, "-") = 0
   , mid(Yourzip,2,len(yourzip)-2)
   , mid(yourzip,2, instr(1,Yourzip, "-")-2) )

That then?