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
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.
2 864
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.
That's amazing, it works! Thanks so much! :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: googleboy |
last post by:
I didn't think this would be as difficult as it now seems to me.
I am reading in a csv file that documents a bunch of different info on
about 200 books, such as title, author, publisher, isbn,...
|
by: Jonathan LaRosa |
last post by:
Hi all -
I'm wondering if anyone has (or knows of) a tool that will allow me to
search through VB code, tables, queries, reports, forms, and other
objects, for references to all other types of...
|
by: tesc |
last post by:
I am so aggravated and need any help I can get. I am using Access 2000
and am trying to sort multiple fields in a select query.
My query is set up as follows:
FIELD 1 FIELD 2 FIELD 3 ...
|
by: Rob Williamson |
last post by:
Does anyone know whether there is a way to create folders inside the
right payne when you click on Tables or Queries or Forms or Reports
in the the Objects side.
I have alot of queries and forms...
|
by: csolomon |
last post by:
Hello:
I am using two queries to get one result set. The issue is, I return no data when I combine them into one query. I have listed both queries, as well as the 3rd query that shows them...
|
by: Brett |
last post by:
Hello,
Is it possible to have just one criteria and have it apply to a group
of queries?
I am trying to create a report with the separate results of 4 queries
based on a prompt for the user...
|
by: bonneylake |
last post by:
Hey Everyone,
Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place.
...
|
by: Lyn DeMaio |
last post by:
I've been tasked with taking two unrealted tables (Providers and Patients, no common field) and allow users, on one form (or form/subform) to search multiple fields in each table, select a record...
|
by: LeighW |
last post by:
Hi,
I'm still having a couple problems with searches.
I have a search form, frm_Search.
The form I am trying to filter, frm_Form1
An unbound combobox on frm_Search, Cbo_Permit
6 different...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |