Too few parameters

  • Thread starter Thread starter Sam Coburn
  • Start date Start date
S

Sam Coburn

I'm getting too few parameters, Expected 1

I know it's in the syntax, but i haven't been able to figure it out. I
think the control reference in the IIF statement might be the problem. I've
tried adding " in different places...just haven't been successfull. Is the
first part of the IIF a string?


strSQL = "UPDATE Aptrh SET Aptrh.ActAmtToPay =
IIf([DiscDate]>=[forms]![frmAPInvDueBySelect]![txtSelectDate],[DiscAmt],[Amo
unt]) " _
& "WHERE (((Aptrh.Paid)=0));"

Set db = CurrentDb
db.Execute strSQL

Thanks for your help.
 
strSQL = "UPDATE Aptrh SET Aptrh.ActAmtToPay =
IIf([DiscDate]>=[forms]![frmAPInvDueBySelect]![txtSelectDate],[DiscAmt],[Amo
unt]) " _
& "WHERE (((Aptrh.Paid)=0));"
Click to expand...

Check what data field is bound to txtSelectDate, and whether it's spelled
correctly.

I guess you've checked whether table Aptrh hold all these fields:
- ActAmtToPay
- DiscDate
- DiscAmt
- Amount
- Paid

.... and that they are spelled correctly.

HTH - Peter
 
Yes. I made the SQL statement from a query that runs OK.

Peter Doering said:
strSQL = "UPDATE Aptrh SET Aptrh.ActAmtToPay =
IIf([DiscDate]>=[forms]![frmAPInvDueBySelect]![txtSelectDate],[DiscAmt],[Amo
unt]) " _
& "WHERE (((Aptrh.Paid)=0));"
Click to expand...

Check what data field is bound to txtSelectDate, and whether it's spelled
correctly.

I guess you've checked whether table Aptrh hold all these fields:
- ActAmtToPay
- DiscDate
- DiscAmt
- Amount
- Paid

... and that they are spelled correctly.

HTH - Peter
Click to expand...
 
Is Form frmAPInvDueBySelect open when you're trying to run this query? It
must be, since the query's looking for a value from the form.
 
Yes. I'm running the code from a command button on the form.

Douglas J. Steele said:
Is Form frmAPInvDueBySelect open when you're trying to run this query? It
must be, since the query's looking for a value from the form.

--
Doug Steele, Microsoft Access MVP



Sam Coburn said:
I'm getting too few parameters, Expected 1

I know it's in the syntax, but i haven't been able to figure it out. I
think the control reference in the IIF statement might be the problem. I've
tried adding " in different places...just haven't been successfull. Is the
first part of the IIF a string?


strSQL = "UPDATE Aptrh SET Aptrh.ActAmtToPay =
Click to expand...
IIf([DiscDate]>=[forms]![frmAPInvDueBySelect]![txtSelectDate],[DiscAmt],[Amo
unt]) " _
& "WHERE (((Aptrh.Paid)=0));"

Set db = CurrentDb
db.Execute strSQL

Thanks for your help.
Click to expand...
Click to expand...
 
You need to resolve the reference to the Control on the
Form in the SQL String before passing it to JET. Try:

strSQL = "UPDATE Aptrh SET Aptrh.ActAmtToPay = " & _
" IIf([DiscDate]>= " & _
Format([forms]![frmAPInvDueBySelect]![txtSelectDate],
"\#mm/dd/yyyy\#") & ", [DiscAmt],[Amount]) " _
& " WHERE (((Aptrh.Paid)=0));"

Watch out for line-breaks due to newsgroup.

HTH
Van T. Dinh
MVP (Access)
 
When you run the Query through the QBE, Access Expression resolves the
reference to the Control on the Form before passing the SQL String to the
JET database engine so it works fine.

When you execute the SQL String in code, VBA does not resolve the reference
and simply passes the SQL String to JET. Since JET doesn't know Forms /
Controls so it assumes the reference to be a parameter which it hasn't got a
value. Hence, you have the "Too few parameters" error.

Since we are passing literal date to JET, the literal date MUST be in US
format "mm/dd/yyyy" and delimited by #. If your default short date format
is "mm/dd/yyyy", it MAY work without the Format() function but you need to
delimited by # in the SQL String construction. It may be a good idea to set
the Format for the Control on the Form to "mm/dd/yyyy" if it is suitable.

In my case in Australia, my default short date format is "dd/mm/yyyy", thus
I need to use the Format() function to convert my date value to a String
with US date format.

--
HTH
Van T. Dinh
MVP (Access)



Sam Coburn said:
That worked!

Could you please help me to understand why the format function was
necessary?

Is the first part of the IIF a string?

Would it have resolved if I used # before and after the forms reference?

Thanks you very much.

You need to resolve the reference to the Control on the
Form in the SQL String before passing it to JET. Try:

strSQL = "UPDATE Aptrh SET Aptrh.ActAmtToPay = " & _
" IIf([DiscDate]>= " & _
Format([forms]![frmAPInvDueBySelect]![txtSelectDate],
"\#mm/dd/yyyy\#") & ", [DiscAmt],[Amount]) " _
& " WHERE (((Aptrh.Paid)=0));"

Watch out for line-breaks due to newsgroup.

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
I'm getting too few parameters, Expected 1

I know it's in the syntax, but i haven't been able to figure it out. I
think the control reference in the IIF statement might be the problem. I've
tried adding " in different places...just haven't been successfull. Is the
first part of the IIF a string?


strSQL = "UPDATE Aptrh SET Aptrh.ActAmtToPay =
IIf([DiscDate]>=[forms]![frmAPInvDueBySelect]! [txtSelectDate],[DiscAmt],[Amo
unt]) " _
& "WHERE (((Aptrh.Paid)=0));"

Set db = CurrentDb
db.Execute strSQL

Thanks for your help.






.
Click to expand...
Click to expand...
Click to expand...
 
Thanks you for the explanation. I didn't realize the difference in making a
SQL statement from the qbe and how jet interprets it.

Thanks again.
 
Back
Top