Access 2000 query SQL statement into VBA code

[Guest]

I placed a question on here a few days ago about wanting to take a SQL statement behind an Access 2000 query and use it in a VBA statement. More specifically, I want to use the DoCmd.RunSQL statement.

Usually, I just copy the SQL statement code from the query and paste it into my module. I then just bring all the lines of text into one line and the RunSQL statement works like a charm. However, there have been several times where I could not get all the statement into one line. For some reason, Access won't let me bring part of the statement up. I think the issue is the quotes and the length. I guess you can only have so many characters across per line. I think I have figured out how to handle the quotes in my Dlookup statements, but I cannot get this code to come together, even with the "_" character. No matter where I split the statement I am getting an "Expected End of Statement" error with the 1st word of the 2nd line of text highlighted.

If anyone can help me figure out how to get this to work, I would be very grateful.

Here is a SQL statement that I am having problems with and I have included the quote changes I have added:

INSERT INTO tblForAccountingReport ( [Date], Week, [Year], Link, [Link Name], Title, CasinoID, Casino, SMNumber, Location, ClientNumber, [Coin In], [Amount Due], [Jackpot Liability], ParticipationPercent, [Tribal Contribution], Revenue, Discount ) SELECT qryAccounting1.Date, Format([Date],""ww"") AS Week, Format([Date],""yy"") AS [Year], qryAccounting1.Link, qryAccounting1.[Link Name], qryAccounting1.Title, qryAccounting1.CasinoID, qryAccounting1.Casino, qryAccounting1.SMNumber, qryAccounting1.Location, qryAccounting1.ClientNumber, qryAccounting1.Difference AS [Coin In], qryAccounting1.AtronicPct AS [Amount Due], [Difference]*(DLookUp(""[Percent]"",""tblJackpotLiabilityPercents"",""[LinkID] = '" & [Link] & "'"")) AS [Jackpot Liability], DLookUp(""[ParticipatePercent]"",""tblCasinoParticipationInfo"",""[LinkID] = '" & [Link] & "'" & " AND [CasinoID] = '" & [CasinoID] & "'"") AS ParticipationPercent, [Difference]*[ParticipationPercent] AS [Tribal Contribution], ([Amount Due]-[Jackpot Liability])-[Tribal Contribution] AS Revenue, 0 AS Discount FROM qryAccounting1 ORDER BY Format([Date],"ww"), qryAccounting1.[Link Name], qryAccounting1.Title, qryAccounting1.Casino;"

Thanks,
Clint

 

[Gary Walter]

Hi Clint,

I probably should have used a simpler example
where word wrap wouldn't screw this up,
but hopefully you will get the idea

Dim strSQL As String
Dim strCriteria As String
Dim strCriteria1 As String

strCriteria = """[LinkID] = '"" & [link] & ""'"""
strCriteria1 = """[LinkID] = '"" & [link] & ""' AND [CasinoID] = '"" & [CasinoID] &
""'"""

strSQL = "INSERT INTO tblForAccountingReport " _
& "... " _
& "[Difference]*(DLookUp(""[Percent]"",""tblJackpotLiabilityPercents""," &
strCriteria & ")) AS [Jackpot Liability], " _
& "DLookUp(""[ParticipatePercent]"",""tblCasinoParticipationInfo""," & strCriteria1 &
") AS ParticipationPercent, " _
& "...."
Debug.Print strSQL

in Debug window, I get:

INSERT INTO tblForAccountingReport ...
[Difference]*(DLookUp("[Percent]","tblJackpotLiabilityPercents","[LinkID] = '" &
[link] & "'")) AS [Jackpot Liability],
DLookUp("[ParticipatePercent]","tblCasinoParticipationInfo","[LinkID] = '" & [link] &
"' AND [CasinoID] = '" & [CasinoID] & "'") AS ParticipationPercent, ....

Good luck,

Gary Walter

I placed a question on here a few days ago about wanting to take a SQL statement

behind an Access 2000 query and use it in a VBA statement. More specifically, I want
to use the DoCmd.RunSQL statement.

Usually, I just copy the SQL statement code from the query and paste it into my

module. I then just bring all the lines of text into one line and the RunSQL
statement works like a charm. However, there have been several times where I could
not get all the statement into one line. For some reason, Access won't let me bring
part of the statement up. I think the issue is the quotes and the length. I guess
you can only have so many characters across per line. I think I have figured out how
to handle the quotes in my Dlookup statements, but I cannot get this code to come
together, even with the "_" character. No matter where I split the statement I am
getting an "Expected End of Statement" error with the 1st word of the 2nd line of
text highlighted.

If anyone can help me figure out how to get this to work, I would be very grateful.

Here is a SQL statement that I am having problems with and I have included the quote changes I have added:

INSERT INTO tblForAccountingReport ( [Date], Week, [Year], Link, [Link Name],

Title, CasinoID, Casino, SMNumber, Location, ClientNumber, [Coin In], [Amount Due],
[Jackpot Liability], ParticipationPercent, [Tribal Contribution], Revenue, Discount )
SELECT qryAccounting1.Date, Format([Date],""ww"") AS Week, Format([Date],""yy"") AS
[Year], qryAccounting1.Link, qryAccounting1.[Link Name], qryAccounting1.Title,
qryAccounting1.CasinoID, qryAccounting1.Casino, qryAccounting1.SMNumber,
qryAccounting1.Location, qryAccounting1.ClientNumber, qryAccounting1.Difference AS
[Coin In], qryAccounting1.AtronicPct AS [Amount Due],
[Difference]*(DLookUp(""[Percent]"",""tblJackpotLiabilityPercents"",""[LinkID] = '" &
[Link] & "'"")) AS [Jackpot Liability],
DLookUp(""[ParticipatePercent]"",""tblCasinoParticipationInfo"",""[LinkID] = '" &
[Link] & "'" & " AND [CasinoID] = '" & [CasinoID] & "'"") AS ParticipationPercent,
[Difference]*[ParticipationPercent] AS [Tribal Contribution], ([Amount Due]-[Jackpot
Liability])-[Tribal Contribution] AS Revenue, 0 AS Discount FROM qryAccounting1 ORDER
BY Format([Date],"ww"), qryAccounting1.[Link Name], qryAccounting1.Title,
qryAccounting1.Casino;"