Oh, the problem is with a sequence of question marks that return nothing? Have you tried the LIKE operator rather than the "=" operator? According to this article, a filter clause is like a WHERE clause, so the same syntax rules would apply. The rules for LIKE would condone an attempt to match a specific-length string with the question-mark symbol.
wouldn't work because the wrong "inner" quotes were used. But your later posts showed a correction to use the single quote that should have worked better syntactically. But I think now your problem is semantics.
I had just found that one out and got it all working, unfortunately I am now stuck at the final stage. I have placed a tick box on my main form and if it is ticked I want to refresh the subform with just data that has that field with 8 characters, for the form load I have:
Code:
Private Sub Form_Load()
Me.Filter = "[Blade] LIKE '????????'"
If Forms![frmMain]![chkFan] < 0 Then
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub
and then on the tick box I have:
Code:
Private Sub chkFAN_AfterUpdate()
Me.test.Requery
End Sub
To explain further, I am trying to include all data or just data with eight characters.
Code:
"AND Scans.Blade LIKE '*' &(Forms!frmMain!tBlade) & '*'" & _
"AND Scans.Blade LIKE '????????'" & _
This one doesn't work as the search box (tBlade) when empty shows everything and just the ones with eight characters. What I want is to show the ones with eight characters and then filter them by the search box.
My full list has:
ABC
DEF12345
ABC12345
If I put A in the search box I will get:
ABC
ABC12345
I want to tick the checkbox and reduce the list to:
DEF12345
ABC12345
and then put A in the search box and just get:
ABC12345
I have now placed a query at the front which selects all records or just those with eight characters:
I have then based my record source in exactly the same way as before but against the query, this slows things down slightly but it's not painfully slow.
I would still love to know a single stage method to achieve this
I can see one possible (not certain) reason for performance issues. And you said it does not work as intended.
The "not working as intended" part: Minty points out that if you are building the string and want to take account some things checked on the form, you build the string conditionally, either by having alternate contributions or NO contributions for each checkbox you have. So to make your code more efficient at the expense of one tedious part (building the string), consider not making the question so complex every time by using conditional contributions. Once the string is built correctly and loaded to the sub-form, you can do the requery as noted by ArnelGP.
More on "not working" and a comment on performance: Consider these similar but not identical constructs. Construct A is yours, lifted via cut/paste. The rest are modifications I have made, and watch out for them having mixed single and double quotes in places.
Code:
"AND Scans.Co LIKE (Forms!frmMain!cboMRO)" & _ 'construct A
"AND Scans.Co LIKE '" & Forms!frmMain!cboMRO) & "'" & _ 'construct B
"AND Scans.Co LIKE '*" & Forms!frmMain!cboMRO) & "*'" & _ 'construct C
"AND Scans.Co = '" & Forms!frmMain!cboMRO & "'" & _ 'construct D
Every time you have one of these, construct A forces the query engine to reach back into the Access environment to pick up the value from the combo box. I would like to think that the engine (Jet or Ace depending on version of Acess) optimizes this but unfortunately it is a black box and I don't know how many times it has to reach back. The SQL specification doesn't include statements about how often a reach back occurs during query implementation because implementation is a vendor issue. However, consider that you have more than one of these "reach back" cases in your presented query.
You can MAYBE speed up what you wrote as construct A by picking up the value and using it as though it were a constant - by switching to construct B. Then the only time you reach into the form is when you build the string. If you think about it, the combo box isn't going to change during the query so it might as well be converted to a constant in the string that was going to use the value.
But then there is the issue of the use of LIKE. Depending on what you wanted to do, you might consider the two different ideas expressed by constructs C and D.
Construct C is essentially "get everything that contains whatever is selected by the combo box as a formal substring." That usage of LIKE is essentially the classic "CONTAINS" operation. Variations of this leave off the leading or trailing asterisks to change "CONTAINS" to "BEGINS WITH" or "ENDS WITH" cases.
Given that construct A didn't use wild cards and I don't recall any particular combo box formatting property that would allow it, the only way that the LIKE statement would differ from an equals-sign in behavior would be if the actual table/list from which the combo box was selecting included the asterisk or question-mark in the selection source. So unless there are embedded wild card characters in the data presented from the combo box, you might as well have used construct D (strict equality). Because without those wild cards, LIKE and equals-sign are the same.
In terms of intent, using the equals-sign when you meant it that way makes it easier to read six months from now when you finally get it working and go on to something else, but then have to come back to this. And at that putative future date, you read that complex statement and ask yourself, "What the HELL was I trying to do when I wrote this?"
My database is only about 14Mb with all data being text and numbers.
I have a concept which is a continuous form containing numerous fields of data (11) in approximately 100,000 rows. The aim is to display all 100,000 rows in a continuous form and trim the data down by search criteria, dates, types etc..
Unfortunately the application needs to work like this as there are no obvious paths for manipulation so it needs to be a central form with all the possibilities available to you on the screen. To this end I have a form with all the controls and a sub-form with the data being re-queried every change.
A few years ago as a complete novice I got it to work perfectly using some very dodgy methods including make tables (clearing and then refilling), queries on queries etc. but as the data increases it has become very slow (every change taking about twenty seconds).
Now as a novice I am re-writing it and trying to speed it up.
