Too Few Parameters Expected 2 Error (1 Viewer)

Mackbear · Sep 13, 2019

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 · Sep 13, 2019

date fields should be delimited with "#"

Uncle Gizmo · Sep 13, 2019

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 · Sep 13, 2019

arnelgp said:
date fields should be delimited with "#"

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

Mackbear · Sep 13, 2019

Uncle Gizmo said:
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 · Sep 13, 2019

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

Mackbear said:
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 · Sep 13, 2019

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 · Sep 13, 2019

Hi. You have this line in your code:

Debug.Print sqlstrapdec

Please post the result of that line here. Thanks.

Uncle Gizmo · Sep 13, 2019

Mackbear said:
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 · Oct 10, 2019

CJ_London said:
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...

Too Few Parameters Expected 2 Error (1 Viewer)

Mackbear

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Uncle Gizmo

Nifty Access Guy

Mackbear

Registered User.

Mackbear

Registered User.

Gasman

Enthusiastic Amateur

CJ_London

Super Moderator

theDBguy

I’m here to help

Uncle Gizmo

Nifty Access Guy

Mackbear

Registered User.

Users who are viewing this thread