Access Functions

[rtolliver]

I looking for an Access function that will verify 2 conditions.

I've tried "IIF" "AND", they didn't work, I'm not sure if the syntacs was
correct.

Here's what I'm tring to do, I have a record that contains 2 yes/no fields
and a number field, if field "A" is "yes" and field "B" is "no" I want to use
the value in the number field.
example:

=IIf([Recvin]="yes" And [In_Inventory]="no",[BdFt],0)

The above example does not work.

 

[Matthias Klaey]

I looking for an Access function that will verify 2 conditions.

I've tried "IIF" "AND", they didn't work, I'm not sure if the syntacs was
correct.

Here's what I'm tring to do, I have a record that contains 2 yes/no fields
and a number field, if field "A" is "yes" and field "B" is "no" I want to use
the value in the number field.
example:

=IIf([Recvin]="yes" And [In_Inventory]="no",[BdFt],0)

The above example does not work.

I assume that Recvin and In_Inventory are boolean (i.e. yes/no) fields
in the table. Then try

=IIf([Recvin]=True And [In_Inventory]=False,[BdFt],0)

HTH
Matthias Kläy

 

[Klatuu]

A Yes/No (Boolean) field can contain only two values -1 for True and 0 for
False.
You are trying to compare text to a number. It should be:
=IIf([Recvin]= True And [In_Inventory]= False,[BdFt],0)
That would be for VBA. If this is in a query, you need to use the actual
value rather than the instrinsic constants True and False
=IIf([Recvin]= -1 And [In_Inventory]= 0,[BdFt],0)

 

[Matthias Klaey]

A Yes/No (Boolean) field can contain only two values -1 for True and 0 for
False.
You are trying to compare text to a number. It should be:
=IIf([Recvin]= True And [In_Inventory]= False,[BdFt],0)
That would be for VBA. If this is in a query, you need to use the actual
value rather than the instrinsic constants True and False
=IIf([Recvin]= -1 And [In_Inventory]= 0,[BdFt],0)

Hmm...

The following works for me:

SELECT tbl_Boolean.ItemNr, tbl_Boolean.TrueFalse
FROM tbl_Boolean
WHERE (((tbl_Boolean.TrueFalse)=Yes));

SELECT tbl_Boolean.ItemNr, tbl_Boolean.TrueFalse
FROM tbl_Boolean
WHERE (((tbl_Boolean.TrueFalse)=True));

and something like this in the control source of a textbos on a form
also works:

=IIf([chkTrueFalse]=Yes;"Hi";"Bye")

I think that in queries and expressions you can use not only True and
False, but also Yes and No. The OP's problem was that s/he enclosed
Yes with quotes.

Greetings
Matthias Kläy

 

[John Vinson]

Here's what I'm tring to do, I have a record that contains 2 yes/no fields
and a number field, if field "A" is "yes" and field "B" is "no" I want to use
the value in the number field.
example:

=IIf([Recvin]="yes" And [In_Inventory]="no",[BdFt],0)

The above example does not work.

That would work if the two fields were Text fields containing the text
strings "Yes" and "No" - but a Yes/No field is not a text field and
does not contain those text strings. It's actually stored as a Number,
with -1 meaning Yes and 0 meaning No. The simplest way to do what you
ask is just to treat the Yes/No field as returning a logical TRUE or
FALSE:

= IIF([Recvin] AND NOT [In_Inventory], [BdFt], 0)

or if that's confusing, use the builtin constants True and False (no
quotes):

= IIF([Recvin] = True AND [In_Inventory] = False, [BdFt], 0)

John W. Vinson[MVP]

 

[rtolliver]

The fields are from a table not a query. I did try using the actual vaules
and got an error in field "#Name?"

A Yes/No (Boolean) field can contain only two values -1 for True and 0 for
False.
You are trying to compare text to a number. It should be:
=IIf([Recvin]= True And [In_Inventory]= False,[BdFt],0)
That would be for VBA. If this is in a query, you need to use the actual
value rather than the instrinsic constants True and False
=IIf([Recvin]= -1 And [In_Inventory]= 0,[BdFt],0)
--
Dave Hargis, Microsoft Access MVP

I looking for an Access function that will verify 2 conditions.

I've tried "IIF" "AND", they didn't work, I'm not sure if the syntacs was
correct.

Here's what I'm tring to do, I have a record that contains 2 yes/no fields
and a number field, if field "A" is "yes" and field "B" is "no" I want to use
the value in the number field.
example:

=IIf([Recvin]="yes" And [In_Inventory]="no",[BdFt],0)

The above example does not work.