Adding Lead Zero's in a Query 2

[scottparks]

I need to fill in with lead zero's if the number is not 6 digits long. I am having trouble with doing this. SERIAL is the query field I want to have 6 digits. Below is the code to generate the correct numbers, just no leading zeros if less than 6 digits.

SELECT tblProductTicket.ProductCode, [N]+[BSerial] AS SERIAL
FROM tblIntegers4, tblProductTicket
WHERE ((([N]+[BSerial])<=[ESerial]))
ORDER BY [N]+[BSerial];

Any and all help would be appreciated.

Thank you.

 

[Golom]

From the fact that you are adding the fields [N] and [BSerial], I infer that the [BSerial] field is numeric.

Numeric fields do not support leading zeros so you will need to convert it to text

SELECT tblProductTicket.ProductCode
     , Format([N]+[BSerial], "000000") AS SERIAL 
FROM tblIntegers4, tblProductTicket
WHERE [N]+[BSerial] <= [ESerial]
ORDER BY [N]+[BSerial];

 

[scottparks]

Thank you for the help. I will give that a try.

 

[barny2006]

or you can try this:

SELECT tblProductTicket.ProductCode
, right("000000" & BSerial,6) AS SERIAL
FROM tblIntegers4, tblProductTicket
WHERE [N]+[BSerial] <= [ESerial]
ORDER BY [N]+[BSerial];

 

[scottparks]

Thank you for the reply. The first solution did work, but I will give this one a try also. Thanks for taking time to reply