If and Or Functions in Access

  • Thread starter Thread starter bmistry
  • Start date Start date
B

bmistry

Hi,

I would like to create a simple function that read IF
[Practice] or [Location] is blank "No Data"

The OR and AND Functions are causing me problems. I have
entered the following into the query and I am getting the
error "You have entered a comma without a preceding value
or identifier"

Geographic Alignment: IIf(OR(IsNull([Practice]),(IsNull
([LocationDesc])"No Data")))

Also, if I want enter more then 1 IF Statement, does it
work in a similar way to excel where i just keep adding
with a "," inbetween? Is there a limit to the number of IF
statements I can have?

Thanks in advance

Bhavini
 
bmistry said:
Hi,


The OR and AND Functions are causing me problems. I have
entered the following into the query and I am getting the
error "You have entered a comma without a preceding value
or identifier"

Geographic Alignment: IIf(OR(IsNull([Practice]),(IsNull
([LocationDesc])"No Data")))
Click to expand...

The IIf function is causing you problems as well. The syntax is
IIf(Condition, TrueValue, FalseValue), where condition is what you are
evaluating, TrueValue is the value the function returns if the conditions is
true, and FalseValue is the value returned if the condition is false. You
need to use something like this:

IIf(IsNull([Practice]) OR IsNull([LocationDesc]), "No Data", "Some data")

Obviously, you can replace "Some data" with whatever you like.
Also, if I want enter more then 1 IF Statement, does it
work in a similar way to excel where i just keep adding
with a "," inbetween? Is there a limit to the number of IF
statements I can have?
Click to expand...

I don't know if there is a limit, but you can certainly nest several IIf
statements. Just use another IIf statement at the place you put the
TrueValue or FalseValue data, and be careful to count all the parentheses.

HTH

Adam
 
OR and AND are boolean operators, not functions.
The IIf, on the other hand, is a function. It takes three arguments,
separated by comma, as shown below:
Also, you can't use the function IsNull in queries, you use IS NULL instead.
Finally, you didn't state what you want the IIf function to return if
neither of the two query fields are Null.
BTW, definitions of OR and AND as well as the IIf function is readily
available from Access help files or MSDN Library on the Web.

IIf ( condition, value_if_true, value_if_false )
If condition evaluates to True it retuns value_if_true, otherwise it returns
value_if_false

Try this:
IIf(([Practice] IS NULL) OR ([LocationDesc] IS NULL), "No Data", "Data
Present"))
Here the IIf function will return a string "No Data" if either [Practice] or
[LocationDesc], or both, are null, otherwise it will return a string "Data
Present"

Ragnar
 
Back
Top