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.
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.
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.
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.