?
Solved

Check box and filter form

Posted on 2011-02-28
16
Medium Priority
?
586 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 2
16 Comments
 
LVL 14

Assisted Solution

by:pteranodon72
pteranodon72 earned 668 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1332 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
 

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 1332 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

777 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