Adding A Value To A Field Using Checkboxes

[Guest]

I am trying to enter a value into a field called, FrameValue.
The fields are as follows Frame (Number), FrameValue (Number) and the next
three fields are checkboxes called L, C & R.
How can I get the data into FrameValue using the following.
(1) When the field, Frame, has a value and all of three checkbox fields L, C
& R are False the FrameValue = 1
(2) When the field, Frame, has a value and any combination of the three
checkbox fields L, C & R are True the FrameValue = 0

All help is appreciated

Nick

 

[John Spencer]

In most cases, it would be better not to store the FrameValue, but just to
calculate it as needed.

You should be able to use an immediate if (IIF) in a query to do this.

IIF(Frame is Not Null And L And C And R, 1, IIF(Frame is Not Null and (L OR
C OR R),0,Null))

That should return 1, 0, or Null (blank) depending on the values of the four
fields.

If you really feel that it is absolutely necessary to store the value, then
you can use that formula in some VBA on a form to populate the field or you
can use an update query to populate the field.

Since L, C, and R are all yes/no fields, I used shorthand to check their
values. That is I didn't write
Frame is Not Null And L = True and C = True and R=True

If you aren't doing this in a query, you will have to use the IsNull
Function.
IIF(IsNull(Frame)=True and L and C and R,1, IIF(...)

 

[Guest]

John,
Thank you for the help and advice, which worked very well.
I think it is necessary to store the value in the query but you may have a
better solution. With a Frame Number being compulsory and duplicate frame
Numbers being acceptable, the Frame Number may be the only data for a record
and needs to be counted as one. When one or more of the checkboxes is checked
then only the checkboxes will be counted for that record. So to explain if no
checkboxes are checked then the sum for that record = 1. If two checkboxes
are checked then the sum for that record would = 2. If the Frame Number was
counted with the sum of the checkboxes in the latter example I would have a
result of 3, which is not the required result.

Nick

 

[John Spencer]

We seem to be having a terminology problem.

Tables store data in fields
Queries are used to display and manipulate the data that is stored in
tables. So you can't store the value in the query. You could use a query
to write the data to a field in the table.

No matter, if you have a solution that is working for you then that is good
and the sun is shining, the birds are singing, and all is right with the
world.

May your day be blessed