Problem Dealing With Nulls Passing Parameters To Query 1

[xweyer]

I have created a form that presents the user with a number of drop downs, which pass the user’s selections to a query as parameters.

The problem I have in dealing with null entries. The goal is that when the user leaves a given dropdown blank the query will return ALL data relevant to that field.

At first I thought I could do this simply with a statement like this

IIf(IsNull([Forms]![CUSTOM Frm]![MyField]),,[Forms]![CUSTOM Frm]![MyField])

Where I expected the query would leave the criteria line alone (blank) if the field was Null and use the value stored in it if it was not Null but this statement fails because Access interprets the double commas as a syntax error.

So next I tried explicitly entering a statement between the two commas that would be equivalent to leaving the criteria line blank.

Since placing the statement “Like "*" Or Is Null” in the criteria field returns the desired information (All entries) I expected that I could create a criteria statement something like this.

IIf(IsNull([Forms]![CUSTOM Frm]![MyField]), Like "*" Or Is Null ,[Forms]![CUSTOM Frm]![MyField])

Unfortunately, Access does not accept this as it is “too complex to be evaluated”. It appears to me that the complexity arises because Access refuses to treat the “Like "*" Or Is Null” section as simple text (even if it is enclosed in quotes).

I fail to see what is so complex about

1. Examining the contents a specific field
2. If it is null use a simple OR statement as criteria (or just leave the default criteria unaltered).
3. If it is not null use the contents of the field as criteria.

But that aside, I did find a means of accomplishing what I want on a limited basis by using the following statement which I found on the Web.

[Forms]![CUSTOM Frm]![MyField] Or [Forms]![CUSTOM Frm]![MyField] Is Null

While this works fine for a small number of fields it fails when larger numbers of fields are involved. The reason appears to be that while Access allows the statement to be entered as it appears above, it actually executes it by altering the design of the query. Basically what happens is new fields and rows of criteria are appended to the original query and Access breaks the statement into smaller components utilizing both the original field and the newly created ones to execute it.

When I used this statement as the criteria statement for the 20 fields that I need to work with I ended up with a query that had twenty new fields plus over 100 lines of criteria. Talk about complexity! Needless to say the query bombs.

I know I could work around this by eliminating nulls entries but I really don’t want to go that route.

Does anyone know of a statement that would be functionally equivalent to either

IIf(IsNull([Forms]![CUSTOM Frm]![MyField]), Like "*" Or Is Null ,[Forms]![CUSTOM Frm]![MyField])

Or

IIf(IsNull([Forms]![CUSTOM Frm]![MyField]),,[Forms]![CUSTOM Frm]![MyField])

that Access will accept? Or failing that of a Visual Basic means of accomplishing the same thing? Thanks.

 

[dhookom]

If I want to accept Nulls from a form control against a field name empLastName, I use:
Nz(Forms!frmA!txtLastName, [empLastName])
This solution assumes the field will not contain null values. If Nulls are to be included, then use something like:
WHERE empLastName & "" = Nz(Forms!frmA!txtLastName, [empLastName] & "")

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[cmmrfrds]

Is this an Access database that you are going against or something like SQL Server?

Are you generating a query based on multiple inputs from the Form. Does the Select list change or just the criteria in the query? Can you show a brief example. What is the maximum number of select criteria that you are adding.

 

[xweyer]

This is an Access database. There are a total of twenty fields in the query for which I want the form to generate criteria. The select list does not change, only the criteria.

Basically in all cases what I want to occur is for the user to have either the option of being able to select a specific critera from a drop down list (in which case only entries which match that selection are returned) or to leave the field blank (in which case no critera is applied to that field and all entries are returned).

An example would be in the field "brand" the user could select a given brand (Compaq, Dell, etg.) or juat leave the field blank and have all entries returned.

 

[dhookom]

xweyer,
Since you responded in this thread, I assume you read my post. However, you didn't mention whether you tried it and if it worked. If it didn't work, then would you have some symptoms?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[cmmrfrds]

I would build the sql statement dynamically. For example.
Dim mySQL as Variant

mySQl = "select fld1, etc.... from mytable "
mySQl = mySQL & "where 1 = 1 "
'- text field
If not isnull([Forms]![CUSTOM Frm]![MyField]) then
mySQl = mySQL & "and myfield1 = " & chr(34) & ([Forms]![CUSTOM Frm]![MyField]) & chr(34) & " "
End If
'- numeric field
If not isnull([Forms]![CUSTOM Frm]![MyField2]) then
mySQl = mySQL & "and myfield2 = ([Forms]![CUSTOM Frm]![MyField2]) "
End If
etc........

 

[xweyer]

Sorry to have taken so long to get back to this thread. I had some major things come up that pulled me away from working on the problem. I returned to it about a month ago and have been trying to bang away at it ever since whenever I have a few free minutes.

Dhookum – your information was very helpful. I saw your post and and cmmrfrds at the same time and hadn’t got to try your suggestions when I responded to cmmrfrds questions.

The first expression worked perfectly for fields without any nulls as you said that it would. Unfortunately some of the fields I’m working with do have blanks and I wasn’t able to construct a criterion from your second example that worked for me. At first I thought it was just my failed attempts to get the syntax right but after a long struggle I’m convinced that something else is going on.

Here’s the problem I’ve run into that doesn’t make any sense to me. To illustrate I have a field [Model] that has some blank entries. If I don’t enter any criteria at all for that field in a query I turn up 1348 rows of which 24 are blank which is correct. If I enter Like “*” or [Model] or Is Not Null as the criteria I get 1324 rows which is exactly what I would expect. Here’s where things get funky, if I then enter either “” or IsNull as criteria no rows are returned!

So if these 24 entries aren’t “null” and also aren’t “not null” what the heck are they?

Cmmrfrds – I’m not savvy enough to follow your suggestion. Would I be entering this code into a module or somewhere else?

Anyway a belated thanks to you both for your help.

 

[dhookom]

Null does not equal Null. You have to live with it. That is why I suggested concatenating "" to the field:
Field: NewModel: [Model] & ""
Criteria: Like [Forms]![CUSTOM Frm]![MyField] & "*"

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[xweyer]

Aha! The dawn breaks over Marblehead! No wonder I was having trouble. I was stuck in logic rut. It never occurred to me that something could not be null and not be not null at the same time. So I wasn’t applying your initial suggestion correctly.

Leave it to Bill Gates to introduce quantum logic to databases.

Your last post cleared the fog. Thanks again.

(So criteria-wise speaking, without creating the expression concatenating the empty string to the Model field contents, those 24 records are pretty much in a state that cannot be named.)

 

[dhookom]

The "state" of Null is in some amount of flux at the moment. When you think about Null consider its value as "unknown". Does one unknown value equal another unknown value? No

This is just one reason why I try to avoid placing references to form controls in queries that are used as record sources for forms or reports. I prefer to use the where clause in the OpenForm or OpenReport method.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[xweyer]

Thanks for the tip. It should save me some grief. My thinking on nulls was shaped by working with Rbase back in the day when men were men and nulls were nulls. "Is Null" in an Rbase query would have pulled up the records I was expecting to see. Kinda amazing how that wrong assumption kept me looking in the wrong direction.

What I was trying to accomplish with the form was a simple way of a user to select criteria from a list and generate a quickie query or report on the fly.

I'm not a database tech (big surprise there) just an IT jack of all trades. I got the form idea off the web.

Would the OpenForm or OpenReport method you mentioned be another way of doing this? I'm not familiar either.

 

[PHV]

xweyer, the expression (Null = Null) is always false, but (Null Is Null) is true, even in access.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

The OpenReport method would build a where clause on-the-fly:

Dim strWhere as String
strWhere = "1=1 "
If not IsNull(Me.txtStart) Then
   strWhere = strWhere & " And SaleDate >=#" & _
      Me.txtStart & "# "
End If
If not IsNull(Me.txtEnd) Then
   strWhere = strWhere & " And SaleDate <=#" & _
      Me.txtEnd & "# "
End If
If not IsNull(Me.cboBrand) Then
   strWhere = strWhere & " And [Brand] =""" & _
      Me.cboBrand & """ "
End If
DoCmd.OpenReport "rptSales",acPreview, , strWhere

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]