Need to display leading zeros in concatenation

M

Marty

In my table I have a job code field that is formated 0000. I need to
concatenate that filed with the job title field in a query with a dash
between. Example:
Job Code: 0006
Job Title: Slave
Result: 0006-Slave

I've done the concatenation in an expression field in my query but it drops
the leading zeros. Here's what I've used:

Expr2: [Tbl_NCBHNewHire60-DayTBComplianceStatus]![Job Code] & "-" &
[Tbl_NCBHNewHire60-DayTBComplianceStatus]![Job Title]

I don't have the option of changing the job code field to text because the
data comes from a query run to Excel from in another program and when I
change the format of the column to text I lose the zeros.

Thanks for your help.
 
W

Wayne-I-M

Format([Tbl_NCBHNewHire60-DayTBComplianceStatus]![Job Code], "0000") & "-" &
[Tbl_NCBHNewHire60-DayTBComplianceStatus]![Job Title]
 
M

Marty

Thanks so much! Works like a charm!

Wayne-I-M said:
Format([Tbl_NCBHNewHire60-DayTBComplianceStatus]![Job Code], "0000") & "-" &
[Tbl_NCBHNewHire60-DayTBComplianceStatus]![Job Title]


--
Wayne
Manchester, England.



Marty said:
In my table I have a job code field that is formated 0000. I need to
concatenate that filed with the job title field in a query with a dash
between. Example:
Job Code: 0006
Job Title: Slave
Result: 0006-Slave

I've done the concatenation in an expression field in my query but it drops
the leading zeros. Here's what I've used:

Expr2: [Tbl_NCBHNewHire60-DayTBComplianceStatus]![Job Code] & "-" &
[Tbl_NCBHNewHire60-DayTBComplianceStatus]![Job Title]

I don't have the option of changing the job code field to text because the
data comes from a query run to Excel from in another program and when I
change the format of the column to text I lose the zeros.

Thanks for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top