Can't filter by date & combobox, the query comes up blank as soon as I add the combobox criteria

[Eddy Sincere]

My database manages controlled documents which are subdivided by responsible departments. I set up a query to filter the documents by their expiry date, i.e. if their review date is less than today's date they are expired. My problem comes when I try to add a combo box to filter by responsible department. As soon as I add it to the query criteria it returns no results.



I have multiple search forms/queries/reports set up in this fashion and they work fine. The problem seems to be when I use the combo box with the date. That's when I don't get any results. I'm thinking there's some problem with using dates and combo boxes.

Let me know if you want me to post the db. I'm at my wits end.

Query With Date Only



Query Results With Date Only




Query With Date & Combo Box Criteria



Form with Combo Box Data
(Column one is a record number and column 2 is Responsible Department, therefore I have the bound column set to "2". Of course, even if I set it to "1" I get the same results, i.e Nothing.)

[June7]

Is DRvw actually a Date/Time type field? Is it formatted to show only month/year but actually has a full date value (at least mmddyyyy)? Recommend avoid formatting fields in query. Do this in reports.

What are the combobox properties? It should be UNBOUND. Is Rspnsbl a text or number field?
RowSource
BoundColumn
ColumnCount
ColumnWidths

Do you have lookups set on fields in tables? http://access.mvps.org/access/lookupfields.htm

[Eddy Sincere]

Is DRvw actually a Date/Time type field? YES
Is it formatted to show only month/year but actually has a full date value (at least mmddyyyy)? YES, MM/YYYY is the format

I removed the format in the table and allowed it to just give me the date as mmddyyyy but it didn't fix anything. Still coming up with a blank query.

[June7]

Possibly you read my post before I edited. Might look at again.

[Eddy Sincere]

You're correct, I think I opened your response while you were still writing it.

I changed the date format and made sure the cb was unbound. Rspnsbl is a text field. The Rspnsbl table has a record number field (PK) and then a description field (text).

So with that in mind, I made sure the combobox is bound to column 2.

The row source is an SQL search string which directs it to the specific Rspnsbl field, not the entire table.

Then I closed the DB, reopened and now it's working. I'm not sure what I did that was different, but am thinking it was the date format thing.

Thanks for the checklist, it helps to have someone remind me of each think because its so easy to overlook one piece that throws whole thing into a spiral.

Lastly, no, I do not ever use lookups. You guys broke me of that nasty habit right from the start. Thanks!

[June7]

Format property setting on date should not have made a difference. Format function could make a difference because it results in a string value.

Must have been the combobox.

[Eddy Sincere]

I think you are correct, it is likely the combobox was pulling from the incorrect table. Such a simple mistake but, that always seems to be the case with me. Thanks for the patience.

Quick question - When using a date field, do you like to use the date/time auto format? Or, do you prefer to skip the auto format feature and just let people insert the date as a text field?

[June7]

I have textboxes bound to date/time field

and use InputMask

99/99/"20"00;0;

and ValidationRule

<=Date() Or Is Null