IsNull Function! Pls help me!

netbytes

Registered User.
Local time
Yesterday, 23:22
Joined
Aug 11, 2008
Messages
33
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.
 
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 think the first of your problems is the part of your query that I have highlighted in RED. Also, I think that correcting the query makes your choices the reverse of what you want them to be.

Try this instead.

Code:
[B][COLOR=royalblue]IIf(Nz([2ndTable].[Field3]),[/COLOR][/B]
[B][COLOR=royalblue]  [2ndTable].[Field3],[/COLOR][/B]
[B][COLOR=royalblue]  [1stTable].[Field1])[/COLOR][/B]

Once you have sorted the first problem out. we can see if youi still have additional questions.
 
I think that it is the use of Nz which results in a string not a number and you are getting a type conversion error.

Try
IIf(Isnull[2ndTable].[Field3]),[1stTable].[Field1],[2ndTable].[Field3]);


Brian
 

Users who are viewing this thread

Back
Top Bottom