yes/no field in query

[Song Su]

Access 2003

I want to print label based on select query. The query pull out, for
example, 10 records. I want to use a form for user to check which of these
10 records to print label. My table does not have Yes/No field and I cannot
change design of the table. What are my options for this? Thanks.

 

[fredg]

Access 2003

I want to print label based on select query. The query pull out, for
example, 10 records. I want to use a form for user to check which of these
10 records to print label. My table does not have Yes/No field and I cannot
change design of the table. What are my options for this? Thanks.

Why can't you change the table design? That's what you should do.

The way to do this is to add a Yes/No check box field to the
underlying table.

Then add this check box field to your form.

Code the Form's command button Click event:
DoCmd.OpenReport "LabelReport", acViewPreview

Open the query in Design View. Add the Check box field to the query.
As criteria on the check box field, write:
-1

When you open the form, place a check in whatever records you wish to
print a label for. Click the command button.
Clear all of the check boxes when done.

 

[Song Su]

Thanks for your reply. I'm only allowed to develop front end, linked to
backend at server and I'm not allowed to modify the table structure. IT said
'NO'. Any other options?

 

[John Spencer]

Add a new table to the Access FRONT END.
Table: tblMatches
Field:
fldPK: Autonumber
fldKeyValue: field type is dependent of the key field value
fldPrintThis: Yes/No

In a query, join this table (LEFT JOIN) to your main table
SELECT MainTable.PrimaryKey, tblMatches.fldPrintThis
FROM MainTable LEFT JOIN tblMatches
ON MainTable.PrimaryKey = tblMatches.fldKeyValue

Add any other fields to the SELECT clause that you need to display for the
user to make the selection.

You can base your report on an INNER JOIN of the MainTable and tblMatches
where fldKeyValue is checked.

You can add code that will check all fields or uncheck all fields using
update queries (or a delete query if you want to delete all the records in
tblMatches. This process does have the disadvantage of causing your
database to grow over time, but you can periodically compact the database or
you can use a temporary database to hold the table.

See Tony Toews website
http://www.granite.ab.ca/access/temptables.htm
for an example of temporary database/tables
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks for your reply. I'm only allowed to develop front end, linked to
backend at server and I'm not allowed to modify the table structure. IT
said 'NO'. Any other options?

 

[Song Su]

Thank you. I'll try that.

Add a new table to the Access FRONT END.
Table: tblMatches
Field:
fldPK: Autonumber
fldKeyValue: field type is dependent of the key field value
fldPrintThis: Yes/No

In a query, join this table (LEFT JOIN) to your main table
SELECT MainTable.PrimaryKey, tblMatches.fldPrintThis
FROM MainTable LEFT JOIN tblMatches
ON MainTable.PrimaryKey = tblMatches.fldKeyValue

Add any other fields to the SELECT clause that you need to display for the
user to make the selection.

You can base your report on an INNER JOIN of the MainTable and tblMatches
where fldKeyValue is checked.

You can add code that will check all fields or uncheck all fields using
update queries (or a delete query if you want to delete all the records in
tblMatches. This process does have the disadvantage of causing your
database to grow over time, but you can periodically compact the database
or you can use a temporary database to hold the table.

See Tony Toews website
http://www.granite.ab.ca/access/temptables.htm
for an example of temporary database/tables
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.