Help with IIf Statement

  • Thread starter Thread starter PFSPUPPY
  • Start date Start date
P

PFSPUPPY

I am trying to add a calc'd field to my db.

IIF(P1=0,1,(P2-P1/P1))

What am I missing
 
Here is what I entered and it is still telling me it is a syntax error....

IIf ([Table1]![Period 1 Value] = 0, 1, (( [Table1]![Period 2 Value] -
[Table1]![Period 1 Value] )/ ([Table1]![Period 1 Value] ))

golfinray said:
IIF([p1]=0,1,((p2-p1)/p1))
--
Milton Purdy
ACCESS
State of Arkansas


PFSPUPPY said:
I am trying to add a calc'd field to my db.

IIF(P1=0,1,(P2-P1/P1))

What am I missing
Click to expand...
Click to expand...
 
It appears my <send> failed...

Don't store calculated values in your table (unless you have one of the rare
exceptions). Provide more description if you'd like our assessment whether
your situation is an exception.

Use a query to do the IIF() calculation. That way, you have it as-needed,
on-demand.

Regards

Jeff Boyce
Microsoft Access MVP
 
Hi Jeff,

That is what I am doing (putting the IIF inside of the query) but having
trouble with it.
 
It appears your statement is missing a closing parenthesis (you have 4
'open' and 3 'close').

Regards

Jeff Boyce
Microsoft Access MVP


PFSPUPPY said:
Here is what I entered and it is still telling me it is a syntax error....

