Forms - Field as criteria

Hi,

I am trying to build a form with a list box that runs a query. In the query I have several tables that return details in the query for one of the tables I want the field to change depedning on what the user selects in the list box on the form.

e.g
I have a table for countries and fields are different country names with a list of names in each country.

I can make a static query that returns details for the names specific to that country but by selecting the country name as the field in the query design. However I want the field selection to be based upon what the user selects on the form list box.

I'm new to access and dont know any VB.

Thanks in advance for your kind help.

Regards
Bal

In your query you need to refer to the value selected in the list box,.

for example,..

Take a look at this example which is similar to what you are after,.. but form based.

<<<<List Box Filter>>>>

Thanks very much for your help.

Do I add this line in the Criteria section or field name section in the Query Design view?

I couldn't open the example because my internet is restricted at work - will download at home and work through it. Thanks for this.

Regards
Bal

In the criterial line of the field you are filtering,...

in regards to your iif statement in the other post,.. that goes in the field line. P.s,.. it should look something like

If I predefine a field in the query and put this code in the criteria line will the field then change according to what is selected in the form or does this only qwork with values?


I tried the If statement in the field name section and got an error saying:"The expression you entered contains invalid syntax - You may have entered an operand without an operator"

I typed:

Iif(tblGeneralItems.[Enter SOE Wrap ID] = "*_as*", tblMy Prod.[Enter SOE ID], tblGeneral Items.[Enter SOE Wrap ID])

Thanks alot for your help, my Access skills are very poor.

Kind Regards
Bal

my fault,.. iif statement question answered in correct thread,.. we'll keep this thread for the list box

cheers,

With Regards to if statement I keep getting the enter value dialog box. I did remove the spaces in teh source table but still got the message so not sure why its coming up. Problem is they are linked tables to excel spreadsheets which are reports from another db so the spaces will come back even if I get it to work.

With the list box I put the code you provided in the criteria part:

Like "*" & [Forms]![frmReport]![Select Region] & "*"

I choose one of the fields (country/region name) Wave 5 in this case. Then save and go to the form "frmReport". I click the list box (row Source is a table called Regions & row type is a field list) this drops the list of countries. I select onw and nothing happens. I need it to run the query with the specifid parameters but change the field for the first column to which ever country is selected.

I think I need to take the time out and go through an Access text book.

regards,
Bal