Check box and filter form


I have a report with a filter form.
I need to inlcude or exclude records based on chkExpiredYN is checked or not checked.

The underlying table is tblLetterOfCredit and the qry field is [ExpiredYN]
[ExpiredYN] is a value list with "Yes";"No" as rowsource.

If the chkExpiredYN is checked on filter form then inlcude ALL records with "Yes"
If chkExpiredYN is Null then exclude records with a "Yes" under [ExpiredYN] but include Nulls too

How would I  write this in the SQL?
(you can make up a form name for this filter form)

I am really lost on this.  I seem to not be able to qry the table for records that are Yes or No either for this [ExpiredYN] field.  I type in ="Yes" but the qry includes all records and not just then ones with "Yes".  I also use <>False and it does return records with a "Yes" but if I use <>True then it returns the same  records as it did for <>False.  I dont understand this.

I dont know if a checkbox is recommended under the filter form or a Yes No field.  

thank you
pdvsaProject financeAsked:
Who is Participating?
pdvsaConnect With a Mentor Project financeAuthor Commented:
I guess it can default to 0 but it is not best as it does not look right to have a 0 in a YN field.
pteranodon72Connect With a Mentor Commented:
I'm unclear. What is the field type of ExpiredYN in tblLetterOfCredit? The approach varies whether this is Text (as it sounds according to the combobox) or Yes/No (as it may be).

pdvsaProject financeAuthor Commented:
The field type of [ExpiredYN] is valuelist with "Yes";"No" as the row source.  I hope that is the answer to your question.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

pdvsaProject financeAuthor Commented:
Oh, it is text (i am pretty sure). It is not a yes no field even tbough it says YN...
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
This is the issue with Access allowing Y/N fields to be null.

You should really set a default value of: 0 for this filed in the table, to avoid it ever having nulls.
Also make sure the Triple state property of the corresponding Form control is set to No as well.

Then run an update query to convert all existing nulls to Zeros:
UPDATE YourTable.YourYesNoField = 0
WHERE YourTable.YourYesNoField Is Null;

Now you should only ever have Yes (-1 in Access) or No (0) in your table data.
Assuming your filter is built correctly, the report and form selections should work fine.

OK. You can be sure of the table field's Type by closing all forms and the opening tblLetterOfCredit in Design View.

To filter a report according to a text field you need to compare the value of the record to a literal "Yes". Even though you've what can be entered into that field with the combobox, it is possible that some other text could be in there. You can't compare the value stored to True or False.

In a query, you'd show all records by using the SQL:
SELECT * FROM tblLetterOfCredit

you'd show just the expired records by using the SQL:
SELECT * FROM tblLetterOfCredit WHERE [ExpiredYN]="Yes"

and you'd show non-expired records and nulls using
SELECT * FROM tblLetterOfCredit WHERE Is Null [ExpiredYN] Or [ExpiredYN] = "No"

To change this syntax to look at a filter form:

SELECT * FROM tblLetterOfCredit WHERE [ExpiredYN]=[Forms]![frmFilter]![cmbExpired]

I would use a combo box with values "Yes;No" on the filter form rather than a checkbox. A checkbox can either be True / -1 or False / 0, not "Yes".

Jeffrey CoachmanMIS LiasonCommented:
Yes, pT72 brings up a good point...
Make sure you filter for 0 and -1 (in MS Access) to be sure to get the correct raw numerical values behind yes/no fields in MS Access....
pdvsaProject financeAuthor Commented:
Whoa... I will check this out and revert.  Thanks for the advice Jeff.  
pdvsaProject financeAuthor Commented:
Jeff, i am not sure if you read that the field is not a Yes No field.  It is a text field.  it is text cbo with rowsource Yes No.  Would what you said above apply knowing this?  Thx
pdvsaProject financeAuthor Commented:
I figured out what is problem here:  i have zero lenght string set to NO in the prpty table.  Now, after changing it from NO yto YES i can qry ="Yes" or "No".  

I know this ZLS is tricky stuff.  It is an area i dont want to play with too much but after making the change it seems to work but i dont know if i by allowing ZLS i will run into issues later.  

I did follow Jeff advice to change the Nulls to a 0 but not sure if this is what i was suppose to do as it is a text and not a Yes No field.  

Let me know if i should remove the 0 from the field now and the default to 0.
Jeffrey CoachmanMIS LiasonCommented:

So I mis-read the Field definition.

In any event I see you applied the same principles to your text yes/no field.

FWIW, If it were me, ... I would convert this to a true Y/N filed.

In VBA, it is far easier to reference a number than text.
pdvsaProject financeAuthor Commented:
Jeff, i thought of converting it too.  I think i will have to do this.  I am not sure why i did it like this.  Maybe had something to do the way a true yn was defaulting too.  

One more question: should i default a true YN to 0?  If i do this then i am thinking it might always say 0, which might be confusing to a user.  Not sure if i can default it to "".  I would like for a true YN to default to a BLANK if possible.  

Thank you
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
<One more question: should i default a true YN to 0? >
...IMHO, ... I think that would be best. This is the default in a real Yes/No field.

<which might be confusing to a user.>
Why?, In a real MS Access Yes/No field, all they will see is a blank checkbox...

< I would like for a true YN to default to a BLANK if possible.  >
Then you are defeating the purpose of a real Yes/No field.

In a Real Yes/No field there are ONLY two choices:
True Or False

You can designate these two choices using any terms you like:
Yes      -1      On      True
No      0      Off      False

But there should only be TWO choices...

I can see no real need for a "Blank" value.
(Perhaps you can explain the purpose for this...)

If your experience in Table design make you absolutely positive that this "Third State" is really needed, then perhaps you should stick to text values.
Think about it, a True Yes/No field has only those two values, as the name implies.
If you feel you need more than that then please don't refer to this as a yes/no field.
Simply make it a text filed with three possible values: "Yes", "No", "SomeOtherMysteriousBlankValue"


pdvsaProject financeAuthor Commented:
<which might be confusing to a user.>
>Why?, In a real MS Access Yes/No field, all they will see is a blank checkbox...
If a YN idefaults to 0 i think it does not default to a blank check box?  I thk it defaults to a visible 0

the reason a True YN can be confusing is for a qeustion like this:
" was the company in the database?"

if it says No as a default then one will think NO but in reality it has not been checked and it can be a source of confusion.  This is why a True YN will not work for evry situation and this is why i had it as text.    
Jeffrey CoachmanMIS LiasonCommented:
Then yes, according to all that you have posted on this, a Yes/No datatype will not be appropriate for this situation...

(This is why you should not call it Yes/No)

If this is Yes/No/NoData then simply call it "Answer" and set it as text.
The three values will be: "Yes", "No", "No Data"

Thanks for the clarification.
pdvsaProject financeAuthor Commented:
thanks for the help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.