query criteria true/false field

[supmktg]

I'm trying to limit the records on a subform via an option group selection. The group has 2 options: optionTrue (option value = 1) and optionFalse (optio value = 2). I have the following code in my query criteria of the true/false field.

 IIf([Forms]![FrmHome]![frameProcessed]=1,-1,0)

The false part works, but the true part doesn't. I've tried many variations using true/false, using checkboxes, etc. and nothing works.

The database is SQL Server if that matters.

Any suggestions?

Also, is their a way to have an option for True or False or ALL?

Thanks,
Sup

 

[wazz]

...I have the following code in my query criteria of the true/false field...

i haven't used option groups much but i think you want to apply some code to the whole option group, i.e. you don't apply code to each control in the group, but the entire group.

something like,
OptionGroup_AfterUpdate...
if me.optiongroup = 1 then
me.recordsource = "qry1"
else...

i think you want to change the record source depending on the selection. so, yes you could add another "All" control and change the record source again if that is selected.

 

[EMP]

I have the following code in my query criteria of the true/false field.

IIf([Forms]![FrmHome]![frameProcessed]=1,-1,0)


The false part works, but the true part doesn't. .............

The database is SQL Server if that matters.

Try changing -1 to 1.
If I remember correctly, unlike Access, in SQL Server true is 1, not -1.

^

 

[supmktg]

Emp,

The value for true in the table is -1. I tried changing the value to 1 and it shows records with a false value?

Wazz,

The afterUpdate of the option group requeries the subform which has the query as it's recordsource:

Private Sub frameProcessed_AfterUpdate()
Me.sfrmPatientQue.Requery
End Sub

.

Even if I eliminate the IIF statement and change the criteria to just plain -1, the query returns no records ( and there are records that are true ) while the IIF statement or just plain 0 does return the records that are false.

Any ideas?

Thanks,
Sup

 

[wazz]

i'm not quite getting it. i'd have to see it up close. sry.

 

[supmktg]

Emp, you were right.

I tried changing the selection control to a combo box with the values -1 and 0. With the subforms query criteria for the true/false field as:
[Forms]![FrmHomeScheduling]![cboTrueFalse]

It didn't work so I tried:
Like [Forms]![FrmHomeScheduling]![cboTrueFalse]

and it worked.

Then I changed the combo box value from -1 to 1 (without the Like) and it worked!

I'm still confused as to why the true/false field displays true as -1 in the table if it is actually 1, but I guess I should be happy that I got it to work.

Thanks,
Sup