Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Location
    Boston, MA
    Posts
    8

    Unanswered: bizarre query/module action

    Okay, guys, I have a BIZARRE problem. I have a query that creates a table. It uses a few functions that I've written in a module. I was messing around with the functions, tweaking them a bit, and suddenly whenever I run the query, it kicks out to the module so I can read the code.

    If I change it to a select query, it doesn't happen. Change it back to create a table, it happens again.

    Any ideas? Access 97.

    Thanks!

  2. #2
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    That's because there is an error in your code, post it so we can help

    JefB

  3. #3
    Join Date
    Sep 2002
    Location
    Boston, MA
    Posts
    8
    That's because there is an error in your code, post it so we can help
    Okay, but it's not nice. I don't know much VBA except what I need to know when I need to know it. Here ya go...

    Code:
    Function PhoneConvert(PhoneNumber As String)
    
        Dim x, y, z As Integer
        Dim Ar, Pr, Su As String
        Dim TempString As String
        Dim us As String
            
        For x = 1 To Len(PhoneNumber)
           us = (Mid(PhoneNumber, x, 1))
           If IsNumeric(us) Then
              TempString = TempString & us
            End If
        Next x
           
        PhoneConvert = TempString
        
        If Len(TempString) = 10 Then
           Ar = Left(TempString, 3)
           Pr = Mid(TempString, 4, 3)
           Su = Right(TempString, 4)
           ElseIf Len(TempString) = 11 And Left(TempString, 1) = "1" Then
             Ar = Mid(TempString, 2, 3)
             Pr = Mid(TempString, 5, 3)
             Su = Right(TempString, 4)
             ElseIf Len(TempString) = 7 Then
               Ar = "617"
               Pr = Left(TempString, 3)
               Su = Right(TempString, 4)
               Else
                GoTo EndNow
        End If
        
        PhoneConvert = Ar & "-" & Pr & "-" & Su
                   
    EndNow:
    End Function
    It takes a free form field and, hopefully, converts it to a phone number that is xxx-xxx-xxxx with 617 being the default area code.

    Please be gentle.

  4. #4
    Join Date
    Feb 2002
    Location
    San Antonio, TX
    Posts
    69
    Why not try using the format function?

    ex:

    strPhoneNumber = "6171234567"

    strFormatted = Format(strPhoneNumber, "###-###-####")



    HTH
    Last edited by punkerboy; 09-17-02 at 19:43.
    "Doing stuff is overrated. Take Hitler for example. He did lots but don't we all wish he'd have just stayed home and gotten stoned?"

  5. #5
    Join Date
    Sep 2002
    Location
    Boston, MA
    Posts
    8
    Why not try using the format function?
    Thanks for the idea, but some of the numbers have a leading 1, some don't have area codes... Any other ideas? Thanks!

  6. #6
    Join Date
    Feb 2002
    Location
    San Antonio, TX
    Posts
    69
    The format function can still work for you in the case of no area code or a leading 1....you'll just have to set up testing for it.

    If Len(strPhoneNumber) = 11 Then
    strFormatted = Format(strPhoneNumber, "#-###-###-####")
    ElseIf Len(strPhoneNumber) = 10 Then
    strFormatted = Format(strPhoneNumber, "###-###-####")
    ElseIf Len(strPhoneNumber) = 7 Then
    strFormatted = "617-" & Format(strPhoneNumber, "###-####")
    End If


    That should cover your requirements.
    Last edited by punkerboy; 09-18-02 at 15:59.
    "Doing stuff is overrated. Take Hitler for example. He did lots but don't we all wish he'd have just stayed home and gotten stoned?"

Posting Permissions

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