Runtime error '3075' in CreateQueryDef

[groupy]

Hi,

I have a problem. I made a sql query in the query part of MS Access. The query works fine so now I want to create the query in VBA with the command CreateQueryDef.

But the query gave the 3075 error.

exportDefinition = "SELECT tblClIENTClients.clientCode & ' ' & '10C' & tblPUBLinkClientPub.copies AS Code, tblClIENTClients.companyName, title & ' ' & IIf(IsNull(initials)=True Or initials="",firstName,initials) & ' ' & surname AS Name, tblClIENTClients.address1, tblClIENTClients.address2, tblClIENTClients.address3, tblClIENTClients.address4, Nz(postalcode & country) AS addressEnd] "
exportDefinition = exportDefinition & "FROM (tblClIENTClients INNER JOIN tblPUBLinkPubSAddressS ON tblClIENTClients.clientCode = tblPUBLinkPubSAddressS.clientCode) INNER JOIN tblPUBLinkClientPub ON tblPUBLinkPubSAddressS.pubClientCode = tblPUBLinkClientPub.pubClientCode "
exportDefinition = exportDefinition & "Where tblPUBLinkClientPub.linkPubMediaCode = 12 And tblPUBLinkClientPub.copies = 1"

'Create query
'On Error GoTo err_msg
db.CreateQueryDef strQueryName, mySQL

I tried some thinks to solve the problem:
- I split SQL statement in three parts.
- Changed the " in '.

Please can someone help me.

 

[groupy]

Hi,

After lots of trying I found the solution. You can change the ' in "".

Example:
SELECT tblClIENTClients.clientCode & "" "" & ""10C"" & tblPUBLinkClientPub.copies AS Code, etc.

I hope this can help also other people.