query LIKE filter does not work from a form field

revlisj

Registered User.
Local time
Today, 17:04
Joined
Jun 11, 2013
Messages
29
I’m on a role. Another weird occurrence in Access. I’m attempting to use a form field as the source for a query filter criteria. Everything works fine if I simply use an “if equal” filter condition. As soon as I try a “like” condition, nothing works.

I created a test table with just one column (fld1). The table contains three records with the following values: BRDODS, BRD, TLAODS.

The following “hard coded” query returns two records, as it should.

SELECT Table1.fld1
FROM Table1
WHERE (((Table1.fld1) Like 'BRD*'));

I also created a test form (Form1) with just one text field (Text0). My intent is to soft code a criteria value via the form field instead of hard coding the query, as above. When I populate the form field with BRDODS, the following “soft coded” query returns one record, as it should.

SELECT Table1.fld1
FROM Table1
WHERE (((Table1.fld1)=[Forms]![Form1]![Text0]));

When I enter LIKE ‘BRD*’ in the form field, no records are returned. I should get two records, just like the hard coded query above.

I’ve tried all variations of the LIKE statement in the form field, but nothing works.

Help!
 
You've got to hard code the asterisk into the query:

Code:
SELECT Table1.fld1
FROM Table1
WHERE Table1.fld1 Like "*" & [Forms]![Form1]![Text0] & "*";

Otherwise it doesn't use the * as a wildcard but as a character to look for.

Looks like I beat CJ, but his answer is better. Use his.
 
Last edited:
WHERE (((Table1.fld1)=[Forms]![Form1]![Text0]));

should be

WHERE (((Table1.fld1) LIKE [Forms]![Form1]![Text0]));

and when you complete the text0 field in your form, just put
BRD*
 
Thanks All. I think CJ's answer will work for a single LIKE statement. My larger goal is to build a complex query filter statement using a form field. For example, ...WHERE fld1 LIKE BRD* OR fld1 LIKE TLA*

I realize my test table only has three records, but the real table will contain thousands of records. I want to filter on a given field using multiple LIKE conditions.

Am I asking for the impossible? If so, is there an alternative way to do this in VBA?

Cheers
 
Yours is a very common requirement and there are thousands of thread on this and other forums discussing the subject

you can have multiple likes as you have indicated - following my suggestion for construction. The two things you need to handle are a) when the user has not completed one or more of the search fields on your form and b) you are dealing with different data types - typically string, numeric and date. There are ways to get round this however - take a look at other threads
 
Thanks CJ. I may be wrong, but I think what I originally requested cannot be done. That is, populate a form field with "complex" filter statement {e.g., where (table1.fld1 like ABC* or table1.fld1 like MNO*) and table1.fld1 not like *XYZ*} and then have the value of the form field be successfully interpreted by the query. There seems a disconnect between the form and the query. I found a workaround. I’m building complex SQL statements in VBA (with soft coded filter criteria) then using the SQL statements in the Docmd.RunSQL function. The RunSQL builds a work table. I then use the work table to produce the proper reports. I may be using a sledgehammer to get the task done, but it seems to work.
 
I then use the work table to produce the proper reports

Do you actually mean an Access Report, or are you using 'report' generically to mean a set of data? If you actually produce an Access Report, then you should look into DoCmd.OpenReport (http://msdn.microsoft.com/en-us/library/office/ff192676(v=office.15).aspx). One of the arguments allows you to build criteria to pass to the report. So instead of getting a query/table to have the criteria, you send it to the report.
 
I meant "report" in the generic sense. In essence, I'm getting the datasets I need.

Thanks
 
That is, populate a form field with "complex" filter statement {e.g., where (table1.fld1 like ABC* or table1.fld1 like MNO*) and table1.fld1 not like *XYZ*}
No, that can only be done by building the entire sql string in vba
 

Users who are viewing this thread

Back
Top Bottom