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: 210
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,128
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: 210
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: 210
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: 210
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,128
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
 
Doug Galayda
post Sep 20 2017, 02:16 PM
Post#7



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


John, I appreciate your help! I am sincerely trying to get the drop down to feed Null into the where clause to get various intial record returned
for the select date range.

●I have added a IIF statement in the criterial line in the Reviewer column.
●I can select RCC and I get RCC records and if I select SMS, I get SMS records.
●It is working for selected qualified initials from the drop down, but when I select the NULL value
--it is not feeding a NULL value into my where clause.
●This is my query for the REV/Reviewer drop down

SELECT DISTINCT dbo_tblPermits.REVIEWER AS Expr1, dbo_tblPermits.REVIEWER, IsNull([reviewer]) AS Expr2
FROM dbo_tblPermits
ORDER BY dbo_tblPermits.REVIEWER;
(I did have a Is not null under the reviewer in the drop down supply query, but I took that out today)

●When It shows 2 blank records at the top
●And I have default value = null in the drop down fields properties.
==========================================
●Notice the -1 in the expression below, I temp put a isNull() in there prove to myself if it Actually was Null.
●The expression returns -1 for null
● That means it is a true null.
The second record is a blank but not null value
● when I select the top value from the drop down list, that is what goes into my Reviewer IIF Statement
==========================================
What I have tried. Below are IIF statements from the criterial line of the Reviewer column(which contains user's name initials)
All three of these return good records if reviewer(initials) are selectd, but Want to make it return all initials for the date range
If user selects the empty top drop down value. And I have taken the reviewer criterial out and it does return various initals
For that date range.
#1)IIf([Forms]![FrmActionReport].[ComboReviewer] Is Not
Null,[Forms]![FrmActionReport].[ComboReviewer],[dbo_tblPermits].[REVIEWER] Is Not Null) <--Returns NO records if blank top choice is select, but will return RCC records if RCC is selected.
#2) IIf([Forms]![FrmActionReport].[ComboReviewer] Is Not Null,[Forms]![FrmActionReport].[ComboReviewer],Null) <-- Returns NO records if blank top choice is select, but will return RCC records if RCC is selected.
This post has been edited by Doug Galayda: Sep 20 2017, 03:16 PM
Attached File(s)
Attached File  RevqryDesignMode.PNG ( 4.46K )Number of downloads: 0
Attached File  ReviewerDropDownQry.PNG ( 25.23K )Number of downloads: 0
Attached File  RptQryDoesReturnInitialSelection.PNG ( 9.55K )Number of downloads: 0
Attached File  VARIOUS_Return.PNG ( 61.27K )Number of downloads: 0
Attached File  WithJohnsLineIn.PNG ( 44.34K )Number of downloads: 0
 
Go to the top of the page
 
Doug Galayda
post Sep 20 2017, 03:18 PM
Post#8



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


**John, by the way, I am sorry about not replying in 5 days, Not right.
--With your line in there,(see yellow) it returns RCC records if that is what is selected
But returns NONE if the top Empty blank drop down value is selected.
**I did try it, and I tried to modify it, but I could not get it too deliver the various reviewers for the date range
Then your line gave me the idea to put it into a IIF statement, so that is what I currently have it in.

IIf([Forms]![FrmActionReport].[ComboReviewer] Is Not Null,[Forms]![FrmActionReport].[ComboReviewer],[dbo_tblPermits].[REVIEWER] Is Not Null)
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.MAILED)='') AND ((Left([pkLog_No],4))>="1999"))
AND (dbo_tblPermits.REVIEWER=[Forms]![FrmActionReport].[ComboReviewer] OR ([Forms]![FrmActionReport].[ComboReviewer] IS NULL AND dbo_tblPermits.REVIEWER IS NULL))
ORDER BY GetActionDate_2(Nz([Due],""),Nz([Waiver],''));
This post has been edited by Doug Galayda: Sep 20 2017, 04:17 PM
Attached File(s)
Attached File  WithJohnsLineIn.PNG ( 44.34K )Number of downloads: 0
Attached File  4_RCC_Records_Returned.PNG ( 9.55K )Number of downloads: 1
Attached File  NoneReturned.PNG ( 2.14K )Number of downloads: 0
Attached File  JohnsLineInReviewerColumn.PNG ( 2.65K )Number of downloads: 0
 
Go to the top of the page
 
Doug Galayda
post Sep 20 2017, 05:05 PM
Post#9



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


Attention: The problem(Post) has been solved, Yay!

I found the solution to the query with this IIF statement below at 5:00pm today, Wednesday/9/20/2017
the drop down works the way it should now,
I had to set the drop down value to null, and I don't think it is correctly done
but it does work, bottom line, so I will use it.

Thanks for all you suggestions John, I appreciate your help!

IIf([Forms]![FrmActionReport].[ComboReviewer] Is Not Null,[Forms]![FrmActionReport].[ComboReviewer],
IIf([Forms]![FrmActionReport].[ComboReviewer] Is Null,[Forms]![FrmActionReport].[ComboReviewer]=Null,''))
This post has been edited by Doug Galayda: Sep 20 2017, 05:07 PM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th November 2017 - 07:24 AM