Converting an Excel "IF" statement to Access.

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.

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.

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.

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


"timmycav" wrote:

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.

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.

Hi Maurice,

Yep. Made sure I had the correct field names.

"Maurice" wrote:

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


"timmycav" wrote:

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.

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.

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.

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.

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.