IF() function in Access Tables

T

Tom Wickerath

You can't.

_________________________________


How can I enable the IF() function in an Access 2000 table?
 
G

Graham R Seach

Norman,

I'm sure what Tom meant to say is the following:

1. There is no IF() function. What you're probably thinking of is the IIf()
function.

2. IIf() is a VBA function. VBA functions cannot be applied to tables; only
to forms and reports.

What are you trying to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
T

The Tick

The IIf() statement can also be applied to queries. You may want to make a
query to use this statement instead of a table. For example:

IIf([Field1]=1, "yes", "no")

I hope this might work for you
 
N

Normangerman

No, the IF function like in Excel

=IF (A=B, Yes, No)

The Tick said:
The IIf() statement can also be applied to queries. You may want to make a
query to use this statement instead of a table. For example:

IIf([Field1]=1, "yes", "no")

I hope this might work for you

Graham R Seach said:
Norman,

I'm sure what Tom meant to say is the following:

1. There is no IF() function. What you're probably thinking of is the IIf()
function.

2. IIf() is a VBA function. VBA functions cannot be applied to tables; only
to forms and reports.

What are you trying to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Click to expand...
Click to expand...
 
G

Graham R Seach

Norman,

Both Access and Excel have an If operator, whose syntax is:
If A=B Then
MsgBox "Yes"
Else
MsgBox "No"
End If

....but neither Access or Excel have an If() function! What you are thinking
about is the IIf() function! Its syntax is:
IIf(expression, truepart, falsepart)

You can only use IF in VBA, whereas you can use IIF in VBA and SQL. You
can't use either in tables, 'cause tables don't have anywhere to execute
code.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Normangerman said:
No, the IF function like in Excel

=IF (A=B, Yes, No)

The Tick said:
The IIf() statement can also be applied to queries. You may want to make
a
query to use this statement instead of a table. For example:

IIf([Field1]=1, "yes", "no")

I hope this might work for you

Graham R Seach said:
Norman,

I'm sure what Tom meant to say is the following:

1. There is no IF() function. What you're probably thinking of is the
IIf()
function.

2. IIf() is a VBA function. VBA functions cannot be applied to tables;
only
to forms and reports.

What are you trying to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Click to expand...
Click to expand...
Click to expand...
 
J

Jamie Collins

Graham R Seach said:
neither Access or Excel have an If() function!
Click to expand...

Incorrect. In the Excel UI, choose: Insert, Function, change the
dropdown 'Or select a category' to 'Logical, look in the 'Select a
function' list and see IF listed.
You can only use IF in VBA, whereas you can use IIF in VBA and SQL. You
can't use either in tables
Click to expand...

You missed the fact that IIF may be used in a query, a query can be
used to create a VIEW, table=relation, VIEW=relation, so saying you
can't use IIF in a table is a bit of a technicality.

In MS Access UI, I think the OP needs to created a 'stored query'
object containing something like:

SELECT IIF(MyIntCol=0,'No','Yes') AS Response FROM MyTable;

Save it with a name (e.g. MyView) and the VIEW may be queried as if it
were a table e.g.

SELECT Response FROM MyView;

Jamie.

--
 
D

Douglas J. Steele

Jamie Collins said:
You missed the fact that IIF may be used in a query, a query can be
used to create a VIEW, table=relation, VIEW=relation, so saying you
can't use IIF in a table is a bit of a technicality.
Click to expand...

No, it isn't.

Firstly, I don't think Graham missed the fact that IIf can be used in a
query: I would assume that's what he meant by using it in SQL.

And a query (or view, if you prefer) is not exactly the same as a table.
While you may equate the two, there are definitely different objects in
Access. You cannot put formulae into a table, whereas you can in a query,
and I'm sure that was Graham's point.
 
G

Graham R Seach

Exactly my points. I didn't realise I wasn't clear. Thanks Doug.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
J

Jamie Collins

Graham R Seach said:
Exactly my points. I didn't realise I wasn't clear. Thanks Doug.
Click to expand...

Getting back to the OP's point, I said:

"IIF may be used in a query, a query can be used to create a VIEW...
In MS Access UI, I think the OP needs to (create) a 'stored query'
object."

Does anyone agree this is best advice or want to suggest an
alternative?

Jamie.

--
 
D

Douglas J. Steele

Jamie Collins said:
Getting back to the OP's point, I said:

"IIF may be used in a query, a query can be used to create a VIEW...
In MS Access UI, I think the OP needs to (create) a 'stored query'
object."

Does anyone agree this is best advice or want to suggest an
alternative?
Click to expand...

Agreed. Creating a query is probably the best approach. Depending on what
the OP is trying to do, it's also possible to use the IIf statement as the
control source for a textbox on a form or report.
 
G

Graham R Seach

Jamie,

Yes, it seems Excel does have an If() function, albeit a worksheet function.
I stand corrected.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
Top