Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-10-2017, 04:14 AM   #1
splreece
Newly Registered User
 
Join Date: Jun 2016
Posts: 40
Thanks: 10
Thanked 1 Time in 1 Post
splreece is on a distinguished road
search query logic question - showing nil values

Morning all,

Kind of a newbee question but I am getting into more complicated stuff and may have bypassed some fundamental query techniques.

I think the cause is that I want the user to fill in as many unbound boxes as they need (and leave unneeded ones blank), but the query is assuming that any blank unbound box means its subsequent search criteria must equal blank/Nil.




My setup means I have the same issue with both user criteria parts of my dbase:

- My search form needs to be able to search multiple criteria (8 different unbound boxes) and show results of any combination of those boxes whether the user completes them or not.
i.e. User or ID or Priority or Milestone.

- My report design form needs to be able to pull EXACT matches against the same multiple criteria (the same 8 different unbound boxes) into a report and show only what is selected.
i.e. user = exact match (then filter) id = exact match (then filer) priority = exact match.

In both cases I want the query to know that if the user doesn't complete a particular field that the query should bring back all available entries.



The issue I am having is that if the user doesn't fill in ALL the search or reporting boxes, the results are nil (I assume by leaving a search criteria blank is only accepts blank criteria).

Example of the Search form Query Design (all in different rows ensuring "OR" is taken into account)
[Forms]![frm_Search]![src_cmbpriority]
[Forms]![frm_Search]![src_milestones]


Example of the Report Query Design (all in the same criteria row to ensure that exact match only).

[Forms]![frm_ReportControl]![rc_Priority]
[Forms]![frm_ReportControl]![rc_AssignedTo]
[Forms]![frm_ReportControl]![rc_Status]



any thoughts....???

splreece is offline   Reply With Quote
Old 03-10-2017, 04:27 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,463
Thanks: 40
Thanked 3,381 Times in 3,276 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: search query logic question - showing nil values

this

Forms]![frm_Search]![src_cmbpriority]

needs to be

(Forms]![frm_Search]![src_cmbpriority] OR Forms]![frm_Search]![src_cmbpriority] is Null)


note the brackets
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
splreece (03-10-2017)
Old 03-10-2017, 05:09 AM   #3
splreece
Newly Registered User
 
Join Date: Jun 2016
Posts: 40
Thanks: 10
Thanked 1 Time in 1 Post
splreece is on a distinguished road
Re: search query logic question - showing nil values

ahh thank you very much
so remove the first and last [] and replace with () to wrap both into a single query.

splreece is offline   Reply With Quote
Old 03-10-2017, 06:16 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,463
Thanks: 40
Thanked 3,381 Times in 3,276 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: search query logic question - showing nil values

it wil automatically add back the square brackets, my mistake when copy/pasting. Should be

([Forms]![frm_Search]![src_cmbpriority] OR [Forms]![frm_Search]![src_cmbpriority] is Null)

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London 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
[SOLVED] Query not showing unique values when using <> acarterczyz Queries 3 08-18-2014 11:06 AM
How to DoubleClick Listbox showing results from a search in a query wrweaver Modules & VBA 3 02-26-2013 01:19 PM
Showing zero values in a Union query coach.32 Queries 1 08-30-2011 02:16 AM
Sql query not showing all the values usr123 Queries 2 02-24-2010 09:10 AM
Query - Showing values if a certain time jagstangman Queries 4 05-03-2008 05:17 AM




All times are GMT -8. The time now is 08:58 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 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World