Triple Staee Check box in A2003

[Peter]

Has anyone used a triple state checkbox in A2003?
I find that when I change the checkbox property to triple state it then
effectively locks the checkbox in its null state.

Peter

 

[Allen Browne]

Yes, this is one of the bad side-effects of the themed controls in A2003.

You can work around the problem by turning off themed controls under:
Tools | Options | Forms/Reports
or by setting your Windows XP themes to "Classic".

 

[Peter]

Thanks Allen. I think there's more to it than this though, because I can
replicate the malfunction on 2 Win2000 machines. One of these has A2000 and
A97 and the same malfunction exists in these versions. So maybe Off2003 is
installing something to do with screen behaviour which goes beyond its own
direct scope
I have tried your fix and I can't get any different behaviour.

Thanks

Peter

 

[Peter]

It's rather more basic than I imagined.
Access lets you set triple state for an mdb file but this just can't work
can it as the yes/no field only has 2 states.
If you use SQLServer bit field as the source then triple state operates
correctly in data terms. There is still an issue of whether you can
distinguish Null and false but that's something else entirely.

Peter

 

[Allen Browne]

Okay, that's correct.

The Yes/No field type in JET does not support Null, so the triple-state only
works if the control is unbound, or you can bind it to an Integer type field
so it can be Null.

 

[JohnTee]

hi guys. Can I throw in a circumstance where a Null value result can
make sense for a Yes/No field? I have a Form/Subform thing going on --
Parent form (and its table) is called 'Employees'. Child table is
called 'Life'.
Parent = list of my employees.
Child = those who have elected Life Insurance coverage, with a
Yes/No field for Enrolled, along with other data fields.

On the Employees form, I have a subform that shows me the data from the
Life table. Not every Employee has a Life record.

In Access 2003 (as opposed to Access 2000), the Enrollment checkbox is
now showing greyed out if a Life record was not found. (And the logic
is also triggering a Premium Calculation because it thinks the field
has something in it, when it really returned no child record.)

My desired result (which was working in A2000) is to have both No
values and Null values leave the checkbox blank.

Any thoughts?

Interestingly, if I include 2 checkboxes on the form, both linked to
the Enrollment field, with one checkbox's TripleState set to Yes and
the other to No, they act the same...

As I said, my logic for Premium Calc is also being triggered in A2003
(not in A2000)... that logic is something like:
"If (Not Me!Enrolled) Then do the calc"
Any idea why these 2 things are working differently in A2003?

Thanks for any help/insight you can provide!

John

Okay, that's correct.

The Yes/No field type in JET does not support Null, so the triple-state
only
works if the control is unbound, or you can bind it to an Integer type
field
so it can be Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"Peter" [email protected] wrote in message
It's rather more basic than I imagined.
Access lets you set triple state for an mdb file but this just can't
work
can it as the yes/no field only has 2 states.
If you use SQLServer bit field as the source then triple state
operates
correctly in data terms. There is still an issue of whether you can
distinguish Null and false but that's something else entirely.

Peter


"Peter" [email protected] wrote in message
news:%[email protected][/i][/i]

[/QUOTE]

 

[Allen Browne]

John, I agree that there are *many* situations where Null is a desirable
value to have in a yes/no field. The problem is not the idea, but the
practice, i.e. Microsoft chose to cripple JET (Access) so it does not
support Null in a y/n data type.

It's not a major limitation though: not too hard to use a Number field (size
Integer) instead of a yes/no field, and use a check box for the interface.
Numbers do accept null, so if you turn on the check box's TripleState
property, it cycles through the values -1 (true), 0 (false), and null.

 

[JohnTee]

Thanks Allen. Do you have info on why this would work fine in Access
2000, but not in 2003? Did they change JET, or change the Triple-State
property, etc?

Also, any thoughts on why the "Me!Enrolled" phrase that I use now
evaluates differently, if a Null is encountered? Any suggestion on an
easy way to modify that phrase to properly accommodate Nulls?

Thank you!
John

John, I agree that there are *many* situations where Null is a
desirable
value to have in a yes/no field. The problem is not the idea, but the
practice, i.e. Microsoft chose to cripple JET (Access) so it does not
support Null in a y/n data type.

It's not a major limitation though: not too hard to use a Number field
(size
Integer) instead of a yes/no field, and use a check box for the
interface.
Numbers do accept null, so if you turn on the check box's TripleState
property, it cycles through the values -1 (true), 0 (false), and null.

[/QUOTE]

 

[Allen Browne]

Not sure what's going on there, because A2003 uses the same engine as A2000
(i.e. JET 4.)

For suggestions on handling Nulls, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html