SQL statement

[Cindy]

Can anyone help. I have the code below and what I am
trying to do is Add records to the TarkettTest table by
pulling TktCUSTNum from TarkettDistributors and feeding
the values of variables defined in code as MyYear and
RebatePercent. InsertSQL is my string variable.

InsertSQL = ""
InsertSQL = "INSERT INTO "
InsertSQL = InsertSQL & "TarkettTest "
InsertSQL = InsertSQL & "([TktCUSTNum], [Year],
[PurchPcntAbove]) "
InsertSQL = InsertSQL & "SELECT "
InsertSQL = InsertSQL & "TarkettDistributors.TktCUSTNum,
MyYear AS [Year], RebatePercent AS [PurchPcntAbove] "
InsertSQL = InsertSQL & "FROM "
InsertSQL = InsertSQL & "TarkettDistributors;"
DoCmd.RunSQL (InsertSQL)

Thank you.

Cindy

 

[John Vinson]

Can anyone help. I have the code below and what I am
trying to do is Add records to the TarkettTest table by
pulling TktCUSTNum from TarkettDistributors and feeding
the values of variables defined in code as MyYear and
RebatePercent. InsertSQL is my string variable.

InsertSQL = ""
InsertSQL = "INSERT INTO "
InsertSQL = InsertSQL & "TarkettTest "
InsertSQL = InsertSQL & "([TktCUSTNum], [Year],
[PurchPcntAbove]) "
InsertSQL = InsertSQL & "SELECT "
InsertSQL = InsertSQL & "TarkettDistributors.TktCUSTNum,
MyYear AS [Year], RebatePercent AS [PurchPcntAbove] "
InsertSQL = InsertSQL & "FROM "
InsertSQL = InsertSQL & "TarkettDistributors;"
DoCmd.RunSQL (InsertSQL)

Thank you.

You're putting the names of the VBA variables MyYear and RebatePercent
into the SQL string *as names*. SQL has no access to VBA variables;
they are different domains. Concatenate the *values* of the variables
instead. Assuming that they are numeric fields, try

InsertSQL = InsertSQL & "TarkettDistributors.TktCUSTNum,
" & MyYear & " AS [Year], " & RebatePercent & " AS [PurchPcntAbove] "

Run this in Debug mode and check the value of InsertSQL after it's
built to doublecheck that it's valid.

 

[Cindy]

Thanks, John

It works! Don't know what I'd do with the newsgroups and
those that respond. I sincerely appreciate it.

Cindy

-----Original Message-----

Can anyone help. I have the code below and what I am
trying to do is Add records to the TarkettTest table by
pulling TktCUSTNum from TarkettDistributors and feeding
the values of variables defined in code as MyYear and
RebatePercent. InsertSQL is my string variable.

InsertSQL = ""
InsertSQL = "INSERT INTO "
InsertSQL = InsertSQL & "TarkettTest "
InsertSQL = InsertSQL & "([TktCUSTNum], [Year],
[PurchPcntAbove]) "
InsertSQL = InsertSQL & "SELECT "
InsertSQL = InsertSQL & "TarkettDistributors.TktCUSTNum,
MyYear AS [Year], RebatePercent AS [PurchPcntAbove] "
InsertSQL = InsertSQL & "FROM "
InsertSQL = InsertSQL & "TarkettDistributors;"
DoCmd.RunSQL (InsertSQL)

Thank you.

You're putting the names of the VBA variables MyYear and RebatePercent
into the SQL string *as names*. SQL has no access to VBA variables;
they are different domains. Concatenate the *values* of the variables
instead. Assuming that they are numeric fields, try

InsertSQL = InsertSQL & "TarkettDistributors.TktCUSTNum,
" & MyYear & " AS [Year], " & RebatePercent & " AS [PurchPcntAbove] "

Run this in Debug mode and check the value of InsertSQL after it's
built to doublecheck that it's valid.





.