Search in many fields at same time.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I need to find an invoice number sometimes and decided to create this query:
----------------------------------------------------------------------------
SELECT Orders.OrderID, Orders.InvVWD, Orders.InvNorthern, Orders.InvPremium,
Orders.InvYork, Orders.InvVinylbilt, Orders.InvExtras, Orders.InvInstaller,
Orders.InvCutOut, Orders.InvLumber, Orders.InvSuntown, Orders.InvOther,
Orders.InvSalesperson
FROM Orders
WHERE (((Orders.InvVWD) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvNorthern) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvPremium) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvYork) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvVinylbilt) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvExtras) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvInstaller) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvCutOut) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvLumber) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvSuntown) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvOther) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvSalesperson) Like "*" & [Enter Parameter] & "*"));
-------------------------------------------------------------------------
Obviously I am doing something wrong, tried only using "[Enter Parameter]"
but still won't allow me to look in more than 1 field at the time.

Can someone please help me.

Thanks in advance,
Emilio
 
Your first error seems to be un-normalized tables (I could be wrong).
Otherwise, consider changing all your ANDs to ORs.
 
Thanks a lot, it works.

And you mention the "U" word I have always dreaded, it could be, but when I
originally designed the database I had no idea of how to keep track of
invoice numbers for different suppliers other than creating a field for each
supplier.

Could you or someone else please maybe explain to me how to do it?

Thanks,
Emilio



Duane Hookom said:
Your first error seems to be un-normalized tables (I could be wrong).
Otherwise, consider changing all your ANDs to ORs.

--
Duane Hookom
MS Access MVP
--

Wind54Surfer said:
Hi all,

I need to find an invoice number sometimes and decided to create this
query:
----------------------------------------------------------------------------
SELECT Orders.OrderID, Orders.InvVWD, Orders.InvNorthern,
Orders.InvPremium,
Orders.InvYork, Orders.InvVinylbilt, Orders.InvExtras,
Orders.InvInstaller,
Orders.InvCutOut, Orders.InvLumber, Orders.InvSuntown, Orders.InvOther,
Orders.InvSalesperson
FROM Orders
WHERE (((Orders.InvVWD) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvNorthern) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvPremium) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvYork) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvVinylbilt) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvExtras) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvInstaller) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvCutOut) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvLumber) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvSuntown) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvOther) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvSalesperson) Like "*" & [Enter Parameter] & "*"));
-------------------------------------------------------------------------
Obviously I am doing something wrong, tried only using "[Enter Parameter]"
but still won't allow me to look in more than 1 field at the time.

Can someone please help me.

Thanks in advance,
Emilio
Click to expand...
Click to expand...
 
Normalization is a bit much to explain in a reply. Consider reviewing some
of the links from Jeff Conrad's site
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101.
If you have some specific questions, post them back to the tablesdbdesign
news group.

--
Duane Hookom
MS Access MVP
--

Wind54Surfer said:
Thanks a lot, it works.

And you mention the "U" word I have always dreaded, it could be, but when
I
originally designed the database I had no idea of how to keep track of
invoice numbers for different suppliers other than creating a field for
each
supplier.

Could you or someone else please maybe explain to me how to do it?

Thanks,
Emilio



Duane Hookom said:
Your first error seems to be un-normalized tables (I could be wrong).
Otherwise, consider changing all your ANDs to ORs.

--
Duane Hookom
MS Access MVP
--

Wind54Surfer said:
Hi all,

I need to find an invoice number sometimes and decided to create this
query:
----------------------------------------------------------------------------
SELECT Orders.OrderID, Orders.InvVWD, Orders.InvNorthern,
Orders.InvPremium,
Orders.InvYork, Orders.InvVinylbilt, Orders.InvExtras,
Orders.InvInstaller,
Orders.InvCutOut, Orders.InvLumber, Orders.InvSuntown, Orders.InvOther,
Orders.InvSalesperson
FROM Orders
WHERE (((Orders.InvVWD) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvNorthern) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvPremium) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvYork) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvVinylbilt) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvExtras) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvInstaller) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvCutOut) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvLumber) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvSuntown) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvOther) Like "*" & [Enter Parameter] & "*") AND
((Orders.InvSalesperson) Like "*" & [Enter Parameter] & "*"));
-------------------------------------------------------------------------
Obviously I am doing something wrong, tried only using "[Enter
Parameter]"
but still won't allow me to look in more than 1 field at the time.

Can someone please help me.

Thanks in advance,
Emilio
Click to expand...
Click to expand...
Click to expand...
 
Back
Top