Filter Fields in a report using a talbe field value as criteria

[superxela]

I have a query that brings data from a couple of tables. When I manualy enter the criteria as
LIke "*0530*"
it shows the records that I want in the query. However I would like the value 0530 to be taken from a field on a small table. So that when I change the value on the table field, it should change the criteria on the query, and as a result update the reports base on that query.
I tried using
Like "*[FieldName}*"
But it did not work. It returns not records.

The botton line is that I want a report in which I can chage the value of the Criteria (*0530*) for another value (like 0515)and get the records that I want.

I'm in a time frame here, so fast and explicit instructions on how best to do this are greatly appreciated.

Thank You,
Superxela

 

[Steve101]

Superxela,

I'll assume that you have a form open at the time the query is invoked, and that the Form is called YourForm and the bound control on the form is called YourValue.

The query to use the field in the form might look something like this:

SELECT *
FROM YourTable
WHERE SomeField LIKE Forms!YourForm!YourValue

This example will use the value from the field in the form, to search a field called SomeField in the table called YourTable.

Note that in this example, you would need to include the * wildcards in the YourValue field on the form (eg. *0513*). this in fact gives you added flexibility, as you can exclude the wildcard symbols if you wanted to find an exact match.

Additionally, it doesnt matter if the SQL is in fact searching multi tables; providing the WHERE clause is coded appropriately, the mechanism should work.

A typical way to use this sort of technique is to bind the form to an underlying table which allows you to then navigate through the different records, and when you have the criteria you want, you click a button on the form which then applies the query (which for example might be bound to a report which the button press invokes).

Hope that this helps,
Cheers,
Steve