If Statements

[Scott]

I have an If, Else, Then Statment in a report I am
writing. Depending on if I use ( ) or not I get either a
syntax error, an extra ( error, or a missing operator
error. I have tried many variations of this statement and
cannot get a valid response. Can you point me in the right
direction? Thanks.

The statement is

If
([Base1]-[Base0])<=[BaseLine1] then 0
ElseIf
([Base1]-[Base0])<=[BaseLine2] Then ([Base1]-[Base0])-
[BaseLine1]
Else [BaseLine2]-[BaseLine1]
End If


([Base1]-[Base0]) is a field calculated from a query.
BaseLine 1 is the numerical value 250
BaseLine2 is the numerical value 500

 

[Steve Schapel]

Scott,

Where does this code go, and what are you trying to assign a value to?

- Steve Schapel, Microsoft Access MVP

 

[Larry Linson]

What follows your "Then" and "Else" is not a VBA statement. That is clear.

Can you clarify what it is that you are trying to do with this IF statement?
Are you somehow trying to use it as an expression -- to set a Variable or in
a ControlSource property? What you must do to correct your error will depend
on what it is that you want to do, and that isn't obvious from the code
you've posted.

Larry Linson
Microsoft Access MVP

 

[Scott]

Steve,
Larry,

All of the Bases (0,1 and BaseLine1 and 2) are testing
goals. They change on a periodic basis and are contained
in a table titled testscorelevels. I also have a table
titled testscores in which students monthly test scores
are entered.

Testing is done monthly. I have a report that gives me the
difference between last months score and this months
score. Points are awarded depending on the test score. If
the monthly difference is less than baseline1 I want to
display 0. If it is between baseline1 and baseline2 I want
to display the difference. If greater than baseline2 I
want to display baseline2.

Expample. With baselines of 250 and 500, if a student
scores 200 a zero is displayed. If the score is 300, 50
would be displayed. If the score is 550, 500 would be
displayed.

Hope this makes sense.

Scott.

-----Original Message-----
Scott,

Where does this code go, and what are you trying to assign a value to?

- Steve Schapel, Microsoft Access MVP

I have an If, Else, Then Statment in a report I am
writing. Depending on if I use ( ) or not I get either a
syntax error, an extra ( error, or a missing operator
error. I have tried many variations of this statement and
cannot get a valid response. Can you point me in the right
direction? Thanks.

The statement is

If
([Base1]-[Base0])<=[BaseLine1] then 0
ElseIf
([Base1]-[Base0])<=[BaseLine2] Then ([Base1]-[Base0])-
[BaseLine1]
Else [BaseLine2]-[BaseLine1]
End If


([Base1]-[Base0]) is a field calculated from a query.
BaseLine 1 is the numerical value 250
BaseLine2 is the numerical value 500

.

 

[Steve Schapel]

Scott,

I must apologise for the lack of clarity of my question. I was really
trying to find out whether you are trying to do this data manipulation
on a form, or what, and in response to which database event you wanted
this to happen.

If I now understand you correctly, it sounds like you need to use a
calculated field in a query, using an IIf() function, for example...
Points:
IIf(([Base1]-[Base0])<=[BaseLine1],0,IIf(([Base1]-[Base0])<=[BaseLine2],[Base1]-[Base0]-[BaseLine1],[BaseLine2]-[BaseLine1]))
.... and then use this query as the recordsource of your report.

- Steve Schapel, Microsoft Access MVP

 

[Scott]

Steve,

No apology necessary. Thanks for pointing me in the right
direction, It was just what I needed.

Scott

-----Original Message-----
Scott,

I must apologise for the lack of clarity of my question. I was really
trying to find out whether you are trying to do this data manipulation
on a form, or what, and in response to which database event you wanted
this to happen.

If I now understand you correctly, it sounds like you need to use a
calculated field in a query, using an IIf() function, for example...

Points:
IIf(([Base1]-[Base0])<=[BaseLine1],0,IIf(([Base1]-[Base0])

<=[BaseLine2],[Base1]-[Base0]-[BaseLine1],[BaseLine2]-
[BaseLine1]))
.... and then use this query as the recordsource of your report.

- Steve Schapel, Microsoft Access MVP