Yes/No checkbox is no longer a checkbox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ragtopcaddy - 28 May 2008 14:55 GMT

I recently split my mdb for front and back end.

One of the tables has a boolean field that displays as a checkbox. The linked
table displays the checkbox. When I do a maketable query on the linked table,
the resulting table no long has a checkbox, but 0's and -1's. There doesn't
seem to be any way to format it to display the checkbox. Of course, I will
just import the table from the BE, but I'm just curious as to why they
weren't checkboxes to begin with, and furthermore, why I can't change them
back to checkboxes.

Thanks,

Signature

Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Reply to this Message

Klatuu - 28 May 2008 17:31 GMT

0 means not checked (false) and -1 means checked (true)
First, don't use make table queries. Create the table the way you want it.
Then change your query from a make table to an append query to append to your
table.
Then before you run the append query, run a delete query against the table
to remove the old data.
Make table queries cause a lot of database bloat and really should not be
used in most cases.
As to how you view it, users shouldn't be seeing raw tables anyway. You
sould create a datasheet form and use check box controls to display the data.

Signature

Dave Hargis, Microsoft Access MVP

> I recently split my mdb for front and back end.
>
[quoted text clipped - 7 lines]
>
> Thanks,

Reply to this Message

ragtopcaddy - 28 May 2008 21:12 GMT

Thanks Klatuu,

I'm aware of most of these issues. I generally use a make-table query as a
short cut to create the table structure and then change datatypes and field
names where appropriate, and use append queries afterwards. I wasn't aware of
the problem you describe with using them. Does compact/repair or decompile
undo the damage?

Anyway, I'm not posting this because it's a show-stopper. I'm just curious as
to why the field should appear as a checkbox in the original db, and a 0/-1
textbox in the product of the maketable query. Also wondering if there is any
way to get the checkbox back after the maketable query.

Are you suggesting that maketable queries are inherently shaky and that the
checkbox foolishness may be just another manifestation of that instability?

My users see only forms and reports. This just struck me as a curiosity.

Thanks,

>0 means not checked (false) and -1 means checked (true)
>First, don't use make table queries. Create the table the way you want it.
[quoted text clipped - 11 lines]
>>
>> Thanks,

Signature

Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Reply to this Message

Klatuu - 28 May 2008 21:24 GMT

A make table query is the results of the output of a query. It can only
guess at what data types to use for a field. It appears, in this case, it is
makinging into an integer or long integer. You would have to change the data
type back to Yes/No (Boolean) data type.

Compact and repair will remove the bloat from a database, but it will not
affect any table structures. Avoid using the Decompile unnecessarily. It is
really only indented to use as a way to try to recove a corrupt database and
only works (sometimes) when the corruption is in a module (form, report,
standard, or class).

You Q&D for creating tables with a make table is okay, but be sure you check
the data types and for text fields, the length. A make table query assigns
the length specified in your database options as the default length, so it
may be too long or not long enough for the data you intend for later use.

Signature

Dave Hargis, Microsoft Access MVP

> Thanks Klatuu,
>
[quoted text clipped - 31 lines]
> >>
> >> Thanks,

Reply to this Message

ragtopcaddy - 28 May 2008 23:06 GMT

I tried changing it to Yes/No, but I still see 0's and -1's. Just another one
of those mysteries.

Thanks,

>A make table query is the results of the output of a query. It can only
>guess at what data types to use for a field. It appears, in this case, it is
[quoted text clipped - 16 lines]
>> >>
>> >> Thanks,

Signature

Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Reply to this Message

Graham Mandeno - 29 May 2008 00:08 GMT

Hi Bill

By default, any table that is created other than through table design view
will display all the data fields in textboxes.

If you want a boolean (yes/no) field to be displayed in a checkbox, you go
to the Lookup tab on the field's property sheet and change the
DisplayControl to "Check Box".

Signature

Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

>I tried changing it to Yes/No, but I still see 0's and -1's. Just another
>one
[quoted text clipped - 28 lines]
>>> >>
>>> >> Thanks,

Reply to this Message

ragtopcaddy - 29 May 2008 14:29 GMT

Thank you Graham!

>Hi Bill
>
[quoted text clipped - 9 lines]
>>>> >>
>>>> >> Thanks,

Signature

Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Reply to this Message

Bob Quintal - 29 May 2008 00:14 GMT

> I tried changing it to Yes/No, but I still see 0's and -1's. Just
> another one of those mysteries.
>
> Thanks,

In Table design mode, select the field in question, then the lookup
tab at the bottom of the form design view. Change to Check Box.

Q

>>A make table query is the results of the output of a query. It
>>can only guess at what data types to use for a field. It appears,
[quoted text clipped - 18 lines]
>>> >>
>>> >> Thanks,

Signature

Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

Reply to this Message