Converting an Excel "IF" statement to Access.

No,

If pasted as written, with the obvious changes in field names, it should
work.

Have you checked your references? Open a code window (any code window will
do) and on the menu go to Tools References, and have a look. If any are
marked as "MISSING" that could be your problem. Fix them by supplying the
path you need or uncheck and get rid of it if you don't need them. The 4
that you must have a

Visual Basic for Applications
Microsoft (version) Object Library
OLE Automation

and either or both of:

Microsoft ActiveX Data Objects 2.1 (or later) Library
Microsoft DAO 3.6 Object Library
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"timmycav" wrote in message
...
Arvin,

Sorry to keep asking questions. Is this formula meant to be in the
expression builder or in visual basic? I have no knowledge of Visual
Basic.
the current info in Visual Basic follows:
Private Sub Current_Level_BeforeUpdate(Cancel As Integer)

End Sub

Do I enter the formula in here with the current text, or do I replace the
current text completely?

Thanks again.

"Arvin Meyer [MVP]" wrote:

If you are trying to do this in a table, you cannot. But you can do it in
a
query, or in a form or report. In a query:

Expr1: IIf([TableName].[FieldName]100000, "Gold",
IIf([TableName].[FieldName]5000, "Zirconia", "Titanium"))
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

IIf([01 Program Table]![Spend (Total)]100000,"Gold",IIf([01 Program
Table]![Spend (Total)]5000,"Zirconia",IIf([01 Program Table]![Spend
(Total)]5000,"Titanium")))

"timmycav" wrote in message
...
Hello all,

I'm a bit of an Access novice, so I apologise if questions like this
have
been done to detah. I have tried several forums and have, so far, been
unable
to find a solution.

In Excel I have been able to create an IF statement to display text in
a
new
cell when a particular number value appears in another cell:
=IF(X2100000,"Gold",IF(X25000,"Zirconia",IF(X25 000,"Titanium")))

I have tried converting this to an Access IIF statement in a form, but
have
had no luck.
=IIf([01 Program Table]![Spend (Total)]100000,"Gold",IIf([01 Program
Table]![Spend (Total)]5000,"Zirconia",IIf([01 Program Table]![Spend
(Total)]5000,"Titanium")))

I would be grateful for any assistance anyone can provide.

Thanks Steve.

Worked like a charm.

Tim

"Steve Sanford" wrote:

As a check of the data, create a new query. Switch to SQL vies and paste in
the following:

SELECT [01 Program Table].[Spend (Total)], IIf([01 Program Table]![Spend
(Total)]100000,"Gold",IIf([01 Program Table]![Spend
(Total)]5000,"Zirconia","Titanium")) AS TheLevel FROM [01 Program Table];


Now run the query. Do the levels match up with the values?

Note: 100000.0001 will be "Gold" and 100000 will be "Zirconia".


Putting the IIF() statement in a query makes it a "field" that you can use
as a control source for a control on a form.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"timmycav" wrote:

Arvin,

I have checked the data and it all appears fine. Would the fact that the
firleds in the form are formatted as currency have any affect on the way the
formula works?

TC

"timmycav" wrote:

Thanks Arvin.

I will have a further look at the data.

"Arvin Meyer [MVP]" wrote:

If you have not seen errors, the expression was correct so the answer lies
in the data.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"timmycav" wrote in message
...
Hi Arvin,

Thanks for your help. I was trying this in a form, and had attepmted it in
a
query also. I tried it with your suggested formula but only got blank
fields
after running it.

Any further ideas?

"Arvin Meyer [MVP]" wrote:

If you are trying to do this in a table, you cannot. But you can do it in
a
query, or in a form or report. In a query:

Expr1: IIf([TableName].[FieldName]100000, "Gold",
IIf([TableName].[FieldName]5000, "Zirconia", "Titanium"))
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

IIf([01 Program Table]![Spend (Total)]100000,"Gold",IIf([01 Program
Table]![Spend (Total)]5000,"Zirconia",IIf([01 Program Table]![Spend
(Total)]5000,"Titanium")))

"timmycav" wrote in message
...
Hello all,

I'm a bit of an Access novice, so I apologise if questions like this
have
been done to detah. I have tried several forums and have, so far, been
unable
to find a solution.

In Excel I have been able to create an IF statement to display text in
a
new
cell when a particular number value appears in another cell:
=IF(X2100000,"Gold",IF(X25000,"Zirconia",IF(X25 000,"Titanium")))

I have tried converting this to an Access IIF statement in a form, but
have
had no luck.
=IIf([01 Program Table]![Spend (Total)]100000,"Gold",IIf([01 Program
Table]![Spend (Total)]5000,"Zirconia",IIf([01 Program Table]![Spend
(Total)]5000,"Titanium")))

I would be grateful for any assistance anyone can provide.