Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bloomington, Indiana, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Length of field in Query (Access 97 SR2)

    In a query, I'm wanting to test the length of a field containing either a 7 or 11 digit phone number and format them based on the length of the field. Here's the syntax I'm using:

    Number Dialed: IIF(Len(Trim([Out_NumberDialed]))=7, Format([Out_NumberDialed],"###-####"), Format([Out_NumberDialed],"(###) ###-####"))

    which isn't working... Any suggestions will be much appreciated.

    BTW, the data is being pushed from a SQL database into Access, so I can't change any field properties in the table.

    Many thanks!
    Kathy

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Length of field in Query (Access 97 SR2)

    In what way isn't it working? And is [Out_NumberDialed] a text field or a numeric field?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bloomington, Indiana, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Length of field in Query (Access 97 SR2)

    When the query runs, that field is empty; I do not receive an error message. It is a text field. Also, the query pulls info from two related tables. I tried a few ways of putting the table name in along with the field name, but no luck.

    Thanks!
    Kathy

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Length of field in Query (Access 97 SR2)

    >>It is a text field<<

    This is part (if not all) of your problem. the # symbol only applies to numeric fields. Use the @ symbol instead, as in "@@@-@@@@".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bloomington, Indiana, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Length of field in Query (Access 97 SR2)

    That did it!

    Thanks so much for your help,
    Kathy

Posting Permissions

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