Issue with Listbox and Textbox Form for Generating Report

[pkeller]

I have a form I am using to generate a report which will be by company and date. I have a list box set up for the company names and a text box for my date.

I managed to find some code to get the listbox to populate a query with the selections made and then generate a report off that but I can't seem to figure out how to insert my date textbox criteria to get into the query. I have a basic understanding of what my code is doing but really cannot figure out what kind of statement or where to insert it into the code below:

Private Sub OK_Click()

    On Error GoTo Err_OK_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 tblIssues"

    'Build the IN string by looping through the listbox
    For i = 0 To LstCompany.ListCount - 1
        If LstCompany.Selected(i) Then
            If LstCompany.Column(0, i) = "All" Then
                flgSelectAll = True
            End If
            strIN = strIN & "'" & LstCompany.Column(0, i) & "',"
        End If
    Next i

    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE [Company] in " & _
               "(" & Left(strIN, Len(strIN) - 1) & ")"

    '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 "qryIssues"
    Set qdef = MyDB.CreateQueryDef("qryIssues", strSQL)

    'Open the query, built using the IN clause to set the criteria
    'DoCmd.OpenQuery "qryIssues", acViewNormal

    'Clear listbox selection after running query
    For Each varItem In Me.LstCompany.ItemsSelected
        Me.LstCompany.Selected(varItem) = False
    Next varItem

Exit_OK_Click:
    Me.Visible = False
    Exit Sub

Err_OK_Click:
    If Err.Number = 5 Then
        MsgBox "You must make a selection(s) from the list" _
               , , "Selection Required !"
        Exit Sub
    Else
        'Write out the error and exit the sub
        MsgBox Err.Description
        Resume Exit_OK_Click
    End If
End Sub

All I want is for the report to generate off the company name(s) and date typed in to my "LstYear" field. Can anyone help me?

 

[lespaul]

I'm guessing this is on the OK button that is on the form with the list box and the text box for the date selection? I think you could add the bold section and it would work:

Private Sub OK_Click()

    On Error GoTo Err_OK_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 tblIssues"

    'Build the IN string by looping through the listbox
    For i = 0 To LstCompany.ListCount - 1
        If LstCompany.Selected(i) Then
            If LstCompany.Column(0, i) = "All" Then
                flgSelectAll = True
            End If
            strIN = strIN & "'" & LstCompany.Column(0, i) & "',"
        End If
    Next i

    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE [b][SomeDateField] = " & DateTextBoxName.Value & " [/b]AND [Company] in " & _
               "(" & Left(strIN, Len(strIN) - 1) & ")"

    '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 "qryIssues"
    Set qdef = MyDB.CreateQueryDef("qryIssues", strSQL)

    'Open the query, built using the IN clause to set the criteria
    'DoCmd.OpenQuery "qryIssues", acViewNormal

    'Clear listbox selection after running query
    For Each varItem In Me.LstCompany.ItemsSelected
        Me.LstCompany.Selected(varItem) = False
    Next varItem

Exit_OK_Click:
    Me.Visible = False
    Exit Sub

Err_OK_Click:
    If Err.Number = 5 Then
        MsgBox "You must make a selection(s) from the list" _
               , , "Selection Required !"
        Exit Sub
    Else
        'Write out the error and exit the sub
        MsgBox Err.Description
        Resume Exit_OK_Click
    End If
End Sub

it may need some modificaitons since it's a date, but that's the idea.

HTH

Leslie

Have you met Hardy Heron?

 

[pkeller]

Thanks for the input, I actually have been playing around with that line to get the query to work right. Problem is that when I look at the query it is generating with the suggestion you gave me above the criterial field shows "12/31/2007" which gives me an error on my report and an empty query. When I manually type =12/31/2007 in the query criteria my query works. What characters should be added to the above bolded code to get the query criteria to show up properly (=12/31/2007) vs how it is currently showing up (12/31/2007)?

You are correct that this is bound to the "OK" button on my form.

 

[PHV]

strWhere = " WHERE [SomeDateField]=#" & Format(DateTextBoxName.Value, "yyyy-mm-dd") & "# AND ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pkeller]

Works, thank you very much for the assistance!