Please help with my UNION Query

[riekmaharg2]

Hi everyone

I've currently got a UNION Query in order to enable large searches, however I now require that the Name fields have "Like" statements to enable the user to search for all names similar to what they have entered. I've tried various instructions from various forums, however for some annoying reason none of the Null statements will work, so at the moment you have to enter something into the name fields.

What I need is so that access will ingore the name field if its blank and only search for the fields that have been filled in. Heres my UNION query code for the Name search fields:

UNION
SELECT Orders.[Job Number], Orders.[Enquiry date], Orders.[Job Name], Orders.[Contact Name],Orders.Position, Orders.Organisation, Orders.[Type of Institution], Orders.[Address Line 1], Orders.[Address Line 2], Orders.Town, Orders.County, Orders.Postcode, Orders.[Phone Number], Orders.[Mobile Number], Orders.[Fax Number], Orders.[Email address], Orders.[Carers name], Orders.[Relationship to client], Orders.[Client Name], Orders.[Address Line 3], Orders.[Address Line 4], Orders.[Client Town], Orders.[Client County], Orders.[Client Postcode], Orders.[Client phone number], Orders.[Client mobile number], Orders.[Enquiry details], Orders.[Room plan], Orders.[Room plan number], Orders.[Revised room plan], Orders.[General notes], Orders.[Quote number], Orders.[Customer Order number], Orders.[Internal Order number], Orders.[Delivery date], Orders.[Delivery charge], Orders.[Fitters report], Orders.[Invoice number], Orders.[Date paid], Orders.Products, Orders.Demo, Orders.[Client reviews], Orders.[Funded by], Orders.[Funding type], Orders.[Action to take], Orders.[How did you find out about us], Orders.[At which exhibition/demo], Orders.[Use & care guide number], Orders.[VAT exempt form received]
FROM Orders
WHERE (((Orders.[Contact Name])=[Forms]![Search Form]![Contact Name]))

UNION
SELECT Orders.[Job Number], Orders.[Enquiry date], Orders.[Job Name], Orders.[Contact Name], Orders.Position, Orders.Organisation, Orders.[Type of Institution], Orders.[Address Line 1], Orders.[Address Line 2], Orders.Town, Orders.County, Orders.Postcode, Orders.[Phone Number], Orders.[Mobile Number], Orders.[Fax Number], Orders.[Email address], Orders.[Carers name], Orders.[Relationship to client], Orders.[Client Name], Orders.[Address Line 3], Orders.[Address Line 4], Orders.[Client Town], Orders.[Client County], Orders.[Client Postcode], Orders.[Client phone number], Orders.[Client mobile number], Orders.[Enquiry details], Orders.[Room plan], Orders.[Room plan number], Orders.[Revised room plan], Orders.[General notes], Orders.[Quote number], Orders.[Customer Order number], Orders.[Internal Order number], Orders.[Delivery date], Orders.[Delivery charge], Orders.[Fitters report], Orders.[Invoice number], Orders.[Date paid], Orders.Products, Orders.Demo, Orders.[Client reviews], Orders.[Funded by], Orders.[Funding type], Orders.[Action to take], Orders.[How did you find out about us], Orders.[At which exhibition/demo], Orders.[Use & care guide number], Orders.[VAT exempt form received]
FROM Orders
WHERE (((Orders.[Client Name])=[Forms]![Search Form]![Client Name]))

UNION
SELECT Orders.[Job Number], Orders.[Enquiry date], Orders.[Job Name], Orders.[Contact Name], Orders.Position, Orders.Organisation, Orders.[Type of Institution], Orders.[Address Line 1], Orders.[Address Line 2], Orders.Town, Orders.County, Orders.Postcode, Orders.[Phone Number], Orders.[Mobile Number], Orders.[Fax Number], Orders.[Email address], Orders.[Carers name], Orders.[Relationship to client], Orders.[Client Name], Orders.[Address Line 3], Orders.[Address Line 4], Orders.[Client Town], Orders.[Client County], Orders.[Client Postcode], Orders.[Client phone number], Orders.[Client mobile number], Orders.[Enquiry details], Orders.[Room plan], Orders.[Room plan number], Orders.[Revised room plan], Orders.[General notes], Orders.[Quote number], Orders.[Customer Order number], Orders.[Internal Order number], Orders.[Delivery date], Orders.[Delivery charge], Orders.[Fitters report], Orders.[Invoice number], Orders.[Date paid], Orders.Products, Orders.Demo, Orders.[Client reviews], Orders.[Funded by], Orders.[Funding type], Orders.[Action to take], Orders.[How did you find out about us], Orders.[At which exhibition/demo], Orders.[Use & care guide number], Orders.[VAT exempt form received]
FROM Orders
WHERE (((Orders.[Carers Name])=[Forms]![Search Form]![Carers Name]))

I've removed all the like and nulls to start from fresh. This query is also linked to the Search Form in order for users to create a search. If anyone could come up with a way of ignoring the search fields if they are blank that would be great.

PS im using MS Access 2007 if that matters.

 

[namliam]

Ideally you dont want to search on blank fields period... this will just make things messy.

Further more you dont need a union query, you are looking for an OR construction here:
SELECT ....
FROM Orders
WHERE Orders.[Contact Name]=[Forms]![Search Form]![Contact Name]
OR Orders.[Client Name]=[Forms]![Search Form]![Client Name]
OR Orders.[Carers Name]=[Forms]![Search Form]![Carers Name]

Now you can make things very complex trying to filter down for nulls, for each box, instead of:
Orders.[Contact Name]=[Forms]![Search Form]![Contact Name]
You would need:
( Orders.[Contact Name]=[Forms]![Search Form]![Contact Name]
OR [Forms]![Search Form]![Contact Name] IS NULL )
Etc....

Instead you should make code that will make your query dynamic to only add the where clauses you want, have a search on the forum for some sample "search form" s and see if you cannot addapt that to your situation.

P.S. Welcome to AWF

 

[riekmaharg2]

Hi cheers for the reply. I've managed to get the OR's working on a single query, however once I add the Like and Is Null statements the query still wont work unless a value is entered into the Contact Name field which has the like and null statements:

WHERE (((Orders.[Job Number])=Forms![Search Form]![Job Number])) Or (((Orders.[Job Name])=Forms![Search Form]![Job Name])) Or (((Orders.[Contact Name]) Like "*" & Forms![Search Form]![Contact Name] & "*")) Or ((([Forms]![Search Form]![Contact Name]) Is Null))



I'm I missing something obvious here??

 

[namliam]

Appart from your bracketing being off vs what I posted it works, I can guarantee you.

Remember that you have to "leave" the text box before you run your query.

However I cannot stress this enough

Instead you should make code that will make your query dynamic to only add the where clauses you want, have a search on the forum for some sample "search form" s and see if you cannot addapt that to your situation.

Instead you should make code that will make your query dynamic to only add the where clauses you want, have a search on the forum for some sample "search form" s and see if you cannot addapt that to your situation.

and

Instead you should make code that will make your query dynamic to only add the where clauses you want, have a search on the forum for some sample "search form" s and see if you cannot addapt that to your situation.

 

[riekmaharg2]

Originally Posted by Me
Instead you should make code that will make your query dynamic to only add the where clauses you want, have a search on the forum for some sample "search form" s and see if you cannot addapt that to your situation.

Lol. I did have a look at some Dynamic Queries however its all code, and im not that familiar with code. I found a sample peice from Microsoft's help website but I couldn't figure out how to change it to my situation.

Remember that you have to "leave" the text box before you run your query.

Yes in order for me to perform the search I have to click the search button which is just the same as leaving the box.




I've got this to work:

WHERE (Orders.[Contact Name]=Forms![Search Form]![Contact Name] Or Forms![Search Form]![Contact Name] Is Null) AND (Orders.[Job Number]=Forms![Search Form]![Job Number] Or Forms![Search Form]![Job Number] Is Null);


However I'm unsure how to add the like "*" &. I've tried doing it like this:

WHERE (Orders.[Contact Name]= Like "*" & Forms![Search Form]![Contact Name] & "*" Or Forms![Search Form]![Contact Name] Is Null) AND (Orders.[Job Number]=Forms![Search Form]![Job Number] Or Forms![Search Form]![Job Number] Is Null);

But it didn't work. Sorry if I seem dumb but I've never done anything like this on Access before. Can you tell me where I'm going wrong?