Too few parameters

[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.

 

[Peter Doering]

strSQL = "UPDATE Aptrh SET Aptrh.ActAmtToPay =

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

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

 

[Sam Coburn]

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

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

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

 

[Douglas J. Steele]

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.

 

[Sam Coburn]

Yes. I'm running the code from a command button on the form.

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

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.

 

[Van T. Dinh]

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)

 

[Sam Coburn]

It's an unbound control with a short date format.

 

[Van T. Dinh]

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)

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.






.

 

[Sam Coburn]

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.