Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2003
    Posts
    3

    Unanswered: Removing dashesfrom the middle of a string (Zip Code Field)

    I have a Zip Code field that is formatted as Zip+4 (ie #####-####). I am trying to make a query/update query that will remove the dash.

    I've tried an update query with:

    Field: [Postal_Code] Like "#####-####" / Criteria: True / Update To: Format([Postal_Code],Left([Postal_Code],5) & Right([Postal_Code],4))

    And Also have tried a Select query:
    Expr1: IIf([Postal_CodeLike "#####-####",Format([Postal_Code], "@@@@@@@@@"),[Postal_Code]))



    Each time it warns me it going to update around 12000 records, which is correct, but when I in to look at the table (after update query) or run the select query, the dashes are still there.


    Can anyone suggest a working alternative to get this working? Thanks!!

  2. #2
    Join Date
    Nov 2002
    Location
    Sacramento, CA
    Posts
    34
    try this
    Function GetNumbers(ByVal strString As String) As String

    Dim strStr As String
    Dim nI As Integer

    GetNumbers = ""
    For nI = 1 To 5 'Len(strString)
    strStr = Mid(strString, nI, 1)
    If strStr >= "0" And strStr <= "9" Then
    GetNumbers = GetNumbers & strStr
    End If
    Next

    End Function
    Make the money, don't let the money make you.

  3. #3
    Join Date
    Jul 2003
    Posts
    3
    Originally posted by dragracer
    try this
    Function GetNumbers(ByVal strString As String) As String

    Dim strStr As String
    Dim nI As Integer

    GetNumbers = ""
    For nI = 1 To 5 'Len(strString)
    strStr = Mid(strString, nI, 1)
    If strStr >= "0" And strStr <= "9" Then
    GetNumbers = GetNumbers & strStr
    End If
    Next

    End Function
    Can you tell me how I would "call" that in a query?

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    prehaps something along the lines of:
    Code:
    public function KillAllHyphens (incoming as string) as string
      dim finger as integer 'a pointer
      finger = 1 'start at the beginning
      do while finger > 0 'continue until no more hyphens
        finger = instr(finger, incoming, "-") 'find the next hyphen or 0
        if finger > 0 then 'if hyphen found
          incoming = left$(incoming, finger -1) & mid$(incoming, finger+1) 'kill it
        endif
    loop 'until no more hyphens
    sorry - no A on this machine, so code is not verified!

    izy

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    again no A so i can't check if it works in a query.

    the general route is to declare the function public in a global module.

    click in the top box of any empty query column and then the magic-wand thingie (navigating: functions ¦ yourDBname ¦ list of your functions)

    izy

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i don't know about your zip code format, but if you've ALWAYS got the risk of a hyphen at position six, you could squeeze it into an immediate if
    Code:
    noHyphens = iif(mid$([field], 6, 1) = "-", left$([field], 5) & mid$([field], 7), [field])
    that goes in a query nicely!

    izy

  7. #7
    Join Date
    Nov 2002
    Location
    Sacramento, CA
    Posts
    34
    The SQL statement would look something like this.

    UPDATE testing SET testing.[zip+4] = getnumbers([zip+4]);

    its an update query. the [zip+4] will need to be replaced with your field name and 'testing' will need to be replaced with your table name

    also I had to change the getnumbers code to see all 10 characters:
    changed: For nI = 1 To 5
    to: For nI = 1 To 10
    Make the money, don't let the money make you.

  8. #8
    Join Date
    Jul 2003
    Posts
    3
    Thanks, that last one worked. I really appreciate it!!

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and if you are CERTAIN you will have a hyphen at position six, it's even easier:

    update to
    left$([field], 5) & mid$([field], 7)

    izy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •