Insert statement with errors

  • Thread starter Thread starter Alain
  • Start date Start date
A

Alain

Hi, I am getting the error Too few parameters Expected 1 when i try to run
the following SQL statement:

strSQL = "INSERT INTO BudgetGST ( Idbranch, Avril )" & _
" SELECT Idbranch, (Avril*cGST)" & _
" FROM GrossMois WHERE GrossMois.IdBranch = " & num

Avril is a field from GrossMois that contains a value and cGST is a constant.
can anyone tell me what is wrong

Thanks
Alain
 
What's this: (Avril*cGST)

If it is meant to be 2 fields multiplied together, supply an alias for the
result:
strSQL = "INSERT INTO BudgetGST ( Idbranch, Avril )" & _
" SELECT Idbranch, [Avril] * [cGST] AS Amount" & _
" FROM GrossMois WHERE GrossMois.IdBranch = " & num & ";"

If IdBranch is a Text field (not a Number field), you need extra quotes.

If that still doesn't solve it, add this line after yours:
Debug.Print strSql
Then when it fails, open the Immediate Window (Ctrl+G), copy the SQL
statement output there (Ctrl+C), and paste into SQL View in a new query
(Ctrl+V.) Then try to run the query, and see what parameter Access asks for.
 
The Jet engine knows nothing about VBA constants.

You'll need to write a public function that returns the value of the
constant:

Function GSTConstant() As Single
GSTConstant = cGST
End Function

and use that function in your query:

strSQL = "INSERT INTO BudgetGST ( Idbranch, Avril )" & _
" SELECT Idbranch, (Avril*GSTConstant())" & _
" FROM GrossMois WHERE GrossMois.IdBranch = " & num\

Note that the function must be in a standard module. In other words, it
cannot be in a class module, nor a module associated with a form or report.

That having been said, I have to question the design of a table that has
field names like Avril. Having months as fields is usually a sign that the
table isn't properly normalized.
 
Hi,

I have tried all possible combination, the problem really come from the
(Avril*cGST) part, 1 field multiply by a constant declared at the top of my
module,when I replace my constant, cGST, by a number it all works OK
Could it be possible a constant might not work with SQL ??

Alain

Allen Browne said:
What's this: (Avril*cGST)

If it is meant to be 2 fields multiplied together, supply an alias for the
result:
strSQL = "INSERT INTO BudgetGST ( Idbranch, Avril )" & _
" SELECT Idbranch, [Avril] * [cGST] AS Amount" & _
" FROM GrossMois WHERE GrossMois.IdBranch = " & num & ";"

If IdBranch is a Text field (not a Number field), you need extra quotes.

If that still doesn't solve it, add this line after yours:
Debug.Print strSql
Then when it fails, open the Immediate Window (Ctrl+G), copy the SQL
statement output there (Ctrl+C), and paste into SQL View in a new query
(Ctrl+V.) Then try to run the query, and see what parameter Access asks for.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Alain said:
Hi, I am getting the error Too few parameters Expected 1 when i try to run
the following SQL statement:

strSQL = "INSERT INTO BudgetGST ( Idbranch, Avril )" & _
" SELECT Idbranch, (Avril*cGST)" & _
" FROM GrossMois WHERE GrossMois.IdBranch = " & num

Avril is a field from GrossMois that contains a value and cGST is a
constant.
can anyone tell me what is wrong

Thanks
Alain
Click to expand...
Click to expand...
 
Doug,

I am using a db already created by another programmer, can you detail more
about the table design you question, the table is merely keeping taxes on a
monthly basis for budgeting purpose, the IdBranch is the FK and the other
fields are the 12 months of the year nothing else

Alain
 
Back
Top