Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Chantilly, VA USA
    Thanked 0 Times in 0 Posts

    Formatted Fields in Union Query (Access 2000)

    I have a database that tracks project documents. There are two tables, one for technical documents and one for non-technical documents. The fields in the two tables are identical. Both tables include an auto-number field for "Document ID Number."

    In one table, the Document ID Number is preceded by "C-123-". In the other, its preceded by "J-789-". In each case, this is accomplished by Formatting the field, i.e. "C-123-"000

    I need to create a report that shows the status of the documents from both tables. I've written a union query which does exactly what I want --except it's not picking up the input mask. Document C-123-012 is showing up as "12". Document "J-798-045" is showing up as "45."

    How can I get my union query to recognize/reflect the formatting in the field?


    Don Brownlee

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Arlington, Virginia, USA
    Thanked 3 Times in 3 Posts

    Re: Formatted Fields in Union Query (Access 2000)

    Recommend use Format function, which returns a string, to format Autonumber field. Example of simplified SQL:

    <pre>SELECT Format([ID],"""C-123-""000") AS Expr1
    FROM Table1
    UNION SELECT Format([ID],"""J-789-""000") AS Expr1
    FROM Table2;</pre>

    In this example the common Autonumber field is called "ID". Note when entering Format expression I used backslashes for the literals; the query designer substituted the quotes in expression as shown.


Posting Permissions

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