C
cherman
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
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