How do you include quotation marks in a Concatenate result

[BH]

I have the following expression wrote in an Access query:

PCT: Concatenate("SELECT [1_RD_Lead_Involved] FROM tblTracker_RD_Lead WHERE [RefNo_Tracker_RD_Lead]='" & [RefNo_Tracker_RD_Lead] & "'")

The result of this can be from one name up to seven names e.g.

Ashfield PCT

Up to a combination of:

Ashfield PCT, Broxtowe and Hucknall PCT, Gedling PCT, Mansfield District PCT, Newark and Sherwood PCT, Nottingham City PCT, Rushcliffe PCT

What I require is the result to look like this:

"'Ashfield PCT'"

Up to a combination of:

"'Ashfield PCT', 'Broxtowe and Hucknall PCT', 'Gedling PCT', 'Mansfield District PCT', 'Newark and Sherwood PCT', 'Nottingham City PCT', 'Rushcliffe PCT'"

Can anyone assist me in the method of including 'Inverted Commas' and "speech marks" in the expression.

Any help would be appreciated as I have hit a brick wall!

BH

 

[PHV]

You may try something like this:
PCT: [!]Chr(34) & [/!]Concatenate("SELECT [!]Chr(39) & [/!][1_RD_Lead_Involved][!] & Chr(39)[/!] FROM tblTracker_RD_Lead WHERE [RefNo_Tracker_RD_Lead]='" & [RefNo_Tracker_RD_Lead] & "'")[!] & Chr(34)[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BH]

Thanks PHV

Worked a treat!

BH