Field in table will not filter

[aliendan]

Weirdest thing. I have a table with 12 fields. It is populated by a chosen date span from a form. If I chose to show all dates for the month half of my columns won't filter. That capability just goes away. BUT if I just chose two weeks of data, any two weeks, the filter option is there. WEIRD! Any idea why the filter capability comes and goes with just a few columns? I thank you in advance.

Dan

Dan Rogotzke
Daniel_Rogotzke@oxy.com

 

[trevil620]

Do you mean "It is populated by a chosen date span" or do you mean "It is FILTERED by a chosen date span"? Does the date fields in the table include TIME as well as date?

 

[aliendan]

The user inputs a start date and an end date in text fields in a form. Time(hh:mm:ss) does not apply. These dates are picked up by VBA and used in the SQL for an append query that populates the table with all the data from the start date to the end date. One of the fields in the table that looses the filter option, for instance, is a persons last name. This problem just started a couple of weeks ago for no reason that I can find. Visual inspection of the data reveals no data corruption.

Dan Rogotzke
Daniel_Rogotzke@oxy.com

 

[trevil620]

1. Post the 'finished' SQL that is used to do the append.
2. Run some simple queries against the table and field that are filtered to check for 'Null' fields then run again and check for Empty fields (i.e. = "")
3. You said "One of the fields in the table that looses the filter option" ... check that field first.

 

[aliendan]

Yes, I checked all the data for Null values and Empty fields. There are none. FYI, the filters I am talking about are in the column headers of the table. To refresh your memory, this is a quote from my first post with a little bit added at the end: "If I chose to show all dates for the month, half of my columns won't filter. That capability just goes away. BUT if I just chose two weeks of data, ANY two weeks, the filter option is there in all columns."

Here is the SQL:

