Display checkboxes in Query for boolean

[Yitzak]

Have a query that is arregated into crosstab query, it is working
correctly.

select Yes as selected from tblname
or
select true as selected from tblname

Produces -1 ( vba value of True) in the results pane I would like to
see it produce a checkbox with tick in just like I would see if I
displayed a boolean/yes/no field from a table.

Any idea how to force this?

[lyle fairfield]

On Apr 16, 9:57*pm, Yitzak <terrysha...@ya hoo.co.ukwrote:

Have a query that is arregated into crosstab query, it is working
correctly.

select Yes as selected from tblname
or
select true as selected from tblname

Produces -1 ( vba value of True) in the results pane I would like to
see it produce a checkbox with tick in just like I would see if I
displayed a boolean/yes/no field from a table.

Any idea how to force this?

Queries and Tables are for data. Forms and Reports are for editing and
displaying data. It angers the Database Gods when you mix the two and,
sooner or later, they will punish you by corrupting the database,
returning false values, or precipitating a crash. Use a form or
report. Only the developmentally handicapped and rank amateurs edit or
display queries or tables directly (Well, there are some of those
Microsoft people, too). Oops, sorry for repeating myself.

[Yitzak]

On 17 Apr, 03:19, lyle fairfield <lyle.fairfi... @gmail.comwrote :

On Apr 16, 9:57 pm, Yitzak <terrysha...@ya hoo.co.ukwrote:

Have a query that is arregated into crosstab query, it is working
correctly.

select Yes as selected from tblname
or
select true as selected from tblname

Produces -1 ( vba value of True) in the results pane I would like to
see it produce a checkbox with tick in just like I would see if I
displayed a boolean/yes/no field from a table.

Any idea how to force this?

Queries and Tables are for data. Forms and Reports are for editing and
displaying data. It angers the Database Gods when you mix the two and,
sooner or later, they will punish you by corrupting the database,
returning false values, or precipitating a crash. Use a form or
report. Only the developmentally handicapped and rank amateurs edit or
display queries or tables directly (Well, there are some of those
Microsoft people, too). Oops, sorry for repeating myself.

No way to do this then?

There are no access forms or reports at all in this project. The front-
end is in VB

e.g.

tblShop
ShopID Paid
1 yes
2 no

tblshopServices
ShopID SvcID
1 100
1 101
2 100

tblServices
SvcID Desc.
100 Internet
101 Phone
102 Post

So crosstab query (tblshopService s/tblServices) joining to tblShop

shopID Paid Internet Phone Post
1 Y Y Y N
2 N N Y N

The problem is only Paid field appears as a checkbox, Internet/Phone
will appear as vbFalse/True 0/-1.
Say I got it to display a checkbox for the Internet field - checking
it un-checking it could do nothing as it would have to delete or add a
row to tblshopServices . Maybe thats why it doesn't do this?

Just a pain because people write ad-hoc queries and I did this cross-
tab so they could use it in other queries to show in ONE ROW rather
than many which of the standard services each shop offers. The results
of these queries are exported/pasted into Excel and would make it more
standard if all fields in the query looked like the real field "Paid"
BTW - impressed with the crosstab feature in Access better than other
DB's I've used.

[Roger]

On Apr 16, 8:42*pm, Yitzak <terrysha...@ya hoo.co.ukwrote:

On 17 Apr, 03:19, lyle fairfield <lyle.fairfi... @gmail.comwrote :

On Apr 16, 9:57 pm, Yitzak <terrysha...@ya hoo.co.ukwrote:

Have a query that is arregated into crosstab query, it is working
correctly.

select Yes as selected from tblname
or
select true as selected from tblname

Produces -1 ( vba value of True) in the results pane I would like to
see it produce a checkbox with tick in just like I would see if I
displayed a boolean/yes/no field from a table.

Any idea how to force this?

Queries and Tables are for data. Forms and Reports are for editing and
displaying data. It angers the Database Gods when you mix the two and,
sooner or later, they will punish you by corrupting the database,
returning false values, or precipitating a crash. Use a form or
report. Only the developmentally handicapped and rank amateurs edit or
display queries or tables directly (Well, there are some of those
Microsoft people, too). Oops, sorry for repeating myself.

No way to do this then?

There are no access forms or reports at all in this project. The front-
end is in VB

e.g.

tblShop
ShopID * * * *Paid
1 * * * * * * * * yes
2 * * * * * * * * no

tblshopServices
ShopID * * * *SvcID
1 * * * * * * * * 100
1 * * * * * * * * 101
2 * * * * * * * * 100

tblServices
SvcID * * * * *Desc.
100 * * * * * * Internet
101 * * * * * * Phone
102 * * * * * * Post

So crosstab query (tblshopService s/tblServices) joining to tblShop

shopID * * * * Paid * * * * Internet * * * * Phone* * * Post
1 * * * * * * * * Y * * * * * * *Y * * * * * * * * *Y * * * * * * N
2 * * * * * * * * N * * * * * * *N * * * * * * * * Y * * * * * * *N

The problem is only Paid field appears as a checkbox, Internet/Phone
will appear as vbFalse/True 0/-1.
Say I got it to display a checkbox for the Internet field - checking
it un-checking it could do nothing as it would have to delete or add a
row to tblshopServices . Maybe thats why it doesn't do this?

Just a pain because people write ad-hoc queries and I did this cross-
tab so they could use it in other queries to show in ONE ROW rather
than many which of the standard services each shop offers. The results
of these queries are exported/pasted into Excel and would make it more
standard if all fields in the query looked like the real field "Paid"

BTW - impressed with the crosstab feature in Access better than other
DB's I've used.- Hide quoted text -

- Show quoted text -

open the query
right click anywhere and select properties
from the list of fields that make up the query, hilight the whole
'internet' column
choose the lookup table of the 'field properties'
select checkbox as the display control

[Chuck Grimsby]

Actually, -1 <"True". -1 means that the value isn't false. A
nit-picky point to be sure, but in the Microsoft world it often helps
to remember that booleans are best stated as "Whatever isn't False (0)
is "True". Or in other words, 0=false, *anything* else is "true".

As for your display issue, it would be best to have the results show
up as a continuous form. From there, you can format the results to
whatever your needs are.

Either way, form or query, remember that the results won't be
editable. However, if you use a form, you can trap the change event
and have the form issue an update query, then re-run the query so your
user can see the result of the change.

On Wed, 16 Apr 2008 18:57:46 -0700 (PDT), Yitzak
<te*********@ya hoo.co.ukwrote:

>Have a query that is arregated into crosstab query, it is working
correctly.

select Yes as selected from tblname
or
select true as selected from tblname

Produces -1 ( vba value of True) in the results pane I would like to
see it produce a checkbox with tick in just like I would see if I
displayed a boolean/yes/no field from a table.

Any idea how to force this?

--
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!