Filtering a List Box using a date rage and combo selection.

[bakkouz]

Hi,
I am using this code to filter a List Box based on a Date range and a Combo Box selection:

Private Sub Combo139_AfterUpdate()
Dim StrgSQL As String
      StrgSQL = "SELECT  [User Name], [Date Of Request], [Description of Problem], Status, Sub_Job FROM QRY_SearchAll " & _
                "WHERE [Date of request] BETWEEN #" & CDate(Me.txtStartDate) & _
                "# AND #" & CDate(Me.txtEndDate) & "#;"
StrgSQL = StrgSQL & " WHERE Sub_Job = Combo139"
Me.SearchResults.RowSource = StrgSQL
Me.SearchResults.Requery
End Sub

However, It is not working. when I click the Combo box the List Box comes up blank. what am I doing wrong?

 

[bob fitz]

Untested, but maybe something like:

Private Sub Combo139_AfterUpdate()
Dim StrgSQL As String
      StrgSQL = "SELECT  [User Name], [Date Of Request], [Description of Problem], Status, Sub_Job FROM QRY_SearchAll " & _
                "WHERE [Date of request] BETWEEN #" & CDate(Me.txtStartDate) & _
                "# AND #" & CDate(Me.txtEndDate) & "# AND Sub_Job = " & Me.Combo139

Me.SearchResults.RowSource = StrgSQL
Me.SearchResults.Requery
End Sub

 

[bakkouz]

Bob,
It did not work, after I click on the combo box, it prompts me to enter a value corresponding to the same option I'm selecting from the combo box.

 

[bob fitz]

What type of field is "Sub_Job" and what type of value is held in the bound column of the combo box. If it is not numeric, you may need to enclose the value returned by "Me.Combo139" with single quotes:
AND Sub_Job = '" & Me.Combo139 & "'"

 

[bakkouz]

Hey Bob,
yes indeed Sub_Job is text not numeric, and it is technically working now, expect strangely it is now displaying incorrect results! ie: sometimes it displays results that are out of the date range, sometimes it doesn't display results that are within the date range.
What could be the cause of this?

 

[bob fitz]

Hey Bob,
yes indeed Sub_Job is text not numeric, and it is technically working now, expect strangely it is now displaying incorrect results! ie: sometimes it displays results that are out of the date range, sometimes it doesn't display results that are within the date range.
What could be the cause of this?

Not sure . What date format do you use in your part of the world. dd/mm/yyyy or mm/dd/yyyy.

 

[bakkouz]

its dd/mm/yyyy. and I've set the date format for the [Date Of Request] field and the date range text boxes accordingly. would you like to me attach a sample?

 

[bob fitz]

I think your problem is caused by the fact that your dates are dd/mm/yyyy but access expects dates to be mm/dd/yyyy.
See: http://allenbrowne.com/ser-36.html
Post back if you're still stuck and I'll help if I can.

 

[bakkouz]

Bob, You are right, that was it, Thanks alot.
although i suspect this will cause some confusion for the users. I'll see what I can do about it.
Thanks again.

 

[bob fitz]

Bob, You are right, that was it, Thanks alot.
although i suspect this will cause some confusion for the users. I'll see what I can do about it.
Thanks again.

Why should it confuse the users.

 

[bakkouz]

well because when entering the date they are accustomed to dd/mm/yyyy and the form only seems to work with the mm/dd/yyyy format so until they get used to it I suspect they'll be some confusion

 

[bob fitz]

well because when entering the date they are accustomed to dd/mm/yyyy and the form only seems to work with the mm/dd/yyyy format so until they get used to it I suspect they'll be some confusion

Let them continue to enter dates in the format that they expect to use (dd/mm/yyyy) and adjust your code to handle it. Perhaps something like:

Combo139_AfterUpdate()
Dim StrgSQL As String
      StrgSQL = "SELECT  [User Name], [Date Of Request], [Description of Problem], Status, Sub_Job FROM QRY_SearchAll " & _
                "WHERE [Date of request] BETWEEN #" & CDate(Format(Me.txtStartDate,"mm\/dd\/yyyy")) & _
                "# AND #" & CDate(Format(Me.txtEndDate,"mm\/dd\/yyyy")) & "# AND Sub_Job = " & Me.Combo139

Me.SearchResults.RowSource = StrgSQL
Me.SearchResults.Requery
End Sub

 

[bakkouz]

This worked like a charm! You are fantastic! thanks a ton!

 

[bob fitz]

This worked like a charm! You are fantastic! thanks a ton!

You're welcome. Glad to help