simple (i think) query can't work it out (1 Viewer)

[jd_boss_hogg]

Hi All - i think this is a simple enough thing to do, but just can't work it out in my head..

I have some invoices in a dbase. I want to build a query where customer = widgets PLC, >Date = 1/1/11 < 1/2/11, and where item 1 OR item2 OR item 3 or Item 4 contains "nails"

It's the querying more than one field for the same item that i can't seem to get working... how do i do this ? If i put all the fields into the design query, it then only gives me results where 'nails' is in ALL fields, where i want an either/or kind of result... ?

hoping someone can help !

 

[Trevor G]

Add an expression to a new field that will add in the IIF([FieldName]="nails",1,[FieldName]) and repeat the IIF for the other fields in the expression then you can set the critieria for 1.

Without seeing the database, but from your thread this should work.

 

[plog]

You've got bigger problems than this immediate issue.

...where item 1 OR item2 OR item 3 or Item 4 contains

When you have numerated field names (i.e. item1, item2, item3, contact1, contact2, contact3, etc.) it means your database isn't properly structured. Instead of having a field for each item, you need to make a table of items with a row in that table for each item. In a broad view it would go from looking like this:

InvoiceTable
InvoiceID, InvoiceDate, CustomerID, Item1, Item2, Item3, Item4
1012, 6/28/2012, "Nails", "Hammer", "2 x 4", "Glue"

To this:

ItemTable
InvoiceID, Item
1012, "Nails"
1012, "Hammer"
1012, "2 x 4"
1012, "Glue"

This makes your immediate issue simpler to solve and will avoid other issues that you will be sure to have in the future.