Query search in various fields!

[U812B4]

How do I write a query that will do a search across (7) fields after a parameter has been entered?

In my table I have (1) field that has WO Number and (7) with:
Field 2: Traveler 1
Field 3: Traveler 2
Field 4: Traveler 3 to Traveler 7

When trying to view the Traveler Report I would like the question: Enter Traveler, then the user would enter TR0700080 (Example) this in return would show the WO (Work Order) associated to it.

I am unsure how to write the expression in the criteria field so that it will search for the TR# in all (7) Traveler fields since it could be in any.

I hope I have explained this well enough. ~ Thank you

 

[maxmangion]

Hi,

I think you have a problem with your setup. Could you explain why do you have 7 traveller fields? perhaps a search on normalization would help reconsider your table structure

 

[N1ck9141]

try this

ive had a similar problem

1st) create a form(called search) with a text box and a button label the text box text0 (should do this by default ) save it

2nd) create a query called searchquery that shows all the fields of you table
now in the critia for all the fields (apart from your wo one) place this code
[Forms]![search]![Text0]
and save

3nd) return to your form named search and enter a button to run "searchquery" ...

to use type the tr number in the text box and click the botton

 

[U812B4]

I have (7) traveler fields because one work order may have multiple travelers assigned depending on how many steps it may need to get the work done. Sometimes we may have a traveler number and need to know what work order it belongs too.

I have done as you suggested and I am not getting any data . Will keep trying...thanks !

 

[maxmangion]

I have (7) traveler fields because one work order may have multiple travelers

That does not require you to have 7 fields. It break the rules of normalization and you will be having similar problems to the one you already have. Create a table with just two fields i.e. workID and TravellerID and your data should be stored as follows

WorkID1 Traveller1
WorkID1 Traveller2
WorkID1 Traveller3
WorkID2 Traveller1
etc

That one all of you travellers will be stored in one field and you will only have to place the criteria in one field.

 

[U812B4]

Would this not require me to enter the work order then as many times as I have travelers? The way I have the form setup now, the user enter the work order number, po number, etc and in the traveler fields the traveler numbers that will be assigned to that work order.

 

[maxmangion]

well you will only be entering the WorkID (not all the other fields related to the Work). Moreover, you can use a subform in your mainform and that way the WorkID will already be there for you.

 

[N1ck9141]

forgot the bottom bit

ive had a similar problem

1st) create a form(called search) with a text box and a button label the text box text0 (should do this by default ) save it

2nd) create a query called searchquery that shows all the fields of you table
now in the critia for all the fields (apart from your wo one) place this code
[Forms]![search]![Text0]
and save

3nd) return to your form named search and enter a button to run "searchquery" ...

to use type the tr number in the text box and click the botton

some i forgot is the u need to keep the "seaarchQuery" open in the back ground then return to it after u fill out yr form and click the buttom please try this it should auto update the query that way

 

[U812B4]

Success

After many hours of racking my brain to use this information I have finally found success! Not only did I attain what I was looking for but I learned as well.

 

[N1ck9141]

happy

glad it helped, i run a few tests myself and found some small flaws but nothing major glad this helped

 

[U812B4]

Success

glad it helped, i run a few tests myself and found some small flaws but nothing major glad this helped

I actually did what was suggested above and that was adding a subform instead. I tried your method and I am sorry but it did not work for me, I do appreciate the help thou!

Thank you!