Solved

Check box and filter form

Posted on 2011-02-28
16
570 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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
 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

776 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