Basic Compile error...... (1 Viewer)

[liuec]

I'm trying to compile the below code, however something seems wrong and I can't tell what... can some one tell me whats wrong?
The error I'm receiving is "end of statement error".
Update: sqlqry points to a sql code here, which is copied from an Access query and the code itself runs fine in Access so no need to exam the integrity of the code. The compile error here has something to do with quotations, parenthesis or other symbol (i'm pretty sure)

Thanks so much!

code here:

Dim sqlstr = string

sqlqry = "INSERT INTO [TBL_Product-SD] ( Product_SDAmt, Loc_Ff, Gender, Ft, age, CT_Code )" _
& "SELECT [Tbl Amt-SD].Amt, [Tbl LocFf-SD]![Loc] & " (" & [Tbl LocFf-SD]![Ff] & ") " _
& "" AS Loc_Ff, [Tbl Amt-SD].GDR, [Tbl Amt-SD].FA, [Tbl Amt-SD].AG, [Tbl Amt-SD].[CT Code] " _
& "FROM [Tbl LocFf-SD] INNER JOIN [Tbl Amt-SD] ON [Tbl LocFf-SD].FfID = [Tbl Amt-SD].FfID " _
& "WHERE ((([Tbl Amt-SD].Amt) > 0) And (([Tbl Amt-SD].GDR) = IIf([Forms]![SelectOptionsForm-SD_base].[OptMale] = " _
& "True, "M", "") Or ([Tbl Amt-SD].GDR) = IIf([Forms]![SelectOptionsForm-SD_base].[OptFemale] = True, "F", "")) And " _
& "(([Tbl Amt-SD].FA) = IIf([Forms]![SelectOptionsForm-SD_base].[Opt100] = True, 10, 0) Or ([Tbl Amt-SD].FA) = " _
& "IIf([Forms]![SelectOptionsForm-SD_base].[Opt25] = True, 5, 0) Or ([Tbl Amt-SD].FA) = " _
& "IIf([Forms]![SelectOptionsForm-SD_base].[Opt50] = True, 0, 0) Or ([Tbl Amt-SD].FA) = " _
& "IIf([Forms]![SelectOptionsForm-SD_base].[Opt100] = True, 10, 0)) And (([Tbl Amt-SD].AG) = " _
& "IIf([Forms]![SelectOptionsForm-SD_base].[Opt75] = True, "75", "")) And (([Tbl Amt-SD].[CT Code]) = "B1") And " _
& "(([Tbl LocFf-SD].Selected) = Yes))" _
& "ORDER BY [Tbl Amt-SD].Amt;"

 

[pbaldy]

You have a number of instances like this:

& "True, "M", "")...

where you use double quotes within a string. If you think about it, the quotes at the beginning of the M close off the quotes at the beginning, leaving everything else in limbo. Those need to be single quotes.

 

[liuec]

You have a number of instances like this:

& "True, "M", "")...

where you use double quotes within a string. If you think about it, the quotes at the beginning of the M close off the quotes at the beginning, leaving everything else in limbo. Those need to be single quotes.

Thanks, single quotes as in

'

?
After I change these to single quotes then it is no longer a valid sql code in access..... are there ways to work around it?

I appreciate it.

 

[pbaldy]

I only meant the inner ones, like:

& "True, 'M', '')...

 

[liuec]

I only meant the inner ones, like:

& "True, 'M', '')...

I see what you mean here.
I'm interested in getting & " True, "M", "") as the result, I saw somewhere online that the quotation symbol should be replaced with & chr(34) & ?

 

[pbaldy]

Yes, it's just a lot more concatenation:

& "True, " & chr(34) & "M" & chr(34) & ...

 

[Xproterg]

My advice is to remove all of the quotes that return the query onto another line... and just make the query one long string. It's more confusing than it needs to be with all of those quotation marks.