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?
 
Gustav BrockConnect With a Mentor CIOCommented:
You must concatenate the list:

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

Open in new window

/gustav
0
 
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
 
Dale FyeConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.