"IF" Function on Access XP Expression Builder.

[SAM_B]

Hi,

can someone PLEASE tell me how you use the IF function in the access expression builder.

i am trying to say that:

IF (some company is eligable for a 5% discount), then
calculate (price*5% discount)
else
calculate (price)


i have tried to do this in every way i know how, as i need it for some work.

if it is even remotely possible for this to work, i would greatly appreciate some response.

Thanks.

also...as this is my first post, i appoloise if i have posted in the wrong forum

 

[Mile-O]

It's IIF and not IF


Field names go in sqaure brackets.

 

[SAM_B]

yes, i have tried that aswell, but it doesnt work.

what syntax do i use?

IIF=(data........)

=IIF(data...)

or another?.

obviously, the fields inside the () brackets are []

 

[NaKin]

in query grid
Calculated_field_name: IIf ([discount]=-1, [price]*.05, [price])

in text box
= IIf ([discount]=-1, [price]*.05, [price])

-1 equals true in access

 

[SAM_B]

thanks alot, i will try that.

 

[Mile-O]

-1 equals true in access

Any value that's not 0 is True.

 

[Pat Hartman]

I believe it's the other way around. Any value that is not true is false.

 

[ChrisO]

Just playing with the truth a bit…(Sorry for the pun )

Option Explicit
Option Compare Text

[color=green]'  False = 0 and True = Not False
'
'  If stored as an integer...
'
'  False = 0000 0000 0000 0000
'  the Not operator flips all bits so
'  True  = 1111 1111 1111 1111
'
'  In two's complement this equals -1
'[/color]
Sub Test()
    Dim intX As Integer
    
    intX = 3             [color=green]' Set intX to some value other than 0 or -1[/color]
    
    If (intX) Then
        MsgBox "True"    [color=green]'*********** is true because it is non zero[/color]
    Else
        MsgBox "False"
    End If
    
    If (intX = True) Then
        MsgBox "True"
    Else
        MsgBox "False"   [color=green]'*********** is false because True = -1[/color]
    End If

    intX = Not intX      [color=green]'intX is now -4 but the logic has not changed[/color]
    
    If (intX) Then
        MsgBox "True"    [color=green]'***********[/color]
    Else
        MsgBox "False"
    End If
    
    If (intX = True) Then
        MsgBox "True"
    Else
        MsgBox "False"   [color=green]'***********[/color]
    End If
    
End Sub

In VBA it appears unreliable to think of all non zero values as True.
A non zero value can be both true and not equal to True at the same time.
Only -1 will always behave as expected.

Regards,
Chris.

 

[Pat Hartman]

The first of each set of your tests is an identity test rather than a conditional. An identity test will return true unless the value is null or 0. It doesn't make much sense to use this construct except on a boolean. I know that - "If MyBoolean" and "If MyBoolean = True" are equivalent but "If MyVariable" and "If MyVariable = something" are not. Many people omit the "= True" part of a boolean test because it saves typing. I don't because I believe that ommissions like this foster misunderstanding the true nature of the condition being tested. Actually, "If MyVariable" is a substitute for "Not IsNull(MyVariable)" and again, rather than writing the longer, more explict instruction, some people take a shortcut that can be misunderstood.

Notice that "Not SomeVariable" adds 1 to the value of SomeVariable and then multiplies the result by -1. That's because VBA expects you to know what you are doing and you wouldn't be writing this instruction if SomeVariable were not a boolean. Using this formula "not 3" becomes "-4". Does that make sense? But "not 0" becomes "-1" and "not -1" becomes "0".

 

[ChrisO]

Hmmm…

Now Pat, you should know I’m too old to argue with a lady.

But just for the sake of an argument, which we are not having…

“Notice that "Not Some Variable" adds 1 to the value of Some Variable and then multiplies the result by -1.”

I believe this is incorrect. I believe the Not operator simply flips all bits of the operand.

In my previous post, although I did not spell it out…

False = [color=red]0[/color]000 0000 0000 0000
the Not operator flips all bits so
True  = [color=red]1[/color]111 1111 1111 1111

After the operation the MSB (Red) gets pushed to the Negetive bit of the condition codes.

The processor interprets this as a negative value, in two’s complement, and simple uses the other 15 bit to achieve the value. No multiplication is required; the micro code need only mask off the MSB, and therefore is faster. The remaining 15 bits can be interpreted as + or – depending on the N bit in the condition codes. There is no need for any further multiplication to determine sign; the sign is already in the condition codes.

That is where we may disagree, only for the sake of an argument that we are not having.

Now to where I think we agree.

“Many people omit the "= True" part of a boolean test because it saves typing. I don't because I believe that ommissions like this foster misunderstanding the true nature of the condition being tested.”

That was the exact reason for my reply; to not only state, but to also demonstrate, the reason.

Kind regards,
Chris.