Updating listbox rowsource not working

[j2curtis64]

Hello,

I am trying to add a criteria to a form. I am pulling the data from a table. All the criteria are working correctly except for the last one "date". The data type for date is "Date/Time. The other fields are all text. See text in bold and underline.

When i go to the text box in the form and enter the date the following message is received from Access: "Enter Parameter Value", then when i enter the date all the records are returned. The filter is not working.

see code below

'Constant Select statement for the RowSource
strSQL = "SELECT issues.id, issues.Month,issues.ApprovingOfficial, issues.CardHolder, issues.RequisitionNumber, issues.TransactionNumber, issues.Category, issues.Status,issues.DateIssueOpened,issues.Close_ Date,issues.Last_Update_Date,issues.date " & _
"FROM issues"
strWhere = "WHERE"
strOrder = "ORDER BY issues.id;"

'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.CardHolder) Then '<--If the textbox txtcardholder contains no data THEN do nothing
strWhere = strWhere & " (ISSUES.cardholder) Like '*" & Me.CardHolder & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.ApprovingOfficial) Then
strWhere = strWhere & " (ISSUES.approvingofficial) Like '*" & Me.ApprovingOfficial & "*' AND"
End If


If Not IsNull(Me.RequisitionNumber) Then
strWhere = strWhere & " (ISSUES.RequisitionNumber) Like '*" & Me.RequisitionNumber & "*' AND"
End If
If Not IsNull(Me.TransactionNumber) Then
strWhere = strWhere & " (ISSUES.transactionnumber) Like '*" & Me.TransactionNumber & "*' AND"
End If
If Not IsNull(Me.Category) Then
strWhere = strWhere & " (ISSUES.category) Like '*" & Me.Category & "*' AND"
End If
If Not IsNull(Me.Status) Then
strWhere = strWhere & " (ISSUES.status) Like '*" & Me.Status & "*' AND"
End If
If Not IsNull(Me.Date) Then
strWhere = strWhere & " (ISSUES.date) " = " " & Me.Date & "' AND"
End If

[pbaldy]

In the same way you surrounded the text values with ', the date value must be surrounded by #. You also have quotes around the =, which are wrong.

[j2curtis64]

I updated it as you suggested.....

If Not IsNull(Me.Date) Then
strWhere = strWhere & " (ISSUES.date) Like '# & Me.date & #' AND"
End If

But, it's still not working. I am still being prompted to enter date after I press the "Run" button. Then I do not get any records returned.

[pbaldy]

You didn't do it the same way you did the others. Try

strWhere = strWhere & " (ISSUES.date) = #" & Me.date & "# AND"

[j2curtis64]

I updated as suggested but it's still not working. All records contained in the table were returned.

If Not IsNull(Me.Date) Then
strWhere = strWhere & " (ISSUES.date) = #" & Me.Date & "# AND"
End If

[pbaldy]

That looks okay now. What's the full code? There are records with other dates than displayed on the form?

It may not be the problem here, but "Date" is not a good field name, as Access can confuse it with the Date() function.

[j2curtis64]

Here is the entire code....

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
Const conJetDate = "\#mm\/dd\/yyyy\#"
'Constant Select statement for the RowSource
strSQL = "SELECT issues.id, issues.Month,issues.ApprovingOfficial, issues.CardHolder, issues.RequisitionNumber, issues.TransactionNumber, issues.Category, issues.Status,issues.DateIssueOpened,issues.Close_ Date,issues.Last_Update_Date,issues.date " & _
"FROM issues"
strWhere = "WHERE"
strOrder = "ORDER BY issues.id;"

'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.CardHolder) Then '<--If the textbox txtcardholder contains no data THEN do nothing
strWhere = strWhere & " (ISSUES.cardholder) Like '*" & Me.CardHolder & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.ApprovingOfficial) Then
strWhere = strWhere & " (ISSUES.approvingofficial) Like '*" & Me.ApprovingOfficial & "*' AND"
End If
If Not IsNull(Me.RequisitionNumber) Then
strWhere = strWhere & " (ISSUES.RequisitionNumber) Like '*" & Me.RequisitionNumber & "*' AND"
End If
If Not IsNull(Me.TransactionNumber) Then
strWhere = strWhere & " (ISSUES.transactionnumber) Like '*" & Me.TransactionNumber & "*' AND"
End If
If Not IsNull(Me.Category) Then
strWhere = strWhere & " (ISSUES.category) Like '*" & Me.Category & "*' AND"
End If
If Not IsNull(Me.Status) Then
strWhere = strWhere & " (ISSUES.status) Like '*" & Me.Status & "*' AND"
End If
If Not IsNull(Me.Date) Then
strWhere = strWhere & " (ISSUES.date) = #" & Me.Date & "# AND"
End If
'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
'Pass the SQL to the RowSource of the listbox
Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder
End Sub


Private Sub lstCustInfo_DblClick(Cancel As Integer)
'Open frmCustomer based on the ID from lstCustInfo listbox
DoCmd.OpenForm "Issue Details", , , "[ID] = " & Me.lstCustInfo, , acDialog
End Sub
' CHANGED BOUND COLUMN FROM 3 to 1, 7/20/2011

Ok...I'll take a look at changing the name for "Date"

[pbaldy]

When you strip off 5 characters, you're stripping off the ending #. This might help you debug the string:

http://www.baldyweb.com/ImmediateWindow.htm

[j2curtis64]

So, i am new at this. So how do i debug this? do i copy all the code and place in SQL screen (in query)? I didnt think the SQL window will recognize comple code.

[pbaldy]

Was the link not clear? Use

Debug.Print strSQL & " " & strWhere & "" & strOrder

to see the final SQL.

[j2curtis64]

Sorry but not really. Again I am new at this. Where in the code so i place "Debug.Print strSQL & " " & strWhere & "" & strOrder". Then while in view i look for immediate window. When I run I am getting the following:

SELECT issues.id, issues.Month,issues.ApprovingOfficial, issues.CardHolder, issues.RequisitionNumber, issues.TransactionNumber, issues.Category, issues.Status,issues.DateIssueOpened,issues.Close_ Date,issues.Last_Update_Date FROM issues WHEREORDER BY issues.id;

This same code is returned 4 times.

Thank you for your assistance

[pbaldy]

Presuming you placed that right before using it, two things are obvious. There is nothing being returned by the WHERE clause, and you don't have a space between the WHERE and ORDER BY clauses.

[j2curtis64]

Solved. Thanks for your assistance

[pbaldy]

No problem.