Want to pass "Is Null" from Function to Query

[chuckcoleman]

I'm feeling a little brain dead right now so I could use some help. I have a query that has a text field and I want the criteria for that text field to be "Is Null". I have a Function that looks like:

Function fHasEmail() As Variant
If CurrentProject.AllForms("Client Form").IsLoaded = True Then
fHasEmail = "*"
Else
fHasEmail = "Is Null "
End If
End Function

It tests if a form is loaded and if it is, the criteria is "anything", i.e. no criteria, all values. That works. If the form is not loaded I want "Is Null" passed to the criteria line of the query so that the query selects records where the field is null.

Any help will be appreciated.

Thanks,

Chuck

 

[Ranman256]

In queries:
Where [field] =null

The function is not used in criteria.

 

[chuckcoleman]

Thanks, but I'm not sure what you mean. I know how to set a criteria in a query that is null. The function changes the criteria based on if a certain form is open or not. Therefore, don't you have to put that function's name in the criteria line in the query?

 

[plog]

I wouldn't apply criteria to the text field at all. I would pass it's value to the function and then let the function return True or False to let you know if you should include the record or not.

Function IncludeRecord(in_TextValue) As Boolean
   ' determines if record is to be included based on form and in_TextValue

ret =  CurrentProject.AllForms("Client Form").IsLoaded 
      ' return value, default status of Client Form

if ret = False AND IsNull(in_TextValue) Then ret= true
    ' if form not loaded and in_TextValue is Null then include record

IncludeRecord = ret

End Function

Then in your query, you make a new field based on that function and set the criteria beneath it to True:

IncludeRecord: IncludeRecord()

 

[chuckcoleman]

Plog, thank you, it worked perfectly. Take a second if you have it and explain why your method worked and what I was trying didn't. The code I posted did work on a Y/N field; it just didn't work on the text/Email field. Help me get smarter and understand if you have a minute.

Thank you again,

Chuck

 

[plog]

Ultimately a query is a string of SQL:

"SELECT Field1 FROM YourTable WHERE Field2 = 'Some String' ORDER BY Field1"

If you were directly constructing that string (like in a VBA) your method could have worked. But when you try to do it through the Design Viewer you aren't directly building a string, the Design Viewer is. What you put in the Design Viewer gets translated (open up a query in SQL view and you will see a ton of unnecessary parenthesis, full table references that are unneeded etc.).

When you use the return value of a function in the criteria section, it treats that value as the type of the field it is applied to. So, whatever your function returned (* or Is Null) was put into single quotes because it was part of a text comparison. Those quotes make the value a string and not values unto themselves:

"...WHERE Field2 = '*'..."
"...WHERE Field2 = 'Is Null'..."

It made the query only include records where Field2 literally was an asterisk or the phrase "Is Null". You wanted this:

"...WHERE Field2 = *..."
"...WHERE Field2 Is Null..."

Your method never had a chance because the Design Viewer included those single quotes around your return value. In fact the asterisk one still wouldn't work, even without those quotes--if you don't want to apply criteria to something, you just don't include it in the WHERE clause.

 

[chuckcoleman]

Plog, Thank you, I will internalize this tonight. I really appreciate your help.

Take care,

Chuck