Formatting Concatenated Fields

[hobbit2612 via AccessMonster.com]

Hi I wonder whether someone may be able to help please.

I'm using Duane Hookom Concatenate coding which is superb but I would like to
go a little further with this in terms of how the fields are formatted.

The issue I have is around formatting a reference number allocated to each
job which appears in my query.

The code I currently have is
CombinedNumber: Concatenate("SELECT JobNumber FROM tblJobs WHERE JobID = "&
[tblCustomers]![JobID])

What I would like to do is to have the Job Number appear as 3 digits e.g. 001,
002 etc. The data held in the table does have the correct format, but when I
use the Concatenate function the formatting disappears. I've tried allsorts,
adding '& FORMAT([JobNUmber]),"000"), changing the Input Mask of the query
field, but without success.

Could someone help me please?

Many thanks and regards

Chris

 

[John Spencer]

CombinedNumber: Concatenate("SELECT FORMAT(JobNumber,""000"") FROM tblJobs
WHERE JobID = " & [tblCustomers]![JobID])

Within a string you need to use TWO quotes in a row to end up with a single
quote when the string is interpreted.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[hobbit2612 via AccessMonster.com]

John, that's fantastic!

Thank you so much for your help and guidance.

Regards

Chris

CombinedNumber: Concatenate("SELECT FORMAT(JobNumber,""000"") FROM tblJobs
WHERE JobID = " & [tblCustomers]![JobID])

Within a string you need to use TWO quotes in a row to end up with a single
quote when the string is interpreted.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi I wonder whether someone may be able to help please.

[quoted text clipped - 19 lines]

Chris