Thank you so much! And terribly sorry about not placing tags on the code. I fixed what you both were talking about and I am reposting the code with tags. It's still not quite working but I feel its getting closer.
Also. Please remember I took this code from a posting I found. If you have a better way to pass the parameters from the form to the query I am open to it. My problem right now is this sort of VBA is beyond my understanding and why I need help from you pros.
Code:
Private Sub DateOKButton_Click()
On Error GoTo Err_DateOKButton_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM Claims "
'Build the IN string by looping through the listbox
For i = 0 To DentalPracticeNameListBx.ListCount - 1
If DentalPracticeNameListBx.Selected(i) Then
If DentalPracticeNameListBx.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & DentalPracticeNameListBx.Column(0, i) & "',"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
If Not IsNull(Me.StartDateTxtBx) And Not IsNull(Me.EndDateTxtBx) = True Then
strWhere = "WHERE [InvoiceDate] BETWEEN #" & Me.StartDateTxtBx & "# AND #" & Me.EndDateTxtBx & "# "
With Me.DentalPracticeNameListBx
If .Value <> "All" Then
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strIN = strIN & strDelim & .ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
strIN = Left(strIN, Len(strIN) - 1) & ")"
End If
End With
strWhere = strWhere & strIN
End If
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "ClaimsBatchSummaryQuery"
Set qdef = MyDB.CreateQueryDef("ClaimsBatchSummaryQuery", strSQL)
'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "ClaimsBatchSummaryQuery"
'Clear listbox selection after running query
For Each varItem In Me.DentalPracticeNameListBx.ItemsSelected
Me.DentalPracticeNameListBx.Selected(varItem) = False
Next varItem
Exit_DateOKButton_Click:
Exit Sub
Err_DateOKButton_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_DateOKButton_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_DateOKButton_Click
End If
End Sub
And this is supposed to feed this query. And to answer your previous statement, I read I needed to remove the criteria from the query and let the VBA code place the criteria in the query. If that is incorrect then please let me know.
Code:
SELECT Claims.CIInvoiceNumber AS InvoiceNumber, Claims.InvoiceDate, Claims.PracticeReferenceNumber, Claims.PractitionerID, Claims.DOB, Claims.PractitionerName, Claims.DentalPracticeName, Claims.PolicyNumber, Claims.MemberFirstName, Claims.MemberLastName, Claims.PaymentType, Claims.NonDiscountedAmountTotal, Claims.DiscountedAmountTotal, Claims.MemberCollectedAmountTotal, Claims.ReimbursedAmountTotal, Claims.MSAdminFeeTotal
FROM Claims
GROUP BY Claims.CIInvoiceNumber, Claims.InvoiceDate, Claims.PracticeReferenceNumber, Claims.PractitionerID, Claims.DOB, Claims.PractitionerName, Claims.DentalPracticeName, Claims.PolicyNumber, Claims.MemberFirstName, Claims.MemberLastName, Claims.PaymentType, Claims.NonDiscountedAmountTotal, Claims.DiscountedAmountTotal, Claims.MemberCollectedAmountTotal, Claims.ReimbursedAmountTotal, Claims.MSAdminFeeTotal;