Append query using an iif function not working within a macro

  • Thread starter Thread starter Jeannie
  • Start date Start date
J

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.
 
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)
 
I used the OpenQuery command inside of a macro.

bhicks11 via AccessMonster.com said:
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.
Click to expand...
Click to expand...
 
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 said:
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.
Click to expand...
Click to expand...
 
Thanks for the further info, Jeannie. And, did you try the alternative
expression that I suggested?
 
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.

Steve Schapel said:
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.
Click to expand...
Click to expand...
 
Jeannie,

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