IIf ([Table1]![Period 1 Value] = 0, 1, (( [Table1]![Period 2 Value] -
[Table1]![Period 1 Value] )/ ([Table1]![Period 1 Value] ))

golfinray said:
IIF([p1]=0,1,((p2-p1)/p1))
--
Milton Purdy
ACCESS
State of Arkansas


PFSPUPPY said:
I am trying to add a calc'd field to my db.

IIF(P1=0,1,(P2-P1/P1))

What am I missing
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeff

I corrected it but an still receiving a syntax error.

IIf ([Master]![Period 1 Value] = 0, 1, ( [Master]![Period 2 Value] -
[Master]![Period 1 Value] )/ ([Master]![Period 1 Value] ))

I have to be missing something really elemental.....



Jeff Boyce said:
It appears your statement is missing a closing parenthesis (you have 4
'open' and 3 'close').

Regards

Jeff Boyce
Microsoft Access MVP


PFSPUPPY said:
Here is what I entered and it is still telling me it is a syntax error....

IIf ([Table1]![Period 1 Value] = 0, 1, (( [Table1]![Period 2 Value] -
[Table1]![Period 1 Value] )/ ([Table1]![Period 1 Value] ))

golfinray said:
IIF([p1]=0,1,((p2-p1)/p1))
--
Milton Purdy
ACCESS
State of Arkansas


:

I am trying to add a calc'd field to my db.

IIF(P1=0,1,(P2-P1/P1))

What am I missing
Click to expand...
Click to expand...
Click to expand...
 
It would help to know the specific error message....

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


PFSPUPPY said:
Hi Jeff

I corrected it but an still receiving a syntax error.

IIf ([Master]![Period 1 Value] = 0, 1, ( [Master]![Period 2 Value] -
[Master]![Period 1 Value] )/ ([Master]![Period 1 Value] ))

I have to be missing something really elemental.....



Jeff Boyce said:
It appears your statement is missing a closing parenthesis (you have 4
'open' and 3 'close').

Regards

Jeff Boyce
Microsoft Access MVP


PFSPUPPY said:
Here is what I entered and it is still telling me it is a syntax error....

IIf ([Table1]![Period 1 Value] = 0, 1, (( [Table1]![Period 2 Value] -
[Table1]![Period 1 Value] )/ ([Table1]![Period 1 Value] ))

:

IIF([p1]=0,1,((p2-p1)/p1))
--
Milton Purdy
ACCESS
State of Arkansas


:

I am trying to add a calc'd field to my db.

IIF(P1=0,1,(P2-P1/P1))

What am I missing
Click to expand...
Click to expand...
Click to expand...
 
Hi Steve,

Sure thing.

The expression you entered contains invalid syntax.

You omitted and operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks.

Also, the cursor is then "blinking" on the comma after the 0 if that helps
you.

Thank you.




Steve Sanford said:
It would help to know the specific error message....

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


PFSPUPPY said:
Hi Jeff

I corrected it but an still receiving a syntax error.

IIf ([Master]![Period 1 Value] = 0, 1, ( [Master]![Period 2 Value] -
[Master]![Period 1 Value] )/ ([Master]![Period 1 Value] ))

I have to be missing something really elemental.....



Jeff Boyce said:
It appears your statement is missing a closing parenthesis (you have 4
'open' and 3 'close').

Regards

Jeff Boyce
Microsoft Access MVP


Here is what I entered and it is still telling me it is a syntax error....

IIf ([Table1]![Period 1 Value] = 0, 1, (( [Table1]![Period 2 Value] -
[Table1]![Period 1 Value] )/ ([Table1]![Period 1 Value] ))

:

IIF([p1]=0,1,((p2-p1)/p1))
--
Milton Purdy
ACCESS
State of Arkansas


:

I am trying to add a calc'd field to my db.

IIF(P1=0,1,(P2-P1/P1))

What am I missing
Click to expand...
Click to expand...
Click to expand...
 
Can't see anything wrong.
(Maybe try a period [Master].[Period 1 Value] instead of the exclamation
mark
[Master]![Period 1 Value])


I would start out with:

IIF([Master]![Period 1 Value] = 0, 1, 2)


If no error, then:

IIF([Master]![Period 1 Value] = 0, 1,([Master]![Period 2
Value]-[Master]![Period 1 Value]))


If no error, then:

IIF([Master]![Period 1 Value] = 0, 1,([Master]![Period 2
Value]-[Master]![Period 1 Value])/ ([Master]![Period 1 Value] )))


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


PFSPUPPY said:
Hi Steve,

Sure thing.

The expression you entered contains invalid syntax.

You omitted and operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks.

Also, the cursor is then "blinking" on the comma after the 0 if that helps
you.

Thank you.




Steve Sanford said:
It would help to know the specific error message....

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


PFSPUPPY said:
Hi Jeff

I corrected it but an still receiving a syntax error.

IIf ([Master]![Period 1 Value] = 0, 1, ( [Master]![Period 2 Value] -
[Master]![Period 1 Value] )/ ([Master]![Period 1 Value] ))

I have to be missing something really elemental.....



:

It appears your statement is missing a closing parenthesis (you have 4
'open' and 3 'close').

Regards

Jeff Boyce
Microsoft Access MVP


Here is what I entered and it is still telling me it is a syntax error....

IIf ([Table1]![Period 1 Value] = 0, 1, (( [Table1]![Period 2 Value] -
[Table1]![Period 1 Value] )/ ([Table1]![Period 1 Value] ))

:

IIF([p1]=0,1,((p2-p1)/p1))
--
Milton Purdy
ACCESS
State of Arkansas


:

I am trying to add a calc'd field to my db.

IIF(P1=0,1,(P2-P1/P1))

What am I missing
Click to expand...
Click to expand...
Click to expand...
 
Hi Steve,

Logical approach but no luck. Even at the initial pass through:
IIF([Master]![Period 1 Value] = 0, 1, 2) I get the error, so either
something is wrong on my end still. I switched out the ! for . and that
didn't work...another good idea and something i wouldnt have thought of.

Thanks




Steve Sanford said:
Can't see anything wrong.
(Maybe try a period [Master].[Period 1 Value] instead of the exclamation
mark
[Master]![Period 1 Value])


I would start out with:

IIF([Master]![Period 1 Value] = 0, 1, 2)


If no error, then:

IIF([Master]![Period 1 Value] = 0, 1,([Master]![Period 2
Value]-[Master]![Period 1 Value]))


If no error, then:

IIF([Master]![Period 1 Value] = 0, 1,([Master]![Period 2
Value]-[Master]![Period 1 Value])/ ([Master]![Period 1 Value] )))


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


PFSPUPPY said:
Hi Steve,

Sure thing.

The expression you entered contains invalid syntax.

You omitted and operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks.

Also, the cursor is then "blinking" on the comma after the 0 if that helps
you.

Thank you.




Steve Sanford said:
It would help to know the specific error message....

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


:

Hi Jeff

I corrected it but an still receiving a syntax error.

IIf ([Master]![Period 1 Value] = 0, 1, ( [Master]![Period 2 Value] -
[Master]![Period 1 Value] )/ ([Master]![Period 1 Value] ))

I have to be missing something really elemental.....



:

It appears your statement is missing a closing parenthesis (you have 4
'open' and 3 'close').

Regards

Jeff Boyce
Microsoft Access MVP


Here is what I entered and it is still telling me it is a syntax error....

IIf ([Table1]![Period 1 Value] = 0, 1, (( [Table1]![Period 2 Value] -
[Table1]![Period 1 Value] )/ ([Table1]![Period 1 Value] ))

:

IIF([p1]=0,1,((p2-p1)/p1))
--
Milton Purdy
ACCESS
State of Arkansas


:

I am trying to add a calc'd field to my db.

IIF(P1=0,1,(P2-P1/P1))

What am I missing
Click to expand...
Click to expand...
Click to expand...
 
PFSPUPPY,

Try confirming Form Name and Field Name and what is the Dta Type of the
Field Name...

IIF([FormName]![FieldName] = 0, 1, 2)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

PFSPUPPY said:
Hi Steve,

Logical approach but no luck. Even at the initial pass through:
IIF([Master]![Period 1 Value] = 0, 1, 2) I get the error, so either
something is wrong on my end still. I switched out the ! for . and that
didn't work...another good idea and something i wouldnt have thought of.

Thanks




Steve Sanford said:
Can't see anything wrong.
(Maybe try a period [Master].[Period 1 Value] instead of the
exclamation
mark
[Master]![Period 1 Value])


I would start out with:

IIF([Master]![Period 1 Value] = 0, 1, 2)


If no error, then:

IIF([Master]![Period 1 Value] = 0, 1,([Master]![Period 2
Value]-[Master]![Period 1 Value]))


If no error, then:

IIF([Master]![Period 1 Value] = 0, 1,([Master]![Period 2
Value]-[Master]![Period 1 Value])/ ([Master]![Period 1 Value] )))


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


PFSPUPPY said:
Hi Steve,

Sure thing.

The expression you entered contains invalid syntax.

You omitted and operand or operator, you entered an invalid character
or
comma, or you entered text without surrounding it in quotation marks.

Also, the cursor is then "blinking" on the comma after the 0 if that
helps
you.

Thank you.




:

It would help to know the specific error message....

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


:

Hi Jeff

I corrected it but an still receiving a syntax error.

IIf ([Master]![Period 1 Value] = 0, 1, ( [Master]![Period 2
Value] -
[Master]![Period 1 Value] )/ ([Master]![Period 1 Value] ))

I have to be missing something really elemental.....



:

It appears your statement is missing a closing parenthesis (you
have 4
'open' and 3 'close').

Regards

Jeff Boyce
Microsoft Access MVP


Here is what I entered and it is still telling me it is a
syntax error....

IIf ([Table1]![Period 1 Value] = 0, 1, (( [Table1]![Period 2
Value] -
[Table1]![Period 1 Value] )/ ([Table1]![Period 1 Value] ))

:

IIF([p1]=0,1,((p2-p1)/p1))
--
Milton Purdy
ACCESS
State of Arkansas


:

I am trying to add a calc'd field to my db.

IIF(P1=0,1,(P2-P1/P1))

What am I missing
Click to expand...
Click to expand...
Click to expand...
 
Hi Gina,

I'm not using any forms so I am not sure what you mean. Is there a way to
just do this through SQL in access? I've been trying to use CASE but that
isn't really working out for me. Luckily this isn't something I need done by
the end of today so don't sweat it. I'm just frustrated that Access isn't
easier for something like this. All I am using is one table and just trying
to create a calculated field off a period 1 and period 2 columns (both of
which have numbers in them, and calculating the change--Period1 is sometimes
blank which is why I am doing that first check and setting it to 1 if true)



Gina Whipp said:
PFSPUPPY,

Try confirming Form Name and Field Name and what is the Dta Type of the
Field Name...

IIF([FormName]![FieldName] = 0, 1, 2)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

PFSPUPPY said:
Hi Steve,

Logical approach but no luck. Even at the initial pass through:
IIF([Master]![Period 1 Value] = 0, 1, 2) I get the error, so either
something is wrong on my end still. I switched out the ! for . and that
didn't work...another good idea and something i wouldnt have thought of.

Thanks




Steve Sanford said:
Can't see anything wrong.
(Maybe try a period [Master].[Period 1 Value] instead of the
exclamation
mark
[Master]![Period 1 Value])


I would start out with:

IIF([Master]![Period 1 Value] = 0, 1, 2)


If no error, then:

IIF([Master]![Period 1 Value] = 0, 1,([Master]![Period 2
Value]-[Master]![Period 1 Value]))


If no error, then:

IIF([Master]![Period 1 Value] = 0, 1,([Master]![Period 2
Value]-[Master]![Period 1 Value])/ ([Master]![Period 1 Value] )))


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


:

Hi Steve,

Sure thing.

The expression you entered contains invalid syntax.

You omitted and operand or operator, you entered an invalid character
or
comma, or you entered text without surrounding it in quotation marks.

Also, the cursor is then "blinking" on the comma after the 0 if that
helps
you.

Thank you.




:

It would help to know the specific error message....

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


:

Hi Jeff

I corrected it but an still receiving a syntax error.

IIf ([Master]![Period 1 Value] = 0, 1, ( [Master]![Period 2
Value] -
[Master]![Period 1 Value] )/ ([Master]![Period 1 Value] ))

I have to be missing something really elemental.....



:

It appears your statement is missing a closing parenthesis (you
have 4
'open' and 3 'close').

Regards

Jeff Boyce
Microsoft Access MVP


Here is what I entered and it is still telling me it is a
syntax error....

IIf ([Table1]![Period 1 Value] = 0, 1, (( [Table1]![Period 2
Value] -
[Table1]![Period 1 Value] )/ ([Table1]![Period 1 Value] ))

:

IIF([p1]=0,1,((p2-p1)/p1))
--
Milton Purdy
ACCESS
State of Arkansas


:

I am trying to add a calc'd field to my db.

IIF(P1=0,1,(P2-P1/P1))

What am I missing
Click to expand...
Click to expand...
Click to expand...
 
PFSPUPPY,

Let's BACK-UP... You are not using forms??? Umm, this cannot be done in a
table. Access is NOT Excel on steriods, they just hold data, much like a
bucket holding water. Either move over to Excel, where you can do
calculations in cells OR use a query, where you could do the calcualtion and
display the results OR, better yet, use a form, where you can also display
the results of your query.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

PFSPUPPY said:
Hi Gina,

I'm not using any forms so I am not sure what you mean. Is there a way to
just do this through SQL in access? I've been trying to use CASE but that
isn't really working out for me. Luckily this isn't something I need done
by
the end of today so don't sweat it. I'm just frustrated that Access isn't
easier for something like this. All I am using is one table and just
trying
to create a calculated field off a period 1 and period 2 columns (both of
which have numbers in them, and calculating the change--Period1 is
sometimes
blank which is why I am doing that first check and setting it to 1 if
true)



Gina Whipp said:
PFSPUPPY,

Try confirming Form Name and Field Name and what is the Dta Type of the
Field Name...

IIF([FormName]![FieldName] = 0, 1, 2)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

PFSPUPPY said:
Hi Steve,

Logical approach but no luck. Even at the initial pass through:
IIF([Master]![Period 1 Value] = 0, 1, 2) I get the error, so either
something is wrong on my end still. I switched out the ! for . and
that
didn't work...another good idea and something i wouldnt have thought
of.

Thanks




:

Can't see anything wrong.
(Maybe try a period [Master].[Period 1 Value] instead of the
exclamation
mark
[Master]![Period 1 Value])


I would start out with:

IIF([Master]![Period 1 Value] = 0, 1, 2)


If no error, then:

IIF([Master]![Period 1 Value] = 0, 1,([Master]![Period 2
Value]-[Master]![Period 1 Value]))


If no error, then:

IIF([Master]![Period 1 Value] = 0, 1,([Master]![Period 2
Value]-[Master]![Period 1 Value])/ ([Master]![Period 1 Value] )))


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


:

Hi Steve,

Sure thing.

The expression you entered contains invalid syntax.

You omitted and operand or operator, you entered an invalid
character
or
comma, or you entered text without surrounding it in quotation
marks.

Also, the cursor is then "blinking" on the comma after the 0 if that
helps
you.

Thank you.




:

It would help to know the specific error message....

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


:

Hi Jeff

I corrected it but an still receiving a syntax error.

IIf ([Master]![Period 1 Value] = 0, 1, ( [Master]![Period 2
Value] -
[Master]![Period 1 Value] )/ ([Master]![Period 1 Value] ))

I have to be missing something really elemental.....



:

It appears your statement is missing a closing parenthesis
(you
have 4
'open' and 3 'close').

Regards

Jeff Boyce
Microsoft Access MVP


message
Here is what I entered and it is still telling me it is a
syntax error....

IIf ([Table1]![Period 1 Value] = 0, 1, (( [Table1]![Period 2
Value] -
[Table1]![Period 1 Value] )/ ([Table1]![Period 1 Value] ))

:

IIF([p1]=0,1,((p2-p1)/p1))
--
Milton Purdy
ACCESS
State of Arkansas


:

I am trying to add a calc'd field to my db.

IIF(P1=0,1,(P2-P1/P1))

What am I missing
Click to expand...
Click to expand...
Click to expand...
 
Back
Top