help with the string in vba

[leddy]

Hi all,

I'm trying to amend a query on the fly, but it includes a function and i'm getting all confused with how I write it in vba so that I can change it (if that makes sense!)

Here is the sql of the query as is:

SELECT DISTINCT tempExport.ROUNDCODE, tblpafdata.Str, tblpafdata.PCD, tempExport.NDP, Concatenate("SELECT DISTINCT ORG & PRMNUM & iif(PRMNUM='' OR IsNull(PRMNUM),NUMRANGE,'') AS N FROM tblpafdata WHERE PCD = '" & [PCD] & "'") AS [Num Range]
FROM tempExport INNER JOIN tblpafdata ON tempExport.postcode = tblpafdata.PCD
WHERE (((tempExport.ROUNDCODE) Is Not Null));

As you can see it includes double and single quotes - I want to be able to change this in vba, but i'm trying to rewrite it so that the double quotes aren't seen as terminating the string etc & it's not working

Any help would be appreciated!

Thanks, leddy

 

[MarkK]

I would get the query working in the design grid first. Then copy that SQL to wherever you need it.

 

[ghudson]

Usually you can substitute the double quotes for single quotes to run the SQL in VBA. If not then you will have to build it using the Chr(34) code in the appropriate spots. Passing the SQL srting to the debugger helps you see what you are building in the immediate window.

Debug.Print SQL