combine columns

[Mike Hughes]

I'm trying to combine 4 columns. Some of the columns have leading 0's and when I run my query I loose the leading 0's.

BATCH SOURCE NO SEQ LINE
11012011 P 0048 005 25

SELECT [BATCH] & "" & [SOURCE] & "" & [NO] & "" & [SEQ] AS RECEIPT, [7ef].LINE
FROM 7ef;

Could someone tell me what I'm doing wrong?
screen shot attached

thanks Mike

 

[plog]

It appears NO and SEQ are numeric values in your table. Most likely you have an input mask making them appear with those leading 0's. To get them formatted with the leading 0's in your query, you will need to use the Format function (http://www.techonthenet.com/access/functions/numeric/format.php).

 

[Mike Hughes]

I'm not sure how to do what you're suggesting.

 

[plog]

Put this as another field in that query:

TestFormat: Format([NO],"000000") & Format([SEQ],"000000")

Run the query and see what you get, then use it to produce the string of data you want to.

 

[Mike Hughes]

where does the format line go?

SELECT

[BATCH], [SOURCE], [NO], [SEQ], [LINE]

FROM 7ef

Format([NO],"0000") & Format([SEQ],"000")

 

[plog]

SELECT
Format([NO],"0000") & Format([SEQ],"000") AS TestField, [BATCH], [SOURCE], [NO], [SEQ], [LINE]

FROM 7ef

 

[Mike Hughes]

That did it. Thanks for your time. Mike