Return value and null from query

[bschulze]

I have a query that is evaluating criteria and returning values. The evaluation is working properly but I'm having a hard time getting the results I desire. The field in the query is a Text field. I usually get an complication error unless I use the Like clause. The list of criteria I've tried never returns the null values, only the items that contain data if at all. When I drop the is null from the true statement and put it on a separate line it works, problem is as expected in that case, it picks up nulls for both the true and false conditions.

Query Criteria:
IIF(DMax("P","tblP")=[Forms]![frm1].[cbo1],DMax("P","tblP") or is null,[Forms]![frm1].[cbo1])

Simplified:
IIF(a=b,a or is null,b)


What I've tried so far:
IIF(a=b,a or is null,b)
IIF(a=b,a and is null,b)
Like IIF(a=b,a or is null,b)
Like IIF(a=b,a and is null,b)
IIF(a=b,Like a or is null,b)
IIF(a=b,Like a and is null,b)



Any thoughts would be greatly appreciated, I think I'm just overlooking something easy.

 

[mrmmickle1]

Can't quite make out what your trying to do but it sounds like the issue your having is related to the known limitations of the IIF() (Immediate If Function)

[FONT=wf_segoe-ui_semibold]Remarks[/FONT]
[FONT=wf_segoe-ui_semibold]IIf always evaluates both [FONT=wf_segoe-ui_semibold]truepart[/FONT] and [FONT=wf_segoe-ui_semibold]falsepart[/FONT], even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating [FONT=wf_segoe-ui_semibold]falsepart[/FONT] results in a division by zero error, an error occurs even if [FONT=wf_segoe-ui_semibold]expr[/FONT] is[FONT=wf_segoe-ui_semibold]True[/FONT].

https://support.office.com/en-us/article/IIf-Function-32436ecf-c629-48a3-9900-647539c764e3

May be easier to create a UDF with simple IF THEN logic....[/FONT]

 

[bschulze]

I tried the UDF below, still no go. If I only pass the text value from strMaxPer or strCurPer the query filter works fine, however once I add the "Or Is Null" if fails. Also, when I try just an "Is Null" it also fails. I'm not aware of this being a restriction. Any assistance would be great.

Dim strMaxPer, strCurPer, strFilterCriteria As String

strMaxPer = DMax("P", "tblP")
strCurPer = [Forms]![frm1].[cbo1]

If strMaxPer = strCurPer Then
    strFilterCriteria = """" & strCurPer & """ Or Is Null"
Else
    strFilterCriteria = strCurPer
End If

ChrBckQryFilter = strFilterCriteria

End Function

 

[james_lankford]

 strFilterCriteria = " '" & strCurPer & "' Or strCurPer Is Null "

edit:
wait, that's not right either

you have to get your field name in there with the is null
something like this

function ChrBckQryFilter() 
  
  Dim strMaxPer as string, strCurPer as string, strFilterCriteria As String
  
  strMaxPer = DMax("P", "tblP")
  strCurPer = [Forms]![frm1].[cbo1]
  
  If strMaxPer = strCurPer Then
     strFilterCriteria = " my_field = '" & strCurPer & "' or my_field Is Null " 
  Else
    strFilterCriteria = " my_field = '" & strCurPer &"' "
  End If
  
  ChrBckQryFilter = strFilterCriteria


End Function

 

[bschulze]

James, did you get the query to accept the "Is Null". I've tried your suggestion without success, I've even scaled the criteria back to only the "Is Null" condition and it still will not be accepted through the UDF. It executes successfully in the Query Designer as "Is Null". I cant find much on this topic. Let me know if you have successfully implemented this concept with the Is Null function, thank you.

 

[xenou]

Give an example of data and the expected result (or unexpected result, as the case may be). The way you are writing IIF() looks very suspicious here and frankly it's hard to tell what you actually want the formula to do.

 

[bschulze]

Sorry, I dont think the HTML maker works on 64 bit systems. In the example below if the combobox and the max of tblP are equal it should return the max and null, if not equal it should return just the value in the combobox. Hope that helps.

cbo = 2015 - Return value ID's 1,2,3,5
cbo = 2016 - Return value ID's 4,6,7,8,9

'All year values are text not numbers

tblP
P
2015
2016


Query Data
ID P
1 2015
2 2015
3 2015
4 2016
5 2015
6
7 2016
8
9 2016

 

[xenou]

if the combobox and the max of tblP are equal it should return the max and null

What do you mean by it should return the max and null? Can you post some of your actual query and what results you want? For instance, 2015 has no nulls, so do you not want nulls because there are none, or do you not want to see nulls for 2015 even if there were some? It's not clear what this is really about. Also, why return a max when you already know what it is (the value in the combobox)?

 

[bschulze]

Sorry, I'll try to be clearer. New set of data below. If the combobox is the last period entered, then I want to see all records with that period and the null values, if the combobox is not the last period entered, I only want to see the records with the period (no nulls), hence the dmax test. Really simple, if evaluates to true provide period and nulls, if evaluates to false only provide period.

When hard-coded the below SQL statement works for the "is null", when its included in the iif statement it will not work

SELECT * FROM tbl1 WHERE (((tbl1.)="201605" Or (tbl1.) Is Null));



IIf(DMax("P","tblP")=[Forms]![frmTest1].[cboper],[Forms]![frmTest1].[cboper] Or Is Null,[Forms]![frmTest1].[cboper])


ID P
1 201601
2 201602
3 201603
4 201604
5 201605



ID b
2 201601
3 201601
4 201602
5
6 201602
7 201602
8
9 201603
10 201603
11 201603
12 201603
13 201604
14 201604
15 201604
16 201604
17 201604
18 201604
19 201605
20
21 201605
22
23 201605
24 201605
25 201605
26 201605
27 201605
28
29


Desired results by ID if
cboPer = 201601 - 2,3
cboPer = 201602 - 4,6,7
cboPer = 201603 - 9,10,11,12
cboPer = 201604 - 13,14,15,16,17,18
cboPer = 201605 - 19,21,23,24,25,26,27 and 5,8,20,22,28,29

 

[xenou]

Okay, this works I think:

SELECT *
FROM 
	tbl1
WHERE 
	nz(tbl1.b, DMax("P","tblP")) = Forms!frmTest1.cboper

I.e., we make the nulls equal to DMax("P","Tbl") and that takes care of it.


Here is another way to do this:

SELECT *
FROM 
	tbl1
WHERE 
	tbl1.b = Forms!frmTest1.cboper

UNION ALL
	
SELECT *
FROM 
	tbl1
WHERE
	tbl1.b IS NULL 
	AND Forms!frmTest1.cboper = DMax("P","tblP")