Insert statement with errors

[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

 

[Allen Browne]

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.

 

[Douglas J. Steele]

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.

 

[Alain]

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

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.

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

 

[Alain]

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

 

[Douglas J. Steele]

Jeff Conrad has a good list of resources at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

By having 12 fields, one for each month, you've got what's known as a
repeating group, which violates database normalization principles. Rather
than 12 fields in a single row, you should have 12 separate rows, one for
each month.