Queries based on forms (one OR multiple fields)

[i.am.sophie]

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

 

[vbaInet]

Welcome to the forum!

Using only two criteria, it will look like this:

WHERE 	([Dem Name] = [Forms]![Historical Form]![DemName] OR [Forms]![Historical Form]![DemName] IS NULL)
AND 	([Dem Type] = [Forms]![Historical Form]![DemType] OR [Forms]![Historical Form]![DemType] IS NULL)

... note the format and apply the other fields exactly as written.

As an aside, since you mentioned that you only started this since yesterday, I have a hunch that your table structure may not be normalised.

 

[vbaInet]

Actually, test it thoroughly with the two fields first before adding the others.

 

[i.am.sophie]

Thanks a lot! I've seen this on a forum, but it always returned an empty table. Probably it's something on my end, but just can't figure out why it's not working like the way you suggested.

In the meantime, I've also received an answer on a different forum and it works correctly, just FYI:

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,"")=""));

Thanks a lot for your help anyway!

 

[vbaInet]

What I gave you is the same as what you got elsewhere, except that mine is much more efficient. If you had tried it as I wrote you will find that it works... these are tried and tested methods.