By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,862 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Cannot update checkboxes with an update query in access 97 - help!!

P: 2
Hi,

I'm trying to use an update query to update a checkbox field in access 97.

In the update query design I am indicating in criteria:
[fieldname]=true

When I run the update query, a message box appears saying "You are about to update xxx records. .... Are you sure you want to update these?" to which I click yes.

However, when I check the table, none of the checkboxes have been updated - they are all still empty.

Can someone help me out with this - is there some issue I should be aware of when trying to update checkboxes?

Many thanks.
Mar 22 '07 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,610
Hi,

I'm trying to use an update query to update a checkbox field in access 97.

In the update query design I am indicating in criteria:
[fieldname]=true

When I run the update query, a message box appears saying "You are about to update xxx records. .... Are you sure you want to update these?" to which I click yes.

However, when I check the table, none of the checkboxes have been updated - they are all still empty.

Can someone help me out with this - is there some issue I should be aware of when trying to update checkboxes?

Many thanks.
Your Criteria states that [fieldname]=True which would return all values which are Checked (Yes) but you state that the Check Boxes are still empty. I'm lost.
Mar 23 '07 #2

P: 2
Your Criteria states that [fieldname]=True which would return all values which are Checked (Yes) but you state that the Check Boxes are still empty. I'm lost.
Sorry, I meant to say that I have typed in the 'Update to:' section, not the 'criteria' section. So:

All checkboxes are currently empty (ie: FALSE).

I want to update them all to checked (ie: TRUE).

In the update query design I have the following:
Update to: [fieldname]=TRUE
Criteria:FALSE

When I run this, it tells me it is updating all records, but when I go to the table none of the boxes are checked.

hope that makes sense !!
Mar 23 '07 #3

P: 13
Hi

I think you need to select the field you want to update twice (side by side, so to speak). In the first instance you show the criteria for the update, and in the second you show the update to.

B
Mar 23 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry, I meant to say that I have typed in the 'Update to:' section, not the 'criteria' section. So:

All checkboxes are currently empty (ie: FALSE).

I want to update them all to checked (ie: TRUE).

In the update query design I have the following:
Update to: [fieldname]=TRUE
Criteria:FALSE

When I run this, it tells me it is updating all records, but when I go to the table none of the boxes are checked.

hope that makes sense !!
Do me a favour because I think your syntax is wrong here. Change the query view to SQL and copy and paste it in here.

Mary
Mar 25 '07 #5

missinglinq
Expert 2.5K+
P: 3,532
This drove me crazy, because I couldn't get it to work either! Finally figured it out. Checkboxes can have one of three separate formats; Yes/No, True/False and On/Off! The trick is that in the Query Grid, your Update To and Criteria have to match the format you've set in your table!

If in your table definition your field is defined as True/False then in your query:

Criteria False
Update to True

but if your field in the table design view is defined as Yes/No (which is the default) then you have to use

Criteria No
Update to Yes

So you need to go into the Design View for you table and see what format your checkbox field has and match it up in the query!
Mar 25 '07 #6

ADezii
Expert 5K+
P: 8,610
This drove me crazy, because I couldn't get it to work either! Finally figured it out. Checkboxes can have one of three separate formats; Yes/No, True/False and On/Off! The trick is that in the Query Grid, your Update To and Criteria have to match the format you've set in your table!

If in your table definition your field is defined as True/False then in your query:

Criteria False
Update to True

but if your field in the table design view is defined as Yes/No (which is the default) then you have to use

Criteria No
Update to Yes

So you need to go into the Design View for you table and see what format your checkbox field has and match it up in the query!
That is a great point to know - Thanks.
Mar 25 '07 #7

missinglinq
Expert 2.5K+
P: 3,532
One of the reasons I love Access, despite all its many quirks, and cruising this forum, is that there's always something new to learn about it!
Mar 25 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
This drove me crazy, because I couldn't get it to work either! Finally figured it out. Checkboxes can have one of three separate formats; Yes/No, True/False and On/Off! The trick is that in the Query Grid, your Update To and Criteria have to match the format you've set in your table!

If in your table definition your field is defined as True/False then in your query:

Criteria False
Update to True

but if your field in the table design view is defined as Yes/No (which is the default) then you have to use

Criteria No
Update to Yes

So you need to go into the Design View for you table and see what format your checkbox field has and match it up in the query!
Just one addition to this. If you put -1 for True or Yes and 0 for False or No it will work.
Mar 25 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.