Form based Parameter queries and tri-state tick boxes

[Dale Walker]

I've created a query that gets parameters from a few fields on a form,
one of which is a tri-state tick box linked to a binary field on the
query.

What I'm trying to do is get the query to select data dependent on the
state of the tickbox, ie,
just true details on the query if the form tickbox ticked,
just false details on the query if the form tickbox unticked,
all details on the query if the form tickbox is grayed out (Null).

I can get the form/query to work if the tick box is ticked or unticked
but how can I get the query to ignore the parameter if it's Null.

Or am I going about this the wrong way?

 

[Brendan Reynolds]

By 'binary' do you mean a Jet Boolean (Yes/No) field? Null values are
implicitly converted to False when stored in these fields. To use the Triple
State property, use an Integer field instead.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Dale Walker]

By 'binary' do you mean a Jet Boolean (Yes/No) field? Null values are
implicitly converted to False when stored in these fields. To use the Triple
State property, use an Integer field instead.

Hmm, that doesn't seem to be born out when I actually attempt to do
run the query. In fact it does seem to attempt a 'NULL' as I get no
results at all whereas a FALSE returns several records. However, I
require the query criteria to be 'NOT NULL' so that it would pick up
everything.

 

[John Spencer (MVP)]

One method that might work.

Field: YourBooleanField
Criteria: Forms!FormName!FormControl OR Forms!FormName!FormControl Is NULL

 

[Brendan Reynolds]

I didn't follow that, but if you doubt my statement about Null values and
Jet Boolean (Yes/No) fields, try the following code, using the Discontinued
field in the Products table in the Northwind sample MDB ...

Public Sub AssignNullToBool()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
db.Execute "UPDATE Products SET Discontinued = Null WHERE ProductID = 1"
Set rst = db.OpenRecordset("SELECT Discontinued FROM Products WHERE
ProductID = 1")
Debug.Print "Is it Null? " & IsNull(rst.Fields("Discontinued"))
Debug.Print "Is it False? " & (rst.Fields("Discontinued") = False)
rst.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Dale Walker]

I didn't follow that, but if you doubt my statement about Null values and
Jet Boolean (Yes/No) fields, try the following code, using the Discontinued
field in the Products table in the Northwind sample MDB ...

Public Sub AssignNullToBool()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
db.Execute "UPDATE Products SET Discontinued = Null WHERE ProductID = 1"
Set rst = db.OpenRecordset("SELECT Discontinued FROM Products WHERE
ProductID = 1")
Debug.Print "Is it Null? " & IsNull(rst.Fields("Discontinued"))
Debug.Print "Is it False? " & (rst.Fields("Discontinued") = False)
rst.Close

End Sub

Ah but, I'm not looking for Null value in a Jet Boolean field. I'm
trying to get a tri-state tick box (YES/NO/Null) to do one of the
following...

select records of ticked entries in a Jet Boolean field if true
select records of unticked ticked entries in a Jet Boolean field if
false.
select all records if NULL.

The issue is about tick boxes and using the returned NULL value so
that I can somehow get a parameter query on a Boolean field to select
all records.

 

[Brendan Reynolds]

Yes, but you said that this check box was *bound* to a 'binary' field.

It is certainly possible that I could be wrong about the cause of your
problem. Why not find out for sure? It would only take a few minutes to add
an Integer field to the table, change the control source of the check box to
point to that integer field, and see if that makes a difference.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Dale]

Cool. Did the job. Thanks.

One method that might work.

Field: YourBooleanField
Criteria: Forms!FormName!FormControl OR Forms!FormName!FormControl Is NULL

 

[Dale]

Yes, but you said that this check box was *bound* to a 'binary' field.

Actually, I said the form tick box was 'linked' to a 'binary' field via a
parameter query.

It is certainly possible that I could be wrong about the cause of your
problem. Why not find out for sure? It would only take a few minutes to
add an Integer field to the table, change the control source of the check
box to point to that integer field, and see if that makes a difference.

No need. Johns answer did the trick.

You're assuming I'm trying to select NULL values from a table/query but in
fact I'm trying to
reorganise a form tickbox's NULL result to get a select query to select all
values.

In any case passing a NULL value to a select query field relating to
a boolean field does not yield the same results as passing a FALSE value.

I think my perhaps badly worded question may have led you down the wrong
path.