Can someone help me with this pls?

[netbytes]

Hi!

I just want to know how to write this in an update query.

If map > 0 then productprice = map
ElseIf map = 0 then productprice <> map

I have two tables:

QualityMatters Table
- productprice field

Teledynamics Table
- map field

so i used INNER JOIN here do i need to use Iff?

Example:

Before updating ...

productprice map

120.00 0
150.00 250.00

After updating ...

productprice map

120.00 0 >>>> nothing happened
150.00 150.00 >>>> productprice = map

> Sorry! for the alignment <

do i need to use ISNULL???

hope someone can answer my questions.

Thank you,
Have a great day!

 

[CraigDolphin]

ElseIf map = 0 then productprice <> map

Ok, answer this for me:

map = 0
productprice = <insert a value here please>

(your earlier post simply says that when map =0, product price does not equal map....but it says nothing about what it should equal)

 

[netbytes]

Ok, answer this for me:

map = 0
productprice = <insert a value here please>

(your earlier post simply says that when map =0, product price does not equal map....but it says nothing about what it should equal)

oops sorry
I think i just want to transfer the record from the productprice field to the map field just like in my example and btw what if its 0 or NULL?

If map > 0 then productprice = map
elseif map = 0 or a null value the map field will not be changed.


thank you!

 

[CraigDolphin]

And what is the name of the field used to join the two tables?

 

[netbytes]

elo!

UPDATE QualityMatters INNER JOIN Teledynamics ON QualityMatters.vendor_partno=Teledynamics.[Item Number] SET Teledynamics.MAP = IIf(Teledynamics.MAP>0,QualityMatters.productprice=Teledynamics.MAP,0);


this one gave my MAP field all 0 =.=
how to transfer productprice to the map field?
can anyone answer me pls.

 

[CraigDolphin]

First, make a backup in case it goes awry and you need to start over.

Then, try
UPDATE QualityMatters INNER JOIN Teledynamics ON QualityMatters.vendor_partno = Teledynamics.[Item Number] SET Teledynamics.map = IIf([Teledynamics].[MAP]>0,[QualityMatters].[productprice],[Teledynamics].[MAP]);

 

[netbytes]

thank you

First, make a backup in case it goes awry and you need to start over.

Then, try
UPDATE QualityMatters INNER JOIN Teledynamics ON QualityMatters.vendor_partno = Teledynamics.[Item Number] SET Teledynamics.map = IIf([Teledynamics].[MAP]>0,[QualityMatters].[productprice],[Teledynamics].[MAP]);

this one helped me a alot.
btw how about a NULL VALUE?
IIf([Teledynamics].[MAP]> NULL?

it can be used or not?

thanks again..
Added Reputation to you

 

[CraigDolphin]

Well, the query doesn't actually say IIf([Teledynamics].[MAP]> NULL ..., it compares the value to the number 0.

If you want to say:
If map is not null, then use productprice, otherwise use the map value

then you'd use
IIf(Not IsNull([Teledynamics].[MAP]),[QualityMatters].[productprice],[Teledynamics].[MAP])

If you want to say:
if map = 0, or is null, use productprice, otherwise use map value

then you'd probably use
IIf(Nz([Teledynamics].[MAP],0) > 0,[QualityMatters].[productprice],[Teledynamics].[MAP])

NB: the Nz function replaces nulls with a value: in this case 0.
the isnull function returns a true or false depending on whether the value in the field is a null or not.