Too Few Parameters Expected 2 Error

[Mackbear]

Hello everyone, hope you can help. I've been staring at this code for hours and I an unable to figure out what is wrong. It is saying Too Few Parameters Expected 2... :banghead::banghead::banghead: I checked the controls they all have values. I have another one like this on another form and it works. I made sure as well that this is exactly how it written as the other on that works.. :banghead::banghead::banghead:

Dim sqlstrapdec As String

sqlstrapdec = "UPDATE tbl_LFReview SET tbl_LFReview.[LFCreditedAmount]='" & Me.LFCreditedAmount & "', tbl_LFReview.[LFCreditedDate]='" & Me.lfcreditdate & "', tbl_LFReview.[LFReviewNotes]='" & Me.LFReviewNotes & "', tbl_LFReview.[LFCreditStatus]='" & Me.LFCreditStatus & "', tbl_LFReview.[LFReviewedby]='" & Me.ReqbyAP & "', tbl_LFReview.[LFReviewedName]='" & Me.apfullname & "', tbl_LFReview.[LFReviewedDate]='" & Me.ReqDate & "', tbl_LFReview.[LFReviewedDateTime]='" & Me.ReqDateTime & "', tbl_LFReview.[LFReviewedWeek]='" & Me.ReqWeek & "', tbl_LFReview.[LFReviewedMonth]='" & Me.ReqMonth & "', tbl_LFReview.[LFReviewStatus]='" & Me.stat & "' WHERE tbl_LFReview.[entryid_lfreview] IN (" & Replace(SelectedList, "|", ",") & ")"
Debug.Print sqlstrapdec

CurrentDb.Execute sqlstrapdec

 

[arnelgp]

date fields should be delimited with "#"

 

[Uncle Gizmo]

Video number 2 of my blog here suggests ways you can simplify complicated SQL Statements:-

https://www.niftyaccess.com/insert-into-sql-statement/



Sent from Barcelona!

 

[Mackbear]

date fields should be delimited with "#"

I have tried that as well, still saying the same error message ='c

 

[Mackbear]

Video number 2 of my blog here suggests ways you can simplify complicated SQL Statements:-

https://www.niftyaccess.com/insert-into-sql-statement/



Sent from Barcelona!

Hello, thanks for this, it looks like adding new data to a table using the Insert query. might be handy in the future, but for now I am trying to update the existing record. Thanks

 

[Gasman]

Like is no good.

When this has happened to me, it is generally spelling mistakes, as access will not recognise the misspelt data.

Instead of jumping in feet first all the time, why not dip your toe in the water first.?

Debug.Print that sql string in the immediate window and have a breakpoint set.
Then paste all of that string into a new sql window.
That should then ask you for the missing parameters I would have thought.?

Hello everyone, hope you can help. I've been staring at this code for hours and I an unable to figure out what is wrong. It is saying Too Few Parameters Expected 2... :banghead::banghead::banghead: I checked the controls they all have values. I have another one like this on another form and it works. I made sure as well that this is exactly how it written as the other on that works.. :banghead::banghead::banghead:

Dim sqlstrapdec As String

sqlstrapdec = "UPDATE tbl_LFReview SET tbl_LFReview.[LFCreditedAmount]='" & Me.LFCreditedAmount & "', tbl_LFReview.[LFCreditedDate]='" & Me.lfcreditdate & "', tbl_LFReview.[LFReviewNotes]='" & Me.LFReviewNotes & "', tbl_LFReview.[LFCreditStatus]='" & Me.LFCreditStatus & "', tbl_LFReview.[LFReviewedby]='" & Me.ReqbyAP & "', tbl_LFReview.[LFReviewedName]='" & Me.apfullname & "', tbl_LFReview.[LFReviewedDate]='" & Me.ReqDate & "', tbl_LFReview.[LFReviewedDateTime]='" & Me.ReqDateTime & "', tbl_LFReview.[LFReviewedWeek]='" & Me.ReqWeek & "', tbl_LFReview.[LFReviewedMonth]='" & Me.ReqMonth & "', tbl_LFReview.[LFReviewStatus]='" & Me.stat & "' WHERE tbl_LFReview.[entryid_lfreview] IN (" & Replace(SelectedList, "|", ",") & ")"
Debug.Print sqlstrapdec

CurrentDb.Execute sqlstrapdec

 

[CJ_London]

I agree with Arnel, you need to deliminate dates with # - and the date format needs to be US format of mm/dd/yyyy if you are going to treat it as text.

Also, just to confirm - your entryid_lfreview field is numeric and not text?

 

[theDBguy]

Hi. You have this line in your code:

Debug.Print sqlstrapdec

Please post the result of that line here. Thanks.

 

[Uncle Gizmo]

looks like adding new data to a table using the Insert query.

Indeed, It's not the same scenario as yours, but the basic principles shown in that video apply, divide and conquer!

You could pass your textbox values through as variables into the function and then format the strings and dates, adding the delimiters as required, insuring that the formatting is done in a consistent and repeatable way.

Also you can take the text version of the date from any format and have it formatted correctly for the SQL Statement.

Sent from Barcelona!

 

[Mackbear]

I agree with Arnel, you need to deliminate dates with # - and the date format needs to be US format of mm/dd/yyyy if you are going to treat it as text.

Also, just to confirm - your entryid_lfreview field is numeric and not text?

Thanks for this I have the hash for the dates and it works now, and yes the entryid is numeric. I apologize for the late reply...