Converting an Excel "IF" statement to Access.

[Guest]

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(X2>100000,"Gold",IF(X2>5000,"Zirconia",IF(X2<5000,"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.

 

[Arvin Meyer [MVP]]

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")))

 

[Guest]

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?

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")))

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(X2>100000,"Gold",IF(X2>5000,"Zirconia",IF(X2<5000,"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.

 

[Guest]

Maybe a stupid question but you did replace the fieldnames that Arvin
presented with your own table-fieldnames?
--
Maurice Ausum

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?

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")))

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(X2>100000,"Gold",IF(X2>5000,"Zirconia",IF(X2<5000,"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.

 

[Arvin Meyer [MVP]]

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

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?

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")))

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(X2>100000,"Gold",IF(X2>5000,"Zirconia",IF(X2<5000,"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.

 

[Guest]

Thanks Arvin.

I will have a further look at the data.

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

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?

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")))

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(X2>100000,"Gold",IF(X2>5000,"Zirconia",IF(X2<5000,"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.

 

[Guest]

Hi Maurice,

Yep. Made sure I had the correct field names.

Maybe a stupid question but you did replace the fieldnames that Arvin
presented with your own table-fieldnames?
--
Maurice Ausum

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?

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")))

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(X2>100000,"Gold",IF(X2>5000,"Zirconia",IF(X2<5000,"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.

 

[Guest]

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

Thanks Arvin.

I will have a further look at the data.

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

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?

:

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")))

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(X2>100000,"Gold",IF(X2>5000,"Zirconia",IF(X2<5000,"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.

 

[Guest]

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.

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")))

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(X2>100000,"Gold",IF(X2>5000,"Zirconia",IF(X2<5000,"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.

 

[Guest]

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.)

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

Thanks Arvin.

I will have a further look at the data.

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

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?

:

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")))

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(X2>100000,"Gold",IF(X2>5000,"Zirconia",IF(X2<5000,"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.

 

[Arvin Meyer [MVP]]

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 are:

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

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.

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")))

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(X2>100000,"Gold",IF(X2>5000,"Zirconia",IF(X2<5000,"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.

 

[Guest]

Thanks Steve.

Worked like a charm.

Tim

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.)

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

Thanks Arvin.

I will have a further look at the data.

:

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

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?

:

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")))

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(X2>100000,"Gold",IF(X2>5000,"Zirconia",IF(X2<5000,"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.