Access not filtering on Combo list

**RunTime91** · 09-13-2017, 09:56 AM

Greetings ~

I'm using a form with ComboBoxes where users will select a value from the ComboBox which the database will then filter on that value to populate a ListBox.

I have 9 of these ComboBoxes and 7 of the 9 work perfectly... The other two have begin shift times and end shift times stored in them and those will only filter correctly on one value, 12:00 AM and on a few others it will return only a partial of the total records while on most of them the filter will return nothing at all...

Below is the code used to populate the ComboBoxes

Code:

SELECT DISTINCT Sheet1.[Shift Begin] FROM Sheet1 ORDER BY Sheet1.[Shift Begin];

Below is the code used to trigger the filter event when a value is selected (or typed) into the ComboBox

Code:

Private Sub Combo72_AfterUpdate()

'Filter on Shift Begin Time

Me.List37.RowSource = "SELECT Sheet1.[Emp ID], Sheet1.[First Name], " & _
"Sheet1.[Last Name], Sheet1.FullName, Sheet1.[Agent Email Address], " & _
"Sheet1.Supervisor, Sheet1.Manager, Sheet1.Company, Sheet1.Location, " & _
"Sheet1.[Shift Begin], Sheet1.[Shift End], Sheet1.[Contact #], Sheet1.[Shift Code] " & _
"FROM Sheet1 WHERE (((Sheet1.[Shift Begin])=[Forms]![Sheet1]![Combo72])) ORDER BY Sheet1.FullName; "
'Me.Combo72 = ""

End Sub

Any help with this mystery would be greatly appreciated ~

**aytee111** · 09-13-2017, 10:19 AM

Time is very finicky. Create a new query to test what your filter should look like, adding in the criteria line the same as you enter in the combobox. Then you can see what format it should be.

**RunTime91** · 09-13-2017, 11:19 AM

Hey Aytee ~

Gave it a shot...no luck. I can pull all the 12:00 AM records but nothing else...

**aytee111** · 09-13-2017, 11:25 AM

Show us what the data on the record(s) looks like, and what the data type is.

Add a line of code:
Debug.Print Me.List37.RowSource
and see what it is displaying. You can use this SQL string to copy and paste into a query.

Also add:
Me.Lsit37.Requery

**RunTime91** · 09-13-2017, 12:37 PM

Hey Aytee...
Okay...
Debug just showed the string above
Requery had no affect
DataType = Date/Time

Here is a sample of what the times in the Shift Begin field look like:

Shift Begin

7:00:00 AM

8:30:00 AM

4:00:00 PM

4:00:00 AM

**aytee111** · 09-13-2017, 12:58 PM

So, you enter "7:00:00 AM", go to the table, find all the records that display that. The query you created in post #3 should match this. Or from the other side, go to the table and find a time, then put that into your query as criteria and see if it is showing correctly.

It should come out something like
WHERE (((Sheet1.[Shift Begin])=#7:00:00 AM#))

**RunTime91** · 09-13-2017, 02:57 PM

Hello Again ~

Yes... The code, when entering the actual value looks just like above...However, the results are the same...The only time it returns a an accurate filter result is when I filter 12:00 AM

Even when I perform a manual filter action in the table itself it will only work on 12:00 AM...which, imo is just insane...

Why would Access recognize the values in the manual filter dropdown or load the different times into the ComboBox then pretend those values do not exist when attempting to filter on them either directly in the table or with VBA

I get it that Microsoft is horrible with times (and dates ftm) but this is just crazy...

**aytee111** · 09-13-2017, 03:01 PM

You're right, this isn't making sense. Can you post your database, with some sample data?

**RunTime91** · 09-13-2017, 03:46 PM

Hey Aytee... I'm trying to send a PM to you with the db attached... but there seems to be no way to attach the file ?? Any suggestions?

**aytee111** · 09-13-2017, 05:36 PM

To attach:
- click on Go Advanced
- look for the attach icon, a new window will open for you to upload a file

**RunTime91** · 09-14-2017, 10:22 AM

Well so much for that... Unfortunately the Admin's on this site have limited the size of uploads to 500 Kb... (which seems rather small for Access)
I have gutted my db down to 1 form and 1 table consisting of 50 records and I'm still at 532 Kb...
I would have to eliminate the entire table to get even close to 500...

Can't thank you enough for sticking with me on this one Aytee, but it looks like I'm on my own

**aytee111** · 09-14-2017, 10:31 AM

Zip it first.

**RunTime91** · 09-14-2017, 01:05 PM

Here you go ~

**aytee111** · 09-14-2017, 01:32 PM

It is your data. I added a new record and everything worked fine. What Access is displaying is not what is actually in the shift begin field. Where does this data come from?