search multiple fields

[capn beanfart]

Is there a way of doing a parameter query that will search more than one field at a time? At the moment I can search one at a time using this method, but for what I'm trying to do this is too time consuming.

There MUST be a way!!!

 

[mderby]

use a form

The best way is create a form with the all the fields you want to search.

Then in the query in the where clause reference this field.

sample

frmOne: Item1 Item2

then in the query

where [TableName].[Field1] = [Forms]![frmOne]![Item1] and
[TableName].[Field2] = [Forms]![frmOne]![Item2]

this way you can have multiple search fields.

Hope this help.

 

[capn beanfart]

Thanks for replying so quickly.

Typically for my place of work, the computers are down so I can't try out your suggestion yet, but it sounds good.

I'm creating a database so I can log the details of equipment hired out and by whom from our friendly photography department.

I shall try your suggestion as soon as the computers are working again, but as I'm a beginner I may need some more advice if thats ok?!

Cheers!

Phil

 

[capn beanfart]

I tried your suggestion which brought up separate parameter query windows, one after another meaning you have to type out the search repeatedly.

This is better than before but what i'm looking for is to bring up one parameter query window, type in the query once and this will search through all six fields.

On my form I have six fields that I want to search:

ITEM1 to ITEM6.

These are for entering items of kit that students hire out (they can hire out up to 6) and have drop down menus containing our inventory to speed up the process.

The Table is called 'EQUIPMENT' and the form is called 'EQUIPMENT2'.

I tried this in the SQL view, and it almost seems like it's going to work but it brings up results that don't fit into the parameters..

WHERE ((([EQUIPMENT].[ITEM1].EQUIPMENT.ITEM2.EQUIPMENT.ITEM3.EQUIPMENT.ITEM4.EQUIPMENT.ITEM5.EQUIPMENT.ITEM6)=[SEARCH FOR ITEM]));

Does this make any sense or is it utter jibberish?!

PS I've been on holiday which is why I haven't replied for so long!

 

[capn beanfart]

I tried your suggestion which brought up separate parameter query windows, one after another meaning you have to type out the search repeatedly.

This is better than before but what i'm looking for is to bring up one parameter query window, type in the query once and this will search through all six fields.

On my form I have six fields that I want to search:

ITEM1 to ITEM6.

These are for entering items of kit that students hire out (they can hire out up to 6) and have drop down menus containing our inventory to speed up the process.

The Table is called 'EQUIPMENT' and the form is called 'EQUIPMENT2'.

I tried this in the SQL view, and it almost seems like it's going to work but it brings up results that don't fit into the parameters..

WHERE ((([EQUIPMENT].[ITEM1].EQUIPMENT.ITEM2.EQUIPMENT.ITEM3.EQUIPMENT.ITEM4.EQUIPMENT.ITEM5.EQUIPMENT.ITEM6)=[SEARCH FOR ITEM]));

Does this make any sense or is it utter jibberish?!

PS I've been on holiday which is why I haven't replied for so long!

 

[mderby]

Same Item

hi,

At first I thought that it is involving 6 different fields which is why I suggest that query path.

Now I understand that you just want to select 6 records from the same field. I created a sample solution and attached with this reply. I think using the list box for multi-selection is a very good way to handle your requirement. Some of the codes in the frmSearch forms are from someone who posted in this forum last time but unfortunately I forgotten his name. Special thanks to him.

See whether you can make use of this db.

mderby

 

[capn beanfart]

You were right the first time mderby!

I'm looking for a single parameter search that will search through six fields. Sorry if i've not explained myself very well...

For example: If i'm looking for a specific camera, eg 'Pentax' I want to be able to enter this as a parameter value and for it to come up with a list of students that have hired out pentax cameras

 

[capn beanfart]

Oops that posted before i'd finished typing! Not my day...

STUDENT ITEM1 - ITEM2 - ITEM3 - ITEM4
Jo Bloggs - Pentax - l. meter - lens - ext. tube
fred smith - hotshoe - m. lens - pentax - tripod
g. jones - c. release - tripod - polaroid - l. meter

Bad example of my table here but imagine alot of student entries etc.

From this you can see that to find the people with 'pentax' cameras I need to search through more than one field at a time to find that 'jo bloggs' and 'fred smith' have got them.

Your first suggestion was good because it brought up the parameter search windows one after another (search item1, item2, item3 etc), but i'm still looking to make the search just once.

Hope this makes more sense!

Cheers

 

[MattS]

So long as the Parameter your searching for in each field is called the same thing, Access will only ask you for it once, i.e. [Enter Seacrh Criteria]

In the query window enter the above on one line for field 1, then on the next line for field 2, the next line for field 3, etc..

The SQL should look something like:

WHERE (([Equipment].[Item1] = [Enter Search Criteria]) OR ([Equipment].[Item2] = [Enter Search Criteria]) OR ([Equipment].[Item3] = [Enter Search Criteria]))

If you want to be able use the wildcard to search on partial criteria (i.e. Pent*), change the = to say Like

HTH,

Matt.

 

[capn beanfart]

Hi there MatS

That worked perfectly, thank you very much!

I've now got a command button on my form that starts up the query, so it's looking good!


Thanks both to you and mderby for taking the time to sort my troubles out!!

Cheers

Phil.