By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,238 Members | 1,657 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,238 IT Pros & Developers. It's quick & easy.

Queries based on forms (one OR multiple fields)

P: 6
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)

Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM Historical
  3. WHERE (((Historical.[Dem Name]) Like Nz(Forms![Historical Form]!DemName,""))) 
  4. OR (((Historical.[Dem Type]) Like Nz(Forms![Historical Form]!DemType,""))) 
  5. OR (((Historical.Date) Like Nz(Forms![Historical Form]!ExactDate,""))) 
  6. OR (((Historical.Category) Like Nz(Forms![Historical Form]!Category,""))) 
  7. OR (((Historical.WIN) Like Nz(Forms![Historical Form]!WIN,""))) 
  8. OR (((Historical.Month) Like Nz(Forms![Historical Form]!Month,""))) 
  9. 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
May 29 '15 #1

✓ answered by jforbes

I just typed this in, so hopefully there wont be many errors, but typically you can do what you want like this:
Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM Historical
  3. WHERE (((Historical.[Dem Name]) Like Nz(Forms![Historical Form]!DemName,"") OR Nz(Forms![Historical Form]!DemName,"")="")) 
  4. AND (((Historical.[Dem Type]) Like Nz(Forms![Historical Form]!DemType,"") OR Nz(Forms![Historical Form]!DemType,"")="")) 
  5. AND (((Historical.Date) Like Nz(Forms![Historical Form]!ExactDate,"") OR Nz(Forms![Historical Form]!ExactDate,"")="")) 
  6. AND (((Historical.Category) Like Nz(Forms![Historical Form]!Category,"") OR Nz(Forms![Historical Form]!Category,"")="")) 
  7. AND (((Historical.WIN) Like Nz(Forms![Historical Form]!WIN,"") OR Nz(Forms![Historical Form]!WIN,"")="")) 
  8. AND (((Historical.Month) Like Nz(Forms![Historical Form]!Month,"") OR Nz(Forms![Historical Form]!Month,"")="")) 
  9. 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.

Share this Question
Share on Google+
2 Replies


jforbes
Expert 100+
P: 1,107
I just typed this in, so hopefully there wont be many errors, but typically you can do what you want like this:
Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM Historical
  3. WHERE (((Historical.[Dem Name]) Like Nz(Forms![Historical Form]!DemName,"") OR Nz(Forms![Historical Form]!DemName,"")="")) 
  4. AND (((Historical.[Dem Type]) Like Nz(Forms![Historical Form]!DemType,"") OR Nz(Forms![Historical Form]!DemType,"")="")) 
  5. AND (((Historical.Date) Like Nz(Forms![Historical Form]!ExactDate,"") OR Nz(Forms![Historical Form]!ExactDate,"")="")) 
  6. AND (((Historical.Category) Like Nz(Forms![Historical Form]!Category,"") OR Nz(Forms![Historical Form]!Category,"")="")) 
  7. AND (((Historical.WIN) Like Nz(Forms![Historical Form]!WIN,"") OR Nz(Forms![Historical Form]!WIN,"")="")) 
  8. AND (((Historical.Month) Like Nz(Forms![Historical Form]!Month,"") OR Nz(Forms![Historical Form]!Month,"")="")) 
  9. 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.
May 29 '15 #2

P: 6
That's amazing, it works! Thanks so much! :)
May 29 '15 #3

Post your reply

Sign in to post your reply or Sign up for a free account.