Results 1 to 2 of 2
2002-12-23, 18:15 #1
- 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?
2002-12-23, 18:55 #2
- 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
UNION SELECT Format([ID],"""J-789-""000") AS Expr1
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.