way to search for an item in a field and remove it

ewalker3

New member
Local time
Today, 06:07
Joined
Nov 16, 2010
Messages
9
I have a database with a zipcode field
some have the full zip+4 like 90036-1234
some only have 90036-
is there a way to create a routine to strip out that - when there arent numbers after it?


thanks
 
thanks for the link. I dont get how to use the replace with what i want to do
since the numbers are all different
I'd like to be able to do a wildcard for first 5 digits the 6 character would be - i would also need to determine if there are any characters after the - (four more numbers) and exclude those
and those without the 4 digits after the - strip out the -

thanks
 
yes:
I have a table with a list of zip codes that look like either of the following:

33401-1234
90036-2353
33407-
48910-
I would like to be able to remove the - on the entries like 33407 and 48910( that don't have four characters/numbers after the - like the first two above)

thanks.
 
If you simply want to remove a trailing "-" then you could use something like;
Code:
If [URL="http://www.techonthenet.com/access/functions/string/right.php"]Right[/URL](Me.YourTextField, 1) = "-" Then
     Replace(Me.YourTextField, "-", "")
End If
 
on the prior example in the if then...

Is there a way to have it check to see if the space after the - is blank?
If it is then I know that record doesn't have the additional 4 digits (that need to stay)
and the - can be removed?

thanks.
ed
 
John's code does that. His code essentially does this: If the last character of the string it is passed is "-", then it removes it.

Using his code this is what the following zip codes would turn into:

37806- becomes 37806
37804-1234 becomes 37804-1234
37123 becomes 37123

It only triggers if the last character of the string is "-"
 
I tried using the above code. I changed the YourTextField to the Field I want to search (called CZIP).
I get a compile error Expected =

?
thanks.
 
Hi -

From the debug (immediate) window, here are three examples, using the same one-liner. See if this would be of assistance.

Code:
x = "90036-1234"
? iif(len(rtrim(x))<=6, left(x,5), x)
90036-1234

x = "91234-"
? iif(len(rtrim(x))<=6, left(x,5), x)
91234

x = "92486"
? iif(len(rtrim(x))<=6, left(x,5), x)
92486

HTH - Bob
 

Users who are viewing this thread

Back
Top Bottom