Query filtering in Access 2007 not the same as 2003?

[Greg]

I have several applications that were written in Access 2003 that do not work now on machines that have Access 2007. The culprit is queries that have filters which point back to controls on a form. If I have two controls on a form and one is used to filter the query on the other, the filter always returns zero records.

Here is an example of one of the queries:

SELECT [house number] & " " & [unit] AS Composite, [NTAS Addresses].[ACCT STATUS], [NTAS Addresses].[HOUSE NUMBER], [NTAS Addresses].STREET
FROM [NTAS Addresses]
WHERE ((([NTAS Addresses].[HOUSE NUMBER])=[forms]![frmAddressPick]![lstHouseNumbers])
AND (([NTAS Addresses].STREET)=[forms]![frmAddressPick]![lstStreets]))
ORDER BY [house number] & " " & [unit];

Is there a new way to use a filter expression in 2007 that also still works in 2003?

 

[Allen Browne]

There was a change of behavior in A2007.

Try opening the form in design view, and setting its FilterOnLoad property
to No. That solves some of these cases.

If that doesn't solve it, is this form named frmAddressPick? If so, there is
a chicken'n'egg situation here, where the query needs to read the value from
the text box on the form to fetch the records, but the form need the query
data to show in the text box. This is the kind of thing that different
versions can handle differently.

If that's the issue, the solution might be to remove the criteria from the
query, and use the form's Filter instead. You could use the AfterUpdate
event of the 2 text boxes to apply the filter, or add a command button if
you prefer. IMHO, this is a much better technique: more efficient, more
flexible, and more scalable.

For an example of filtering a form dynamically with multiple text boxes and
different data types, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Download the sample database, and pull it apart. You will find the technique
useful for filtering reports as well as forms.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have several applications that were written in Access 2003 that do not
work now on machines that have Access 2007. The culprit is queries that
have filters which point back to controls on a form. If I have two controls
on a form and one is used to filter the query on the other, the filter
always returns zero records.

Here is an example of one of the queries:

SELECT [house number] & " " & [unit] AS Composite,
[NTAS Addresses].[ACCT STATUS],
[NTAS Addresses].[HOUSE NUMBER],
[NTAS Addresses].STREET
FROM [NTAS Addresses]
WHERE ((([NTAS Addresses].[HOUSE NUMBER])
=[forms]![frmAddressPick]![lstHouseNumbers])
AND (([NTAS Addresses].STREET)
=[forms]![frmAddressPick]![lstStreets]))
ORDER BY [house number] & " " & [unit];

Is there a new way to use a filter expression in 2007 that also still works
in 2003?