how to update a boolean field on a form based on other values (1 Viewer)

[martinr]

is it possible to how to update a boolean (yes/no) field on a form based on the values of other fields on the form?

eg = in the afterupdate property on the form could i use a statement like;

IIf([Purchased]>0,[yes_no]=-1,[yes_no =0)

and add other conditions to include other fields?

 

[theDBguy]

If the value of the Yes/No field can be determined based on the values of other fields in the table, then you probably don't need the Yes/No field at all. If so, then yes, you can use an IIf() expression. Otherwise, you will probably have to use an If/Then/Else block.

 

[The_Doc_Man]

In a query, you don't even need the IIF.

SELECT ConsignmentID, ConsPrice, Purchased > 0 As Yes_No, etc.  FROM ConsignmentTable ...

Having a relational expression in a SELECT clause will generate a T/F value.

If you want this on a form, then you can make a checkbox on that form where the .ControlValue is "=[Purchased]>0" and then just remember to do a requery if you change one of the affected values. Since that value isn't bound (it is computed instead), you wouldn't ever store it. But then again, you don't really need to. Anything you can simply compute on the fly, you don't need to store.

 

[vba_php]

IIf([Purchased]>0,[yes_no]=-1,[yes_no =0)

and add other conditions to include other fields?

this is what guy was referring to, martin:

if purchased > 0 and/or anotherField =/</> "value" then
   boolField = -1
else
   boolField = 0
end if

if the above code was simplified to one if/then statement, then:

if purchased > 0 then
   boolField = -1
else
   boolField = 0
end if

furthermore, if you like looking at code, you could always do this:

select case purchased
   case > 0
      boolField = -1
   case else
      boolField = 0
   'even more options here if you want.....
end select

 

[The_Doc_Man]

Adam, you can simplify that further.

boolField = ( Purchased > 0 ) and/or (another field = some value)

You could also use VBA constants vbTrue and vbFalse instead of having to remember that -1 is TRUE and 0 is FALSE. Further, visually it has better mnemonic value to use the named constants for that application.

As a matter of style, particularly because of the mnemonic value if I have to revisit code later, I ALWAYS use the VBA named constants even if I happen to remember the actual numeric values associated with them. And I often use an ENUM statement to define names for my own app-defined codes for that same reason.

 

[CJ_London]

on the basis that the value can only be 0 or greater than zero you can simply use

boolfield=purchased

because boolean fields consider 0 to be false and any other number to be true so won't be appropriate if purchased can be negative

in the immediate window
?cbool(2)
True
?cbool(0)
False
?cbool(-1)
True

so in a query

select purchased, cbool(purchased)
FROM myTable

in vba

Sub vbatest()
Dim b As Boolean

b = 12345
Debug.Print b
'will return true

b = -11
Debug.Print b
'will return true

b = 0
Debug.Print b
'will return false
End Sub

 

[vba_php]

Adam, you can simplify that further.

boolField = ( Purchased > 0 ) and/or (another field = some value)

thanks, mr. veteran! =) you know Richard, you should explore being a PHP programmer in your sparetime. that is, if your wife isn't heaping more responsibility on you. your code is very similar to the nesting operations that go on in PHP in all types of cases. queries included. you can even use arrays inside query statements in the PHP language.

 

[The_Doc_Man]

CJ, the OP said "Purchased > 0" AND some other criteria combined. So just testing "purchased" doesn't work by itself given the original question.

 

[CJ_London]

I understand that, I was just demonstrating an alternative method of determining true or false - here is a fuller example

Sub vbatest()
Dim b As Boolean
'True and False would typically be a comparison such as fld 
b = 12345
Debug.Print b And False
'will return false

b = -11
Debug.Print b And True
'will return true

b = 0
Debug.Print b And True
'will return false
End Sub

I often use the false=0, any other number = true concept in my code.

 

[The_Doc_Man]

Yes, I agree, CJ - but that direct usage might lead to unusual circumstances. The order of operations might lead to some surprises when working with the operator "AND" in conjunction with integers. Our newer users might not be up on the implications of those "order of operations" issues.

Taken from an immediate window...

debug.Print cbool( &h010000 )
True
debug.Print cbool( &h0100 )
True
debug.Print cbool( &h0100 AND &h01000 )
False

Now if you have integer variables equal to those items and do an AND of the integer variables followed by assignment to a boolean variable on the other side of an equals-sign, you will get the wrong answer if your intent was for the two items to be counted as TRUE.

That is, you could have this case:

lngA = &h01000
lngB = &h0100
booC = A AND B

Debug.Print CBool(lngA)
True
Debug.Print CBool(lngB)
True
Debug.Print CBool( booC )
False

Of course, you can't do that kind of assignment in an immediate pane to actually test it.

Anyway, if we did the three lines of code in my second segment we would have the case where it APPEARS that TRUE AND TRUE = FALSE. Which is of course not really the case because of the way the question was asked in that expression.

My point was that there is a danger in using the direct variable. Using an expression that evaluates to a Boolean such as (Purchased > 0) would do the implied conversion because of the relational operator forcing the expression sub-type. Hiding the variable in the CBool function would also do the job. Failing to assure the required type conversion, though, would lead to a serious headache. For newbies, that would be a tough error to catch. For experienced old toots like us, no biggie 'cause we know better.