Pls help me with this! UPDATE QUERY

[netbytes]

Hi,

I have a problem with regards to the update query I did.

The problem is that my 2 Fields have empty records which are from the 2ndTable but i want to update that by using the 1st Field which has a value from the 1st Table.

First Case:

If Field2 and Field3 has a number > 0 Then

TRUE: Field2 and Field3 will get the record from Field1
FALSE: Field2 and Field3 nothing will happen.

Second Case:

If Field2 and Field3 have empty records Then

TRUE: Field2 and Field3 nothing will happen.
FALSE: Field 2 and Field3 nothing will happen.

Is that what you call the NULL Values? (empty)?

Ow well, heres the example:

BEFORE UPDATING

Field1 ------ Field2 ------ Field3
100 --------- <empty> ---- <empty>
200--------- 150 --------- <empty>
300--------- <empty> ----- 260
400 -------- 300 --------- 250

AFTER UPDATING

Field1 ------ Field2 ------ Field3
100--------- <empty> -- <empty> >>>>>> as you can see nothing happened
200---------- 200 ------ <empty> >>>>>> as you can see Field2 changed and took Field1 record because its > 0
300----------<empty> --- 300 >>>>>> also updated Field2 changed and took Field1 record
400---------- 400 -------- 400 >>>>>> both were updated because same were greater than 0 and took the Field1 record

See after updating the records.

Field 2 and Field 3 took the records from Field1.

NOTE:

TABLE 1

- Field 1

TABLE 2

-Field 2 and Field 3

Here is my code for the Field3

UPDATE 1stTable INNER JOIN 2ndTable ON 1stTable.vendor_partno=2ndTable.[M_PartNumber] SET 2ndTable.Field3 = IIf(Nz([2ndTable].[Field3]>0),[1stTable].[Field1],[2ndTable].[Field3]);

thats it.
but it keeps on erasing the records from the Field3

anyway if this will be repaired. I want also Field2 and Field3 in the same query.

thank you,





and BTW, heres the error said:

Microsoft Office Access did'nt update 1 field(s) due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
Do you want to continue running this type of action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help.

thats it.

 

[ASherbuck]

I really can't understand what you are trying to do. You're second cases true and false are identical, use the first case to do this.

If IsNull(Field2) = true and isnull(field3) then
 case 2
else
 case 1
end if

other than that I can't decipher you.

 

[netbytes]

I really can't understand what you are trying to do. You're second cases true and false are identical, use the first case to do this.

If IsNull(Field2) = true and isnull(field3) then
 case 2
else
 case 1
end if

other than that I can't decipher you.

uhm! actually sir,
its in the example.
I just put CASE
but not really a case statement.

im using UPDATE STATEMENT here. in MS ACCESS

All i need to know is that how to write it in the UPDATE statement using IIF .. coz i had already my code and i need to enhance it more.

thx anyway ^_^