Is Not Null to query, not as string

[jalgier]

Greetings,

My question is how do I get Is Not Null as the criteria in a query, but not as a string? The criteria on one of my query fields is based on a field on a form. The user has three choices North, South and Is Not Null. The problem is the "Is Not Null" goes to the query as a string literal, and I don't get the results I am looking for (basically I get no results - understandably). How do I write the VBA to say txtFieldA = "Is Not Null" so that it performs correctly on the query?

thanks

 

[Jacob Mathai]

In VBA, try this :

If IsNull(txtfielda) = true (or false)
your code here
End if

In Query try this :


........ where columnA is not null
or
where columnA is null

 

[jalgier]

Actually, the criteria is the field value from the form itself. The specific field in the query looks like this...

Field: Region
Table: MantleTicket
Total: Group By
Sort:
Show:
Criteria: [Forms]![frmViewInv]![txtVariable]

txtVariable accepts the value of cboRegion (North, South, Either). If cboRegion value is "Either", then txtVariable is assigned "Is Not Null".

If cboRegion = "Either" Then
txtVariable = "Is Not Null" '**here is the problem, I only know how to pass the value as a string**
End If

However, the "Is Not Null" gets passed as a literal, and the query does not perfom correctly. I need "Is Not Null" to be passed to the query so it reads it properly, not as a literal string.

 

[The_Doc_Man]

The way to do this (simplified) is, in query design view, add an extra column that contains the Expression: IsNull([field]) - and put the criterion "True" or "False" as the condition. If you don't want to see the T or F, just uncheck the box that shows whether the result is visible in the query's associated recordset.

If I read your question correctly, you would have three criteria lines.

The first would be in the first criterion row under [Field] and would be ="North". The second would be in the second criterion row under [Field] and would be ="South".
The third would be in the third criterion row under IsNull([Field]) and would be =False.

At least, I THINK that's what you asked for.