Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Anybody see the error in this statement??

Status
Not open for further replies.

notageek7

Technical User
Jan 23, 2004
54
US
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)
 
other than the fact there's no FROM clause in your SELECT statement.


Leslie
 
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
 
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
 
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)
 
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...
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top