Criteria Checkbox

  • Thread starter Thread starter rob p
  • Start date Start date
R

rob p

One field in my main table is delete - yes / no type. I made a query
including that field so I could use delete as a filter for a report.

I want my report to have the option of printing ALLrecords or ONLY THOSE NOT
deleted. How do I code this in criteria for "delete". I found out 0 will
give me all not checked. I can't get all including checked. And having to
put in 0 or -1 seems crude. Isn't there a more user friendly way of working
with Yes/No parameter?
thanks.
 
One field in my main table is delete - yes / no type. I made a query
including that field so I could use delete as a filter for a report.

I want my report to have the option of printing ALLrecords or ONLY THOSE NOT
deleted. How do I code this in criteria for "delete". I found out 0 will
give me all not checked. I can't get all including checked. And having to
put in 0 or -1 seems crude. Isn't there a more user friendly way of working
with Yes/No parameter?
thanks.
Click to expand...

Delete is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

May I suggest changing it to 'Selected' which is a more accurate name
for a Yes/No field anyway.

After you change the field name, you can use
Like IIf(IsNull([Which ones?]),"*",[Which ones?])

If you leave the prompt blank, you will get all the records. If you
enter -1 you will get just the ones that are checked. If you enter 0
you get just the ones that are not checked.
 
fredg said:
Delete is a reserved Access/VBA/Jet word and should not be used as a
field name.
Click to expand...

But you probably won't get into any trouble if you do use it as a field
name, since it's normally going to be qualified by the Table name, or
enclosed in quotation marks or brackets, etc. And the name [Delete] is
suggestive enough of what it's intended to do that there shouldn't be
much likelihood of losing track of its meaning. (Remembering what a
name means is a very important part of maintaining a database!)

[...]
After you change the field name, you can use
Like IIf(IsNull([Which ones?]),"*",[Which ones?])

If you leave the prompt blank, you will get all the records. If you
enter -1 you will get just the ones that are checked. If you enter 0
you get just the ones that are not checked.
Click to expand...

The following Query will list the deleted records:

SELECT MyMainTable.MyMainTableID, MyMainTable.Delete
FROM MyMainTable
WHERE (((MyMainTable.Delete)=Yes));

or you can list them all by erasing the WHERE clause, like this:

SELECT MyMainTable.MyMainTableID, MyMainTable.Delete
FROM MyMainTable;


To display the Table values as you'd like, you can open your Table in
Table Design View, select the [Delete] field, and click on the Lookup
tab. Change the "Display Control" property from "Check Box" to "Text
Box". Then click on the General tab and change the "Format" property to
"Yes/No".

Doing this should make your Table look better. (But don't be fooled --
be aware that the values stored in that field in the Table will still be
-1 or 0.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Back
Top