Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-12-2019, 11:47 AM   #1
goophibama
Newly Registered User
 
Join Date: Jul 2019
Posts: 2
Thanks: 2
Thanked 0 Times in 0 Posts
goophibama is on a distinguished road
Multi criteria query

I was hoping someone could help me with an issue I'm having with a query and hopefully, I can do a decent job of explaining what I'm wanting.

I have a database of business licenses that I am building and I need a query that will return an activity type but only for businesses that are licensed this year. So for instance, you might want to check to get a list of bowling alleys that have an active license, but the user can put in a partial string and it will find matches, but again, only for those with a date falling within this year. I've tried every combination that I can think of, but I can't get this to work. Does anyone have any suggestions? It's been about 15 years since I've used Access, so what I used to know (which admittedly wasn't much) is pretty much gone and I am having to self-teach from scratch. Thanks in advance, this one has been driving me crazy.

Access Capture.GIF

goophibama is offline   Reply With Quote
Old 07-12-2019, 12:09 PM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,281
Thanks: 10
Thanked 2,249 Times in 2,202 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Multi criteria query

Couple things:

1. You have criteria on two different lines, this means you are not using them together (AND) but seperately (OR). Your query returns results that passes just 1 of your criterion. To work together, you need both criterion on the same line--move the date criterion up a line.

2. Change your date field to one that just obtains the year:

YearActive: Year([YourFieldNameHere])

Then for the criteria use the current year:

=Year(Date())

That should do what you want.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
goophibama (07-12-2019)
Old 07-12-2019, 12:11 PM   #3
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 720
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Multi criteria query

get rid of the "and" for starters because that is putting the literal word 'and' as part of your date field criteria. Then there is the fact that you have each critieria on separate rows, which means "gimme based on this" OR "gimme based on that". If on the same row, it is an AND situation. Your post reads like you want "and", so on one row. You could try either way.

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)

Last edited by Micron; 07-12-2019 at 12:11 PM. Reason: clarification
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
goophibama (07-12-2019)
Old 07-12-2019, 12:44 PM   #4
goophibama
Newly Registered User
 
Join Date: Jul 2019
Posts: 2
Thanks: 2
Thanked 0 Times in 0 Posts
goophibama is on a distinguished road
Re: Multi criteria query

Thanks for the help. I moved it to the same line, then keyed off of another field (business license year) and made the change and it worked. Why I didn't key off of the business license year field in the first place is beyond me. Thanks for your help, I really appreciate it!
goophibama is offline   Reply With Quote
Old 07-12-2019, 02:55 PM   #5
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 720
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Multi criteria query

NP. Good luck, and thanks for the reputation boost.

Micron is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA and Query criteria from Multi-Value lists KNutta Queries 8 07-29-2015 11:29 PM
Multi criteria combo query problem compton clerk Queries 5 08-03-2014 11:55 AM
Ignoring null criteria in a multi-criteria query… Nano Queries 16 10-29-2013 12:01 PM
Multi Select ListBox criteria in query oihjk Forms 1 04-01-2003 03:58 PM
[SOLVED] using multi-select on a listbox to set query criteria criteria kom73 Forms 1 10-10-2001 03:45 AM




All times are GMT -8. The time now is 08:48 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World