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.
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.
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.
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
Code:
select purchased, cbool(purchased)
FROM myTable
in vba
Code:
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
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.
I understand that, I was just demonstrating an alternative method of determining true or false - here is a fuller example
Code:
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.
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.
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:
Code:
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.