Append query using an iif function not working within a macro

[Jeannie]

I have an append query that uses the following logic:

If the value on a related, open form is 0, append the [Scrap Price] value,
otherwise append 0.

actual calculation:
IIf([Forms]![ORDER_MASTER]![FORMULA].[Form]![PRICING_UNAVAIL]=0,[SCRAP
PRICE],0).

The query works if I run it independently, but will not make the change if I
embed it in a macro. It appears that the iif function is the problem.

 

[Steve Schapel]

Jeannie,

I can't really offer any insights into why you are having this problem.
I assume you are using an OpenQuery action in your macro? I assume
the ORDER_MASTER form is open at the time? You didn't mention what
actually happens - i.e. macro fails, error message, append proceeds with
no data, append proceeds with incorrect data, etc.

Anyway, for a lark, try changing it to…

actual calculation:
[SCRAP PRICE]*Abs([Forms]![ORDER_MASTER]![FORMULA]![PRICING_UNAVAIL]<>0)

 

[Jeannie]

I used the OpenQuery command inside of a macro.

Sorry Jeannie, what do you mean by embed it into a macro?

Bonnie
http://www.dataplus-svc.com

I have an append query that uses the following logic:

If the value on a related, open form is 0, append the [Scrap Price] value,
otherwise append 0.

actual calculation:
IIf([Forms]![ORDER_MASTER]![FORMULA].[Form]![PRICING_UNAVAIL]=0,[SCRAP
PRICE],0).

The query works if I run it independently, but will not make the change if I
embed it in a macro. It appears that the iif function is the problem.

 

[Jeannie]

Steve,
Yes, I am using the OpenQuery action in a macro. And yes, the form is open.

The append results in "0" in the [SCRAP PRICE] field, instead of a value.
But if I run the query myself, the [SCRAP PRICE] field is updated with a
correct value.

Jeannie,

I can't really offer any insights into why you are having this problem.
I assume you are using an OpenQuery action in your macro? I assume
the ORDER_MASTER form is open at the time? You didn't mention what
actually happens - i.e. macro fails, error message, append proceeds with
no data, append proceeds with incorrect data, etc.

Anyway, for a lark, try changing it to…

actual calculation:
[SCRAP PRICE]*Abs([Forms]![ORDER_MASTER]![FORMULA]![PRICING_UNAVAIL]<>0)

--
Steve Schapel, Microsoft Access MVP

I have an append query that uses the following logic:

If the value on a related, open form is 0, append the [Scrap Price] value,
otherwise append 0.

actual calculation:
IIf([Forms]![ORDER_MASTER]![FORMULA].[Form]![PRICING_UNAVAIL]=0,[SCRAP
PRICE],0).

The query works if I run it independently, but will not make the change if I
embed it in a macro. It appears that the iif function is the problem.

 

[Steve Schapel]

Thanks for the further info, Jeannie. And, did you try the alternative
expression that I suggested?

 

[Jeannie]

Steve,
Thanks for your reply. I worked with the macro and discovered the problem.
At the point of appending the data, it appears the record was in an edit
mode, so it didn't perform the calculation correctly. By forcing a save
prior to the query, it must have recognized the reference to the form/record
and perfomed the append successfully.

Thanks for the further info, Jeannie. And, did you try the alternative
expression that I suggested?

--
Steve Schapel, Microsoft Access MVP

Steve,
Yes, I am using the OpenQuery action in a macro. And yes, the form is open.

The append results in "0" in the [SCRAP PRICE] field, instead of a value.
But if I run the query myself, the [SCRAP PRICE] field is updated with a
correct value.

 

[Steve Schapel]

Jeannie,

Ah, ok, that makes sense. Thanks for letting us know, and pleased to
know that it is working now.