Filter query based on table containing begins with

[ppataki]

Dear All,

I have a query that has multiple tables
One field of the query is the product description

I would like to have a table that filters on product description but not like including or excluding values but based on begins with text

Example:
I have a table that contains one field that is used for product description begins with
eg. product description field in query = SFCO 212424; SFCO 65656 etc etc
product description begins with in table = SFCO

so if I run the query it shows me only those products the name description of which begin with SFCO (plus anything else that I enter in the "begins with" table)

Many thanks in advance

 

[MStef]

In qury criteria put;
Like "SFCO" & "*"

 

[CBrighton]

Yup, Like will do the job.

The * signifies any number of any other characters. So one at the beginning and one at the end would search for anything including the criteria, but * at the end without one at the beginning will search for records which start with the criteria you specify. Equally * at the beginning but not at the end will search for records ending with the criteria.

 

[ppataki]

SFCO is written in a field called Desc in a table
and I need to use this table to filter a query where product descriptions start with SFCO
I tried putting like "[desc]*" but did not work

 

[MStef]

Like "SFCO" & "*"

 

[CBrighton]

Why are you using a table rather than entering the criteria directly into the query or referencing a control on a search form as the criteria (Like [forms]![FormName]![ControlName] & "*")?

 

[ppataki]

I would like to use a table as there will be more criteria as well (so not only I would specify eg SFCO but also SMPF, DSEFG, etc etc)

 

[CBrighton]

How will the user be able to choose which prefix they want if it is in another table?

Would a combobox containing all the prefixes and a command button to open the query / a form based on the query not be more accessable for the end user?

The problem is likely to be that Access doesn't know what record you are looking at in the table with the desc field. Do they have a relationship at all or are they stand-alone?

If this table has records like SFCO, SMPF, DSEFG, etc how is the query to know which prefix it should be using as the criteria?

 

[ppataki]

the table containing the prefixes will be on a form as a subform, so users could freely enter any prefixes they want (from a combobox)
currently the desc field is joined with the description field as left join

 

[CBrighton]

So currently the user enters the selection in a combo box which is then put into a table and used to filter the query.

Why not remove the table and filter on the combobox, or if you require multiple selections a listbox?

Obviously you know more about the database than me so I could be completely wrong but from what you have posted it seems to me like you are overcomplicating it.

 

[ppataki]

OK, that sounds great, but how do you input the results of a multiple selection listbox to the criteria of a query?

 

[CBrighton]

Take a look at this:

http://www.fontstuff.com/access/acctut18.htm