Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    45

    Unanswered: FORMAT query field in SQL view

    Hi everyone!

    I'm have wrote a simple union query - something like:

    SELECT [Field1], [Field2], ...
    FROM [Query 1]

    UNION SELECT [Field1], [Field2], ...
    FROM [Query 2]

    ...and my union query is working fine, BUT I WANT [Field2] TO HAVE A CERTAIN FORMAT (for example - a phone number (956) - 223 - 562)
    I suppose this is very simple, but I'm not an expert and in MS Access Help there is nothing that can help me.

    So PLEASE HELP ME!
    Thank you!

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I will use the standard phone number formatting:

    SELECT format (PN, "(000) 000-0000") FROM tblBackEndPhoneNumber
    UNION SELECT format (PN, "(000) 000-0000") FROM A;

    Where PN is the field holding the 10 digit phone number.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Jun 2003
    Posts
    45
    SELECT format (PN, "(000) 000-0000") FROM tblBackEndPhoneNumber
    UNION SELECT format (PN, "(000) 000-0000") FROM A;

    Where PN is the field holding the 10 digit phone number.

    In my case it is:
    SELECT format (N, "# - 00") FROM tblNumber
    UNION SELECT format (N, "# - 00") FROM tblNumber;

    and N is just a number. After I wrote this in my union query I receive this output:

    23 - 03
    123 - 89
    2 - 43
    1321243 - 89

    The numbers are in the desireble format but the alignment is left, not right.
    I made a search in the forum and I found that FORMAT function really threats everything as TEXT (at least in union queries). I found solutions when the field should contain date or currency but I still don't know HOW TO MAKE BOTH THE FORMAT AND RIGHT ALIGNMENT?

  4. #4
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    You would be better off doing this on the form thagt presents the data.

    If you like the "grid" feel of a raw query you can set the form to datasheet view and embed this onto another form thus you have the Raw grid look and the control of forms. Each cell can then be formatted as much as you want.

    If you want the data to have been imputted correctly then you would have required much the same thing for an input mask set when makeing the table.

    That is not to say that your way is wrong it may be perfectly valid for whatever you are trying to do.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

Posting Permissions

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