Help with Query ... If greater then insert... Syntax Error

[jennwilson]

I am a somewhat Novice Access user and have limited rather zilch programming skills.

Problem:

I am receiving the following error: "The expression you entered contains invalid syntax'

In the query, I have built several fields containing calculations. In one of the fileds, I am trying to have the field insert one value, if the current value is greater than the other value. For example - if AvgEarn equals 9 and Expected is 8, I want to have the 8 placed in the field. I am trying the following and have tried to state this a few other ways as well.


Points: IIf ([AvgEarn]>[Log]![Expected], [Log]![Expected]


Thank you in advance for your assistance and attention to my question!

Jennifer

[Rabbit]

I am a somewhat Novice Access user and have limited rather zilch programming skills.

Problem:

I am receiving the following error: "The expression you entered contains invalid syntax'

In the query, I have built several fields containing calculations. In one of the fileds, I am trying to have the field insert one value, if the current value is greater than the other value. For example - if AvgEarn equals 9 and Expected is 8, I want to have the 8 placed in the field. I am trying the following and have tried to state this a few other ways as well.


Points: IIf ([AvgEarn]>[Log]![Expected], [Log]![Expected]


Thank you in advance for your assistance and attention to my question!

Jennifer

Assuming you pasted everything correctly, then your syntax is indeed wrong.

It should be:
Points: iif([AvgEarn]>[Log]![Expected],[Log]![Expected],"Value if Expected equals or exceeds AvgEarn")

The syntax of iif is iif(Expression, Value if true, Value if false)
1) You forgot to include a value if false.
2) You forgot to close the function.

[ADezii]

I am a somewhat Novice Access user and have limited rather zilch programming skills.

Problem:

I am receiving the following error: "The expression you entered contains invalid syntax'

In the query, I have built several fields containing calculations. In one of the fileds, I am trying to have the field insert one value, if the current value is greater than the other value. For example - if AvgEarn equals 9 and Expected is 8, I want to have the 8 placed in the field. I am trying the following and have tried to state this a few other ways as well.


Points: IIf ([AvgEarn]>[Log]![Expected], [Log]![Expected]


Thank you in advance for your assistance and attention to my question!

Jennifer

I'm not really sure what you are looking for here, but what is relevant is the Syntax:
IIF(<expression>, <value if True>, <value if False>)

'If AvgEarn is greater than Expected, then the value in the [Expected] Field is returned and placed in the [Points] Field. For any other condition, the value in the [AvgEarn] Field is placed in [Points]. Hope this helps.

Points: IIf([AvgEarn]>[Expected],[Expected],[AvgEarn])

[jennwilson]

Thank you for your assistance. In the field, as suggested, I typed in the following:

Points: IIf([AvgEarn]>[log]![Expected],[Log]![Expected],[AvgEarn])

Total: I selected Expression, tried the Total field blank and selected others. Each time, I received the following error:



You tried to execute a query that does not include the specified expression 'IIf(/ >[log]![Expected],[Log]![Expected],[AvgEarn])' as part of an aggregate function.




Do you have any suggestion as to what I should try now??

Once again, thank you so much for your assistance!!!

PS - I am using Access 2000

[jennwilson]

Thank you Thank you Thank you!!!!!!! with extra chocolate sauce and everything else good in this world!

I am all set now and ready to conquer the task at hand. Once again, thanks for your help you have been a life saver... or well at least a job saver!!!


Thanks again!

[Rabbit]

Does this mean you have the other problem solved?

[NeoPa]

No, I think she's just happy after eating a bowlful of chocolate :D
I think we can consider this one closed.

[jennwilson]

No, I think she's just happy after eating a bowlful of chocolate :D
I think we can consider this one closed.

The issue is closed. I do have another problem regarding counting a data in a field that is of a certain value ... but I am looking through previous posts to hopefully solve the problem first. If not, I'll be a repeat customer!!

Thanks again!

[NeoPa]

We'd love to have you back Jenn.
It sounds like a problem we've dealt with before, but as long as it's clearly explained, we're happy to do it again if you need it.
BTW my address for sending chocolate to is :
........ :)