UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Combo Boxes Empty Value In Query Will Not Return Records, Access 2013    
 
   
Doug Galayda
post Sep 12 2017, 05:15 PM
Post#1



Posts: 198
Joined: 19-May 10
From: Springfield, Illinois


Hi UA,
I have a query that accepts a date and then a week range parameter from a report form driver screen
And is used to generate a report. The query below is the query that pulls in the data for the report.

It works fine, but when I added a Reviewer drop down/combo box,
That lists reviewers initials that reviewed the record,
It works fine with a value selected, like RCC
But when I select the empty/blank at the top of the combo box value list,
The report generates blank because I create this fail: (value + null = null), so I get nothing,
I would like to have it detect the null value (and somehow) take the AND off the where clause for that Reviewer combo/field value.
--don’t know how to do that
I have null as the combo boxes data/default value.

The core problem is the line after the second And in the Where clause,
This represents the Reviewer combo box value.
I want to give NO value to the where clause when I select the empty value at the top of the combo box,
But this empty value is causing the where clause to return nothing when the combo box is blank
**I have tried Null also, putting Null in the data/default value, and in the line below, if value not found return Null
~~that performs the same results as the use of ""
And also tried '' and it gives the same results as ""

((dbo_tblPermits.REVIEWER)=Nz([Forms]![FrmActionReport].[ComboReviewer],""))

**The reviewer column is either has a value or it is blank—NULL
**This reviewer column is called Rev in the middle of the report columns.
**Its data type is Short Text
**It Belongs to a linked table.

SELECT dbo_tblPermits.pkLog_No, dbo_tblPermits.Due, dbo_tblPermits.Waiver, GetActionDate_2(Nz([Due],""),Nz([Waiver],"")) AS ActionDate, [forms]![frmActionReport]![StartMon] & " thru " & ([Forms]![frmActionReport]![StartMon]+([forms]![frmActionReport]![cboWeeks]*7)-3) AS For_Period, dbo_tblPermits.NAME, dbo_tblPermits.fkSiteID, dbo_tblPermits.REVIEWER, dbo_tblPermits.GAU_REV, dbo_tblPermits.P_TYPE, dbo_tblPermits.F_TYPE, dbo_tblPermits.MAILED, IsNull([Reviewer]) AS Expr1
FROM dbo_tblPermits
WHERE (((GetActionDate_2(Nz([Due],""),Nz([Waiver],""))) Between [Forms]![frmActionReport]![StartMon] And ([Forms]![frmActionReport]![StartMon]+([forms]![frmActionReport]![cboWeeks]*7)-3)) AND ((dbo_tblPermits.REVIEWER)=Nz([Forms]![FrmActionReport].[ComboReviewer],"")) AND ((dbo_tblPermits.MAILED)='') AND ((Left([pkLog_No],4))>="1999"))
ORDER BY GetActionDate_2(Nz([Due],""),Nz([Waiver],""));

And Q: How do you use SQL to format the query as a query in the text?
I did my best to explain this situation, but please feel free to ask for any explanation on this post you want!
**also i have another very similar combo box I need to add to this query please.
Thanks in advance for Any ideas!
This post has been edited by Doug Galayda: Sep 12 2017, 05:30 PM
Attached File(s)
Attached File  Snippet20170911.PNG ( 24.26K )Number of downloads: 2
Attached File  Snippet20170911.PNG ( 24.26K )Number of downloads: 0
 
Go to the top of the page
 
John Vinson
post Sep 12 2017, 07:56 PM
Post#2


UtterAccess VIP
Posts: 4,105
Joined: 6-January 07
From: Parma, Idaho, US


You can use Boolean logic in the query. Rather than

AND ((dbo_tblPermits.REVIEWER)=Nz([Forms]![FrmActionReport].[ComboReviewer],""))

try

AND (dbo_tblPermits.REVIEWER=[Forms]![FrmActionReport].[ComboReviewer] OR [Forms]![FrmActionReport].[ComboReviewer] IS NULL)

If ComboReviewer contains a value it will be compared to the table field; if it's empty, the table field will be ignored since the second part of the OR will apply.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
Doug Galayda
post Sep 12 2017, 09:20 PM
Post#3



Posts: 198
Joined: 19-May 10
From: Springfield, Illinois


Thanks John I will give a try, thanks!
This post has been edited by Doug Galayda: Sep 12 2017, 09:21 PM
Go to the top of the page
 
Doug Galayda
post Sep 13 2017, 09:54 AM
Post#4



Posts: 198
Joined: 19-May 10
From: Springfield, Illinois


@John I appreciate your effort but your suggestion caused a error
And it would bring back Both Either null or not null, While I want to act on Only the value selected.
Do you have alternative option? I will try it!
This post has been edited by Doug Galayda: Sep 13 2017, 09:55 AM
Go to the top of the page
 
Doug Galayda
post Sep 13 2017, 09:57 AM
Post#5



Posts: 198
Joined: 19-May 10
From: Springfield, Illinois


This is the problem stated another way:
([Forms]![FrmActionReport].[ComboReviewer] IS NULL) and
This returns what I want---returns all the various Reviewers initials within that date range.
//Versus//
((dbo_tblPermits.REVIEWER)=Nz(Forms!FrmActionReport.ComboReviewer,"")) and
~~my original line Does Work to bring back only what I have selected (example RCC),
BUT, IF I SELECT THE EMPTY VALUE at the top of the combo boxes value list,
--it Returns NO RECORDS~~BUT SHOULD RETURN ALL the nulls in the that date range.
How can I make it a pivot/ flip flop/ IF/ELSE ?? to plant the line in the select statement?
Would the very next step be to build a IIF statement on the criterial line?
if that combo field value is null, return [not Null handler] string, else return [Null handler] string?
Go to the top of the page
 
John Vinson
post Sep 13 2017, 11:36 PM
Post#6


UtterAccess VIP
Posts: 4,105
Joined: 6-January 07
From: Parma, Idaho, US


Null is a funny beast: it means "This variable has no value - it's undefined, uninitialized, has no meaning whatsoever". As such, nothing is equal to NULL; for that matter, nothing is UNEQUAL to NULL! The expression X = Null isn't true, or false; it's NULL. So is X <> Null! This is the case even if X is itself NULL! Basically, comparing X with NULL (if X is NULL) is like asking "Is (I don't know what this is) equal to (I don't know what this is either)?" The only answer possible is "I don't know!"

To have a NULL criterion match NULL values in a field, you need to test both:

AND (dbo_tblPermits.REVIEWER=[Forms]![FrmActionReport].[ComboReviewer] OR ([Forms]![FrmActionReport].[ComboReviewer] IS NULL AND dbo_tblPermits.REVIEWER IS NULL))

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th September 2017 - 12:01 PM