Iif returns True part not False

[james d]

I have two tables and the following code to determine price for an item when
entering a new record. It is returning the [Current Price.Sales Price] when
it is True but never give me the False


=IIf([Current Price.Sales Price] Is Not Null,[Current Price.Sales
Price],[TMC Price.DA Price])

 

[KARL DEWEY]

Where are you creating the new record - in a form?
Is the table [TMC Price] in the query feeding the form?

 

[Golfinray]

Are both tables pulled in and linked properly? Is the current sales price
ever null?

 

[james d]

Yes, in a form and I am using the expression builder. When I look at
properties the Iif is in before update. I assume this will pull in the price
once the item is entered?

Where are you creating the new record - in a form?
Is the table [TMC Price] in the query feeding the form?
--
KARL DEWEY
Build a little - Test a little

I have two tables and the following code to determine price for an item when
entering a new record. It is returning the [Current Price.Sales Price] when
it is True but never give me the False


=IIf([Current Price.Sales Price] Is Not Null,[Current Price.Sales
Price],[TMC Price.DA Price])

 

[John Spencer]

I might try rearranging the brackets to the following.

=IIf([Current Price].[Sales Price] Is Not Null,
[Current Price].[Sales Price],[TMC Price].[DA Price])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Duane Hookom]

If you have some code, you should provide it so we understand the context of
your issue. I'm not sure if you are setting an actual value or a default.

If this is in a form, you should remove the table names and try:
=Nz([Sales Price], [DA Price])

--
Duane Hookom
Microsoft Access MVP

Yes, in a form and I am using the expression builder. When I look at
properties the Iif is in before update. I assume this will pull in the price
once the item is entered?

Where are you creating the new record - in a form?
Is the table [TMC Price] in the query feeding the form?
--
KARL DEWEY
Build a little - Test a little

I have two tables and the following code to determine price for an item when
entering a new record. It is returning the [Current Price.Sales Price] when
it is True but never give me the False


=IIf([Current Price.Sales Price] Is Not Null,[Current Price.Sales
Price],[TMC Price.DA Price])

 

[james d]

didn't help.... Nz didn't return any thing. Don't have a query pulling data,
just tables and a form. relationships are as follows:
contacts.customer-current price.customer
contacts.company-existing part quote.company
current price.item-tmc price.item
tmc price.std-std pack.std

I might try rearranging the brackets to the following.

=IIf([Current Price].[Sales Price] Is Not Null,
[Current Price].[Sales Price],[TMC Price].[DA Price])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have two tables and the following code to determine price for an item when
entering a new record. It is returning the [Current Price.Sales Price] when
it is True but never give me the False


=IIf([Current Price.Sales Price] Is Not Null,[Current Price.Sales
Price],[TMC Price.DA Price])

 

[KARL DEWEY]

Use a query with the two tables joined on your 'product' field to feed the
form.
--
KARL DEWEY
Build a little - Test a little

Yes, in a form and I am using the expression builder. When I look at
properties the Iif is in before update. I assume this will pull in the price
once the item is entered?

Where are you creating the new record - in a form?
Is the table [TMC Price] in the query feeding the form?
--
KARL DEWEY
Build a little - Test a little

I have two tables and the following code to determine price for an item when
entering a new record. It is returning the [Current Price.Sales Price] when
it is True but never give me the False


=IIf([Current Price.Sales Price] Is Not Null,[Current Price.Sales
Price],[TMC Price.DA Price])

 

[Linq Adams via AccessMonster.com]

In VBA behind an Access form, the correct syntax would be something like

Not IsNull(Field)

rather than

[Field] Is Not Null