Check Boxes converting to 0 & -1 2

[LarryDeLaruelle]

I set up a make table query to extract a subset of my records to append to another table. Many of the fields are Yes/No check boxex.

In the newly created table they display as 0's and -1's instead of check boxes.

When I try to append those records to another table, I get a Key Violation error. I removed the key field (an autonumber) but that did not help.

I tried the append directly (without an intervening make table) and got the same error.

When I did a make table of both data sets, with the result that in both new tables the check boxes became 0's and -1's, the append works.

Is there a problem in appending data in 0 and -1 format with data in check box format? If so, how can I force Access to use check boxes instead of 0 and -1 when I do the append/make table.?

Thanks.

Larry De Laruelle
larry1de@yahoo.com

 

[WaltLukeIII]

Larry,

First off the checkbox stores a value of True/False or 0/-1. So what it sounds like is happening is the format of the fields in your new table are set to textfield they need to be yes/no formatted.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[LarryDeLaruelle]

Walt:

Thanks.

I looked at the format for the check boxes in the newly created table right away and found that the format was blank.

I can change them to Yes/No, True/False or On/Off but all that does is replace the text value for the 0's and -1's.

Any idea why it changes from the default check box on a make table? Also, any idea on how to change it back to a check box?

Larry De Laruelle
larry1de@yahoo.com

 

[WaltLukeIII]

I don't quite understand your question. I was looking at a test table trying to see if I could understand what your problem is.

The only thing you might have done is changed the "Display Control". Go to the design view and and choose the field that you want to be yes/no then go to the "Lookup" tab and select it and you should see a drop down list labeled "Display Control". Make sure that you have the one selected you want

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[MichaelRed]

Larry,

When you run a maketable query the datatype in the dest table is set to the "presumed" data type of the source QUERY. You probably need to look at the fields n the query and set their "format" appropiatly.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[LarryDeLaruelle]

Walt & Michael:

Excellent, thank you.

I checked the Display Control property on the new table created with the Make Table Query and, sure enough, it was blank. Changing it to Check Box gave me the proper display.

I tried doing the same thing in the query (selecting the field, opening properties and changing the Display Control to Check Box) but when I run the query, the new table still shows the 0's and -1's with the Display Control type blank.

First, any idea why Access sets the Display Control to blank on a Make Table (and, I'm assuming, on an Append)? And, second, is there a way to force a Check Box Display Control for all Yes/No fields in a Make Table Query?

The table I'm working with has almost 50 such fields. This project will probably be a one time to clean up a user error. However, I can envision having this problem in the future when I set up archive routines to move historical data to storage.

Thanks again.

Larry De Laruelle
larry1de@yahoo.com

 

[MichaelRed]

Larry,

What ver of Ms. Access?

'97 the query design view | Properties | Format give the options for several 'varieties' of boolean (Yes/No | True/False On/Off ...?) They all translate in the make table process for me.

I think it is as much effort to do it in the query as to muck around with the process - especially if it is just a 'one-shot' issue.

After all, the data transfers properly, and you can change the display (type) property as easily in the final product table as in the query.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[LarryDeLaruelle]

Walt:

I'm using 97.

I tried tinkering with the query properties but even setting the display control of the field in the query to check box had no effect on the display control in the new table (was set to text not blank as I had indicated).

Even if it worked, you are correct that it is the same or more work than doing it in the created table.

Guess this is just one of those Access quirks we have to live with.

Thanks again to you and Michael. It saved the data entry clerk from having to reinput 25 records.

Larry De Laruelle
larry1de@yahoo.com

 

[StuMunro]

I am having a similar problem with UNION queries. When the results of the query are viewed the yes/no fields have lost their formatting due to the UNION SQL statement they display as 0's and -1's instead of check boxes.

How can I get the checkboxs on a union query??

To see what I mean create 2 tables with the same fields

field1 : autonumber
filed2 : yes/no

then create a query.

select * from table1 union select * from table2;

view the results and the 0,-1 are displayed.

help!!!

 

[LarryDeLaruelle]

Stu:

I see what you mean. An interesting problem.

If you plan to use the result in a Form or Report, you can change the format to Yes/No, True/False or On/Off.

I don't see any way to change the diplay to a checkbox, however.

If you absolutely have to display check boxes, the only solution I can think of is to use create a temporary table containing the result you need and then change the Display Control to check box.

Sorry I couldn't be of more help. Perhaps one of the gurus can offer a tip on how to set the display control for a field in a union query.
Larry De Laruelle
larry1de@yahoo.com

 

[grajesh123]

I am trying to insert a record into the table from the form but it is not saving in the table. I am using an option group for saving the Gender (Male or Female) in the table from the form. Where am I going wrong?

What Datatype should i give for the Gender in the table? And in the Forms which tool I need to take enter the selected choice to table after I save the record?

 

[KenReay]

Hi

Never a good idea to tag an unrelated question onto the end of a thread, it will only be seem by chance - but since I have seen it, you need a numeric datatype in your table, the option group will return a number (depending on the values you have assigned to the option buttons), say you have 0 and 1, the a field type of LongInteger, Integer or even Byte would be OK

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK