Solved

Check box and filter form

Posted on 2011-02-28
16
562 Views
Last Modified: 2012-05-11
Experts,

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
0
Comment
Question by:pdvsa
  • 9
  • 5
  • 2
16 Comments
 
LVL 14

Assisted Solution

by:pteranodon72
pteranodon72 earned 167 total points
ID: 35000428
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).

Thanks,
pT72
0
 

Author Comment

by:pdvsa
ID: 35000798
The field type of [ExpiredYN] is valuelist with "Yes";"No" as the row source.  I hope that is the answer to your question.
0
 

Author Comment

by:pdvsa
ID: 35000814
Oh, it is text (i am pretty sure). It is not a yes no field even tbough it says YN...
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 333 total points
ID: 35001061
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.

JeffCoachman
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35001151
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".

HTH,
pT72
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35001259
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....
0
 

Author Comment

by:pdvsa
ID: 35001272
Whoa... I will check this out and revert.  Thanks for the advice Jeff.  
0
 

Author Comment

by:pdvsa
ID: 35002109
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:pdvsa
ID: 35002190
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.
Thx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35002743
OK

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.
0
 

Author Comment

by:pdvsa
ID: 35002897
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
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 333 total points
ID: 35003779
<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"


JeffCoachman



0
 

Author Comment

by:pdvsa
ID: 35010604
Jeff:
<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.    
0
 

Accepted Solution

by:
pdvsa earned 0 total points
ID: 35010625
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35011071
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.
0
 

Author Closing Comment

by:pdvsa
ID: 35045717
thanks for the help
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now