too few parameters error not understanding why??

this is a access application, i have a method that gets all the selected values from a listbox and is delimited by a comma. i than take those values and pass it into a where clause and use the 'IN' operator on it but i am getting the too few parameters error.

my code.

DoCmd.SetWarnings False
SelectedList = GetSelectedItems(Me.WeekSelector)
Query = "INSERT INTO [WorkTable]([L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE], ID)" _
& " SELECT LoadTable.[L Batch ID], LoadTable.[Pay Group], LoadTable.[Pay Group Description], LoadTable.[General Ledger Account], LoadTable.[General Ledger Cost Center], LoadTable.[General Ledger Department], LoadTable.[Work Center], LoadTable.[Pay Period Ending Date], LoadTable.Hours, LoadTable.Amount, LoadTable.Week, LoadTable.[Pay Type Code], LoadTable.[Pay Type Description], LoadTable.[File Number], LoadTable.Name, LoadTable.[HOURLY SALARY], LoadTable.[FULL TIME_PART TIME], LoadTable.ACTIVE_INACTIVE, LoadTable.[HOURLY RATE], LoadTable.ID" _
& " FROM LoadTable " _
& " WHERE (((LoadTable.Week) IN (SelectedList)));"
CurrentDb.Execute (Query)
DoCmd.SetWarnings True

Open in new window




Public Function GetSelectedItems(lBox As Object) As String
'returns an array of selected items in a ListBox
Dim tmpArray() As Variant
Dim i As Integer
Dim selCount As Integer
    selCount = -1
    '## Iterate over each item in the ListBox control:
    For i = 0 To lBox.ListCount - 1
        '## Check to see if this item is selected:
        If lBox.Selected(i) = True Then
            '## If this item is selected, then add it to the array
            selCount = selCount + 1
            ReDim Preserve tmpArray(selCount)
            tmpArray(selCount) = lBox.ItemData(i)
            
        End If
    Next

    If selCount = -1 Then
        '## If no items were selected, return an empty string
        GetSelectedItems = "" ' or "No items selected", etc.
    Else:
        '## Otherwise, return the array of items as a string,
        '   delimited by commas
        GetSelectedItems = Join(tmpArray, ", ")
    End If
End Function

Open in new window

Aravind RanganathanWindows Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
What specific line are you getting the error on?


»bp
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@ Bill Prew line number 6 on the where condition.
0
Gustav BrockCIOCommented:
You must concatenate the list:

& " WHERE (((LoadTable.Week) IN (" & SelectedList & ")));"

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
First off, get rid of the SetWarnings command.  My personal advice is to eliminate this command from your memory and never use it again.  You have already taken the next step, which is to use the Execute method when running action queries, and since Execute won't raise warnings, you really don't need those.

Now to your code:

SelectedList = GetSelectedItems(Me.WeekSelector)
Query = "INSERT INTO [WorkTable]([L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], " _
                  & "[General Ledger Cost Center], [General Ledger Department], [Work Center], " _
                  & "[Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], " _
                  & "[Pay Type Description], [File Number], Name, [HOURLY SALARY], " _
                  & "[FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE], ID)" _
       & " SELECT LoadTable.[L Batch ID], LoadTable.[Pay Group], LoadTable.[Pay Group Description], LoadTable.[General Ledger Account], " _
                    & "LoadTable.[General Ledger Cost Center], LoadTable.[General Ledger Department], LoadTable.[Work Center], " _
                    & "LoadTable.[Pay Period Ending Date], LoadTable.Hours, LoadTable.Amount, LoadTable.Week, LoadTable.[Pay Type Code], " _
                    & "LoadTable.[Pay Type Description], LoadTable.[File Number], LoadTable.Name, LoadTable.[HOURLY SALARY], " _
                    & "LoadTable.[FULL TIME_PART TIME], LoadTable.ACTIVE_INACTIVE, LoadTable.[HOURLY RATE], LoadTable.ID " _
       & "FROM LoadTable " _
       & "WHERE (((LoadTable.Week) IN (" & SelectedList & ")));"
CurrentDb.Execute (Query)

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.