IF() function in Access Tables

[Guest]

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

 

[Tom Wickerath]

You can't.

_________________________________


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

 

[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

 

[Guest]

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

 

[Guest]

No, the IF function like in Excel

=IF (A=B, Yes, No)

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

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

 

[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

No, the IF function like in Excel

=IF (A=B, Yes, No)

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

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

 

[Jamie Collins]

neither Access or Excel have an If() function!

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

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.

--

 

[Douglas J. Steele]

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.

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.

 

[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
---------------------------

 

[Jamie Collins]

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

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.

--

 

[Douglas J. Steele]

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?

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.

 

[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
---------------------------