anything like Excel If Statements that I can use with Access Queries?

[gemma.mbhh]

[Will Access be able to do what I need. Will it involve VBA?]

So. I have a large spreadsheet which is becoming unwieldy. I use it to
create tables in html (using If statements and concatenating fields
with html fragments to either side of fields with the data I want to
display). This works remarkably well for me. But it's not quite
flexible enough for me to creatively manipulate the many pieces of
interconnected data. My data contains plenty of repetition and seems
ideally suited to moving to Access, from what I have been learning.

Some lines in the table have an image associated with them and some
don't (marked with a y/n field). The If statement checks if I have an
image (y/n) and if I do, concatenates the image name.jpg (contained in
a separate field) with the html for displaying an image. Otherwise it
uses the html for a blank cell. [And so on for other conditions.]

I have reached the limits of flexibility in Excel and want to create
something more robust in Access. I am new to databases and have been
working through some of the training at Office Online.

I am making my database from scratch (and have just about mastered the
concept of relationships and basic queries). I see how to concatenate
fields in a query result, which is ideal for my needs. I can also see
how I would be able to create a query that only shows data with
associated images, or only shows data with no image, etc.

How would I go about selecting a bunch of data with a query (some with
images, some without).... AND present that data in two different ways
depending on if it has an image or not?

i.e. If image=y, concatenate xyz fields, If image=n or null,
concatenate abc fields.....

Nothing I've read so far seems to suggest this functionality is built
into Access. Am I right? Do I need to invest in VBA for Dummies and
quite a bit of spare time?

 

[KARL DEWEY]

Use the 'Imediate If' with this syntax --
IIF( Test , True results, False results)
IIF( Test1 AND Test2, True results, False results)
IIF( Test1 OR Test2, True results, False results)
IIF( Test1 , True results, IIF( Test2 , True results, False results))

 

[gemma.mbhh]

Use the 'Imediate If' with this syntax --
    IIF( Test , True results, False results)
    IIF( Test1 AND Test2, True results, False results)
    IIF( Test1 OR Test2, True results, False results)
    IIF( Test1 , True results, IIF( Test2 , True results, False results))

Thank you so much. And thank you for showing the nested example,
because no doubt that would have been my next question.

I think I can see myself coping with this much VBA, just about.

Cheers,
Gemma.

 

[Ken Sheridan]

Gemma:

Also take a look at the Switch function. This takes a parameter array as
its argument, being a set of pairs of expressions, one of each pair a
conditional expression, the other the value returned for the first of the
conditional expressions to evaluate to True, e.g.

Switch(x>y,"x is bigger than y",x<y,"x is smaller than y",x=y,"x and y are
the same")

will return the text string equivalent to the comparative expression which
evaluates to True, so if x = 1 and y = 2 for instance then "x is smaller than
y" would be returned. Using the Switch function is often better than nested
IIf function calls. The conditional expressions can of course also use
Boolean AND or OR operations rather than just simple comparative operations
as in the above example.

Ken Sheridan
Stafford, England

 

[Dale Fye]

Another advantage of Switch over nested Ifs is that all expressions in a
nested if get evaluated, so if you have an expression in the True results
portion of the nested If, and another one in the false portion, both
expressions will get evaluated. So, for example if you want to evaluate one
expression if a value is NULL, and another if the value is not null, they
will both get evaluated, and one of them could cause an error. I cannot
think of a good example at the moment, but when one comes to me, I'll post
back.

Dale

Use the 'Imediate If' with this syntax --
IIF( Test , True results, False results)
IIF( Test1 AND Test2, True results, False results)
IIF( Test1 OR Test2, True results, False results)
IIF( Test1 , True results, IIF( Test2 , True results, False results))

Thank you so much. And thank you for showing the nested example,
because no doubt that would have been my next question.

I think I can see myself coping with this much VBA, just about.

Cheers,
Gemma.

 

[Michel Walsh]

You probably meant the reverse

SELECT iif( false, 1 / 0, 1) FROM categories

is ok, since 1 / 0 will never be evaluated, but SWITCH is a FUNCTION
(while the JET-SQL IIF is a STATEMENT) and for a FUNCTION, EACH and every
argument from the calling program (that will be Jet) is fully evaluated
before being send to the inner working of the function. As example, consider
the Absolute function call:


Abs( 1 + 5 )

is such that the inner working of Abs will only see 6 and won't be able to
differentiate it from the call made with Abs( 3+ 3). SO, with SWITCH, the
calling program will evaluates each and every or the argument:


SELECT SWITCH( true, 1, true, 2, false, 1 / 0, true, 0) FROM categories

and in Northwind, will produce an error (division by zero) even if
LOGICALLY, we never need the sixth argument, in the inner working of SWITCH,
for those arguments.


NOTE: the VBA-IIF is a FUNCTION, only the JET-SQL-IIF is a STATEMENT.



Vanderghast, Access MVP

 

[Dale Fye]

Michael,

Thanks for the clarification Michel. I never realized the JET contained an
IIF Statement. I always assumed that it was evaluating the vba function.

Dale