Anybody see the error in this statement??

[notageek7]

Does anybody see the error in the following sql statement? Some of the lines below have been moved to the line below because of the width limit of this text input.

'Add Quota
sqlString = "INSERT INTO Quota ( Quota_Key, Comp_Plan_Key, Category, " & _
"QuotaPeriod, TargetPeriod, AttainmentPeriod, RateType ) " & _
"SELECT " & quotaKey & " AS Expr1, " & _
Chr(34) & newCompPlanKey & Chr(34) & " AS Expr2, " & _
Chr(34) & rst("MS_Category") & Chr(34) & " AS Expr3, " & _
Chr(34) & rst("QuotaPeriod") & Chr(34) & " AS Expr4, " & _
Chr(34) & rst("TargetPeriod") & Chr(34) & " AS Expr5, " & _
Chr(34) & rst("AttainmentPeriod") & Chr(34) & " AS Expr6, " & _
Chr(34) & rst("RateType") & Chr(34) & " AS Expr7;"
DoCmd.RunSQL (sqlString)

 

[lespaul]

other than the fact there's no FROM clause in your SELECT statement.


Leslie

 

[traingamer]

Well, you don't need the aliases (as expr...), but the biggest problem is that you don't have a FROM clause for your SELECT fields.

You also do not need to quote numeric fields (but you probably already know that).

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

Try the INSERT INTO ... VALUES ... syntax.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[notageek7]

Here is the code below that has been functioning well for a couple years. I'm just trying to implement this original code with another variable added, 'QuotaKey' which is a variable that holds the next available primary key value from an oracle db because there isn't an autonumber in oracle. The original code works because the select.... part pulls in data and is the 'values' part of the sql structure...it isn't required to use the word 'values'. I'm still learning more advanced sql statements so needed a little help with my syntax on the above statement. Thank you for spending the time to look at my code.

'Add Quota
sqlString = "INSERT INTO Quota ( Comp_Plan_Key, Category, " & _
"QuotaPeriod, TargetPeriod, AttainmentPeriod, RateType ) " & _
"SELECT " & newCompPlanKey & " AS Expr1, " & _
Chr(34) & rst("MS_Category") & Chr(34) & " AS Expr2, " & _
Chr(34) & rst("QuotaPeriod") & Chr(34) & " AS Expr3, " & _
Chr(34) & rst("TargetPeriod") & Chr(34) & " AS Expr4, " & _
Chr(34) & rst("AttainmentPeriod") & Chr(34) & " AS Expr5, " & _
Chr(34) & rst("RateType") & Chr(34) & " AS Expr6;"
DoCmd.RunSQL (sqlString)

 

[notageek7]

ahhh, jusf figured it out....the statement does work. My problem was I used the oracle reserved word 'Category' instead of 'MS_Category' in the first line....insert into...

 

[traingamer]

Your current working version has no quotes on newCompPlanKey, but your changed on has quotes (chr(34)).
Try removing them.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill