Queries based on forms (one OR multiple fields)

[iamsophie]

Hi Everyone,

I’m trying to create a database, which shows all of our campaign sales from Jan 2014. The database includes details like:
- Campaign name
- Campaign type
- Product ID
- Category
- Exact Date
- Month
- Year
- Total Sales
- Incremental Sales
- Uplift %
- Etc.

I have a form with multiple fields, where the user can enter any of the below information:
- Part of the campaign name
- Campaign type
- Product ID
- Category
- Date
- Month
- Year

I also built a query, this is my current code: (broken down for readability)

SELECT Historical.[Dem Name], Historical.Date, Historical.Category, Historical.WIN, Historical.Product, Historical.[Activity Av Per Store £], Historical.[Increm Av Per Store £], Historical.[Activity Sales £], Historical.[Forecast £], Historical.[Incremental £], Historical.[Uplift % £], Historical.[No of Stores]
FROM Historical
WHERE (((Historical.[Dem Name]) Like Nz(Forms![Historical Form]!DemName,""))) 
OR (((Historical.[Dem Type]) Like Nz(Forms![Historical Form]!DemType,""))) 
OR (((Historical.Date) Like Nz(Forms![Historical Form]!ExactDate,""))) 
OR (((Historical.Category) Like Nz(Forms![Historical Form]!Category,""))) 
OR (((Historical.WIN) Like Nz(Forms![Historical Form]!WIN,""))) 
OR (((Historical.Month) Like Nz(Forms![Historical Form]!Month,""))) 
OR (((Historical.Year) Like Nz(Forms![Historical Form]!Year,"")));

It works, but not how I want it. It finds data from the table correctly if ONLY ONE of the fields are complete. E.g. if I enter April, it will find all campaigns from April 2014 and 2015, or if I choose the Beer category, it will find all beer-related campaigns.

Although if I select April AND Beer, it will still list ALL Beer campaigns as the category is before the month in the code. I’d like it to find Beer campaigns only from April 2014 and 2015. Also if I add the year as well, say 2014, it should find Beer campaigns from April 2014.

I tried changing the ORs to ANDs, but then it wouldn’t return anything.

I’m new to all this, only started last afternoon, I’ve built this code using several things I read on forums, so apologies in advance if I have to ask questions.

Thanks for your help,
Sophie

[jforbes]

I just typed this in, so hopefully there wont be many errors, but typically you can do what you want like this:

SELECT Historical.[Dem Name], Historical.Date, Historical.Category, Historical.WIN, Historical.Product, Historical.[Activity Av Per Store £], Historical.[Increm Av Per Store £], Historical.[Activity Sales £], Historical.[Forecast £], Historical.[Incremental £], Historical.[Uplift % £], Historical.[No of Stores]
FROM Historical
WHERE (((Historical.[Dem Name]) Like Nz(Forms![Historical Form]!DemName,"") OR Nz(Forms![Historical Form]!DemName,"")="")) 
 AND (((Historical.[Dem Type]) Like Nz(Forms![Historical Form]!DemType,"") OR Nz(Forms![Historical Form]!DemType,"")="")) 
 AND (((Historical.Date) Like Nz(Forms![Historical Form]!ExactDate,"") OR Nz(Forms![Historical Form]!ExactDate,"")="")) 
 AND (((Historical.Category) Like Nz(Forms![Historical Form]!Category,"") OR Nz(Forms![Historical Form]!Category,"")="")) 
 AND (((Historical.WIN) Like Nz(Forms![Historical Form]!WIN,"") OR Nz(Forms![Historical Form]!WIN,"")="")) 
 AND (((Historical.Month) Like Nz(Forms![Historical Form]!Month,"") OR Nz(Forms![Historical Form]!Month,"")="")) 
 AND (((Historical.Year) Like Nz(Forms![Historical Form]!Year,"") OR Nz(Forms![Historical Form]!Year,"")=""));

The idea is to AND the expressions together. Since AND needs to have all expressions as true to return a true, the expression needs to return true for values that aren't supplied.

[iamsophie]

That's amazing, it works! Thanks so much! :)