INSERT INTO tblPermianCodesDetailReportWthDates ( ORG_SEQNO, DOWNTIME_DATE, AUTOMATION_NAME, DOWNTIME_CD, DOWNTIME_DESC, Type, [Permian Category], HOURS_DOWN, LOST_OIL, LOST_GAS )
SELECT ODS_WELLCOMP.ORG_SEQNO, ODS_DOWNTIME_DETAILS.DOWNTIME_DATE, ODS_WELLCOMP.AUTOMATION_NAME, ODS_DOWNTIME_DETAILS.DOWNTIME_CD, ODS_DOWNTIME_CODES.DOWNTIME_DESC, tblCodeType.Type, ODS_DOWNTIME_CODES.CATEGORY, Sum(ODS_DOWNTIME_DETAILS.HOURS_DOWN) AS HOURS_DOWN, Sum(ODS_DOWNTIME_DETAILS.LOST_OIL_VOL) AS LOST_OIL, Sum(ODS_DOWNTIME_DETAILS.LOST_GAS_VOL) AS LOST_GAS
FROM ((ODS_DOWNTIME_DETAILS INNER JOIN ODS_WELLCOMP ON ODS_DOWNTIME_DETAILS.API_NO14 = ODS_WELLCOMP.API_NO14) INNER JOIN ODS_DOWNTIME_CODES ON ODS_DOWNTIME_DETAILS.DOWNTIME_CD = ODS_DOWNTIME_CODES.DOWNTIME_CD) INNER JOIN tblCodeType ON ODS_DOWNTIME_CODES.DOWNTIME_CD = tblCodeType.Code
WHERE (((ODS_DOWNTIME_DETAILS.DOWNTIME_DATE) Between #1/1/2014# And #1/31/2014#) AND ((ODS_DOWNTIME_DETAILS.DOWNTIME_CD) Not Like 'Z*') AND ((ODS_DOWNTIME_DETAILS.API_NO14) Like '30025233090000'))
GROUP BY ODS_WELLCOMP.ORG_SEQNO, ODS_DOWNTIME_DETAILS.DOWNTIME_DATE, ODS_WELLCOMP.AUTOMATION_NAME, ODS_DOWNTIME_DETAILS.DOWNTIME_CD, ODS_DOWNTIME_CODES.DOWNTIME_DESC, tblCodeType.Type, ODS_DOWNTIME_CODES.CATEGORY
ORDER BY ODS_WELLCOMP.AUTOMATION_NAME, ODS_DOWNTIME_CODES.CATEGORY;


Dan Rogotzke
Daniel_Rogotzke@oxy.com

 

[trevil620]

Hi Dan, now I can say I am even more confused When you first posted, you mentioned a table, a form and filters not working. So I assumed you had some rowsource for a form based on whatever filters a user selected from controls (i.e. drop-downs) on a form. But now when you say "FYI, the filters I am talking about are in the column headers of the table", that to me means you are in "table view" and have used "Advanced - Filter By Form"? Is that correct?
Now, looking at the sql, since I don't have the structure or data to play with, I don't see anything leap out as bad.
But, just to confirm, your SQL: "ODS_DOWNTIME_DETAILS.API_NO14 Like '30025233090000'" should that either have the wildcard (*) at front or back of value, or should it be "=" instead of 'Like"?
Another possibility that may aid in finding the culprit is to break this sql into multiple queries. For example, make "Query1" select just the fields you want and apply some of the simple filters. Then create "Query2" based off of "Query1" and do some grouping. Finally, "Query3" could be based off "Query2" and do the "Insert". You should be able to find the point where it breaks.
Wishing you good luck, and please let me know if I can check something else.
Wayne

 

[dhookom]

I would never rely on using filtering "in the column headers of the table" for anything other than viewing data. I would use another method for filtering for action queries. Actually, I never expect users to use column headers for anything. I guess I take the "old school" approach modifying the SQL or using references to controls in the WHERE clause.

Duane
Hook'D on Access
MS Access MVP

 

[aliendan]

This just populates a table. One of my supervisors looks at the data by opening the table and uses the column headers in the table to filter the data for his viewing purposes. He’s the one that brought this problem to my attention. He’s been doing it this way since I built this reporting front end for him a couple of years ago. This problem just arose about two weeks ago. I’m just trying to figure out why Access decided not to filter this way as it has been, since this is the way he likes to do it. As for the number needing a wildcard (*), no, this designates a particular location and “=” or “Like” will give the same results.

Dan Rogotzke
Daniel_Rogotzke@oxy.com

 

[dhookom]

An additional complexity to your filtering is the query is a totals/group by query. Any filtering of the behind the scenes query would then need to be added as a HAVING clause vs a WHERE clause. You mentioned 12 fields but your SQL has only 10. Is the 12 just an estimate?

Duane
Hook'D on Access
MS Access MVP

 

[aliendan]

I'm sorry guys, I don't want to sound rash but this has nothing to do with the query. It gets the data to the table just fine. The problem is with the table and the column header filtering that my supervisor likes to use. Again, the problem is that if the table is populated with one week of data or two weeks of data or three...any one week or any two weeks or any three weeks of the month, the column header filtering works just fine. It's just that when the entire month of data in there some of the columns quit filtering. That's all. All the fields have data in all circumstances and the data is good because it works in all combinations of data except with the entire month's data. Please, don't make this more difficult that it is. Again, I'm sorry if I come off disgruntled...nothing personal, believe me.

Dan Rogotzke
Daniel_Rogotzke@oxy.com

 

[dhookom]

I just tried this in tableview in Access 2010 and didn't experience any issues.
What version of Access?
You state some columns/fields are filterable and others aren't. Are any of these lookup fields or memo or yes/no or numeric?


Duane
Hook'D on Access
MS Access MVP

 

[aliendan]

They use Access 2007. No fields are memo or look up. Some are numeric and some are text. Problem lies with a couple of text fields as well as a couple of numeric. Like I said, makes no sense to me. With any span of consecutive dates within the month all the fields will filter but not with all the consecutive dates of the month of January, from the 1st through the 31st. Hmmmmm.

Dan Rogotzke
Daniel_Rogotzke@oxy.com

 

[aliendan]

Maybe it's just an anomaly with Microsoft or a recent fluke in the network. It all worked fine for him until about a week or two ago.

Dan Rogotzke
Daniel_Rogotzke@oxy.com

 

[trevil620]

Dan, we're all fine with trying to help solve this issue, but to save you and ourselves from wasting effort, we just needed to define the exact issue.
At first I was undure if the boss was using a form, or viewing a table, thus the reason for my first question. Later, when you posted the 'Insert' query, I started thinking in a different direction. So please bear with me as I try to paraphrase what I percieve to be the issue and you can correct my wrong assumptions.
1. Data is populated into a table; can be any number of months;
2. When you say "the table and the column header filtering that my supervisor likes to use", I believe he hits the 'drop-down' beside the date column header and selects the "Date Filters".
3. Next he selects the 'between' option and selects a start / end date (plus other filters). BOOM - he doesn't get what he wants
4. Does the date field have 'hours / minutes' in the data? If so, that can lead to the problem.
I have a table with 'EnteredDate' set to 'Now()' when the record is added; thus includes time. I can't filter it properly using the 'table filter'.
I just added field 'MyDate' and populated with JUST the mm/dd/yyyy portion of 'EnteredDate' and the filter works perfectly.
If none of this makes sense or works in your case, you may want to consider creating a simple form that allows you to beter control the SQL filtering.
Hope something helps!

 

[dhookom]

One issue I just noticed in the Northwind sample database looking at the Orders table. The Employee column is a lookup field that displays the lastname & firstname in the field's lookup properties. This somehow disables the multiselect in the filtering of the column in datasheet view. If I change the lookup field to display only the lastname field, I can then do the multi-select. I'm not sure if this makes any difference in your situation since you suggest you aren't using lookup fields.

Duane
Hook'D on Access
MS Access MVP

 

[aliendan]

OMG! I just figured it out. Access table column header filters only allow you to filter up to 1,000 different record choices (names in this instance). If there are more than 1,000 (maybe it is 999) different choices (names in this instance) in that column it won't allow the filter option. Thank you all for being so patient with me and again I'm sorry if got at all testy at times. I was just flustered over the problem. Having other ears and minds helped me to figure out different options to test and once again WE found the solution. Thank you all so much.

Dan

Dan Rogotzke
Daniel_Rogotzke@oxy.com

 

[trevil620]

Dan, Great Catch! Curious how you figured this out? I did a quick search and see where SQL Server places a limit on query results to prevent too many locks, but didn't see similar for Access.
But I did find in Access (2010) there is a default setting of 1,000. Then found this explanation:
"Don't display lists where more than this number of records read _____"
Enter the maximum number of records that you want Access to read while it builds the value list for Filter by Form operations. A value list will not appear if the number of records required to complete a list exceeds the number you specify. All values lists will contain only unique values, even if their fields are not indexed. The default is 1000. "
Since it is just before midnight, I can check off "Learned something new today!"
Thanks for letting us know what happened!!
Wayne

 

[aliendan]

Wayne--while I was sitting here I decided to double check…no triple check…quadruply check my data for any anomalies. Using the month of January, any two consecutive weeks of data would filter but not three or four consecutive weeks. Since I was concerned with one column in particular, the AUTOMATION_NAME column, I zeroed in on the data in that column. That data is well names and we have several thousand of them. There are several records for each well involved. First I ran the data for two weeks, then ran a query on that column and grouped it to see each well name once and check for data corruption. Then I did the same for three weeks of data to compare for anything that would ring a bell. The only difference, I noticed, was the number of wells involved. With two weeks there were 878 wells and with three weeks there were 1015 wells. The moment I noticed this there was an ethereal slap to the back of my head and the number 1000 over took my mind. I’m aware of limitations in Access so I ran the data and query many times, starting at two weeks, adding one more day each time and looking at the well count in the query. When I got to 17 days of data there were 987 wells involved and the data still filtered, but when I got to 18 days of data there were 1007 wells and the data would not filter. VOILA! Sure put a smile on MY face.

Dan

Dan Rogotzke
Daniel_Rogotzke@oxy.com