Format data using SQL in access

[jamie81]

I created a union query between two tables in MS Access 2000. I have an autonumber field in the original table that is formatted with leading zeros. These zeros do not show up in my sql query results. How can I format to display zeros using a sql query?

 

[jebry]

Hi!

In the field definition use NewFieldName: Format([AutonumberField], "0000000000&quot.
Of course, you can use as many zeros as you want.

hth
Jeff Bridgham

 

[Guest_imported]

What do you mean by field definition? This is my current queryThe PermID field is the autonumber field)

SELECT [Brochure ID],[Brochure Name],[Brochures Detail_PermID] AS [PermID],[Order Date],[Ship Date],[Qty],[Salutation],[LastName],[FirstName],[MiddleName],[Address1],[Address2],[City1],[State1],[Zip1],[Add1Country],[HomePhone1],,[PrimaryAddress]
FROM [FirstAddressBrochuresPendingQuery]


UNION SELECT [Brochure ID],[Brochure Name],[Brochures Detail_PermID] AS [PermID],[Order Date],[Ship Date],[Qty],[Salutation],[LastName],[FirstName],[MiddleName],[AltAddress],[AltAddress2],[City2],[State2],[Zip2],[AltAddCountry],[HomePhone2],[Email],[PrimaryAddress]
FROM [SecondAddressBrochuresPendingQuery];

 

[jebry]

Okay,

If I understand what you have here correctly, replace:

[Brochures Detail_PermID] AS [PermID]

with

Format([Brochures Detail_PermID], "0000000000&quot AS [PermID]

Again, using however many zeros you want.

hth
Jeff Bridgham

 

[jamie81]

It worked perfectly! Thank you very much.