Append Query code to VBA statement

[Guest]

I am trying to convert the SQL code from an append query to a DoCmd.RunSQL statement. I have the SQL for it below. For some reason, I cannot get all of this info correct in the VBA statement.

Also, if you happen to know where I can get a utility to translate queries for me, I would really appreciate it. I saw one a few years ago, but I cannot remember where it is

Thanks in advance
Clin

SQL

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 qryAccountingForExecRpts.Date, Format([Date],"ww") AS Week, Format([Date],"yy") AS [Year], qryAccountingForExecRpts.Link, qryAccountingForExecRpts.[Link Name], qryAccountingForExecRpts.Title, qryAccountingForExecRpts.CasinoID, qryAccountingForExecRpts.Casino, qryAccountingForExecRpts.SMNumber, qryAccountingForExecRpts.Location, qryAccountingForExecRpts.ClientNumber, qryAccountingForExecRpts.Difference AS [Coin In], qryAccountingForExecRpts.AtronicPct AS [Amount Due], [Difference]*(DLookUp("[Percent]","tblJackpotLiabilityPercents","[Link] = '" & [Link Name] & "'")) 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 Discoun
FROM qryAccountingForExecRpt
ORDER BY Format([Date],"ww"), qryAccountingForExecRpts.[Link Name], qryAccountingForExecRpts.Title, qryAccountingForExecRpts.Casino;

 

[[MVP] S.Clark]

What specific error messages are you receiving?

Which Casino?

--
Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.fmsinc.com/consulting/

I am trying to convert the SQL code from an append query to a DoCmd.RunSQL

statement. I have the SQL for it below. For some reason, I cannot get all
of this info correct in the VBA statement.

Also, if you happen to know where I can get a utility to translate queries

for me, I would really appreciate it. I saw one a few years ago, but I
cannot remember where it is.

Thanks in advance,
Clint

SQL:

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 qryAccountingForExecRpts.Date, Format([Date],"ww") AS Week,

Format([Date],"yy") AS [Year], qryAccountingForExecRpts.Link,
qryAccountingForExecRpts.[Link Name], qryAccountingForExecRpts.Title,
qryAccountingForExecRpts.CasinoID, qryAccountingForExecRpts.Casino,
qryAccountingForExecRpts.SMNumber, qryAccountingForExecRpts.Location,
qryAccountingForExecRpts.ClientNumber, qryAccountingForExecRpts.Difference
AS [Coin In], qryAccountingForExecRpts.AtronicPct AS [Amount Due],
[Difference]*(DLookUp("[Percent]","tblJackpotLiabilityPercents","[Link] = '"
& [Link Name] & "'")) 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 qryAccountingForExecRpts
ORDER BY Format([Date],"ww"), qryAccountingForExecRpts.[Link Name],

qryAccountingForExecRpts.Title, qryAccountingForExecRpts.Casino;

 

[John Vinson]

I am trying to convert the SQL code from an append query to a DoCmd.RunSQL statement. I have the SQL for it below. For some reason, I cannot get all of this info correct in the VBA statement.

Also, if you happen to know where I can get a utility to translate queries for me, I would really appreciate it. I saw one a few years ago, but I cannot remember where it is.

Thanks in advance,
Clint

SQL:

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 qryAccountingForExecRpts.Date, Format([Date],"ww") AS Week, Format([Date],"yy") AS [Year], qryAccountingForExecRpts.Link, qryAccountingForExecRpts.[Link Name], qryAccountingForExecRpts.Title, qryAccountingForExecRpts.CasinoID, qryAccountingForExecRpts.Casino, qryAccountingForExecRpts.SMNumber, qryAccountingForExecRpts.Location, qryAccountingForExecRpts.ClientNumber, qryAccountingForExecRpts.Difference AS [Coin In], qryAccountingForExecRpts.AtronicPct AS [Amount Due], [Difference]*(DLookUp("[Percent]","tblJackpotLiabilityPercents","[Link] = '" & [Link Name] & "'")) 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 qryAccountingForExecRpts
ORDER BY Format([Date],"ww"), qryAccountingForExecRpts.[Link Name], qryAccountingForExecRpts.Title, qryAccountingForExecRpts.Casino;

My guess is that you're having trouble building a SQL string constant
because your SQL contains plenty of ' and " characters already. To
include a " in a string delimited by " you need to double it up. Try
something like

strSQL = "... , Format([Date], ""ww"") As Week, Format([Date], ""yy"")
As [Year}, ...