Append Query & Null Values

[David Eagar]

Have the following scenario with perhaps inbuilt fatal flaw

Building an Inventory Management db and in the process of constructing the tools to move some inventory from 1 location in the warehouse to another (purely an internal transfer)

Part of my table design includes an allocated quantity field to record any outstanding orders for this inventory unit (I am planning on a built in LIFO system, which I haven't got around to contemplating yet)

All went well, the stock was decreased from the existing location using an update query. but failed to materialise in the new location using an append query

After much yelling and cursing at the offending query, finally did a search and found the following:

Update queries will not work if you trying to append a primary key value (knew that one) OR you are trying to append a null value into a field

In the real world, it may well be possible that I will have 0 allocations for a given inventory unit

Does anybody know a way around this seeming impass, or am I faited to rebuild my tables again?

Thanks in advance for any enlightenment

 

[KeithG]

use the nz function to change the null value to a zero. Nz([FieldName],0)

 

[David Eagar]

Thanks for the quick response - I have seen a few posts with the nZ function, but have bever used one - this looks like my chance

 

[David Eagar]

When I said all went well, perhaps I was a little hasty

Having applied the Nz fix, I get violation errors and can't update or append??

 

[KeithG]

Can you post your Sql?

 

[David Eagar]

INSERT INTO 1tblInventory ( IItem, IQty, ILoc, IDate, IAlloc )
SELECT [2qryInvMove2].IItem, [2qryInvMove2].IncQty, [2qryInvMove2].NewLoc, [2qryInvMove2].IDate, [2qryInvMove2].NAlloc
FROM 2qryInvMove2;


If I run the update query manually, don't get the error message (mind you, it still doesn't append), when I run it from a macro, I do get the error message

 

[KeithG]

where is the nz funtion?

 

[KeithG]

If I run the update query manually, don't get the error message (mind you, it still doesn't append),

If you dont get an error and the record is not Appended are you sure there is a record to Append? Have you tried to view the results before running the append query?

 

[David Eagar]

NAlloc: Nz([IAlloc],0)

This is set in the 2qryInvMove2 where it reads the entry data from the form

Objective is to append the NAlloc value into the IAlloc field in the table


ps. don't spend too much time on this (it is purely a training exercise for me and not a live db and I still have some more things to try) but thanks anyway for the assistance - I can post the db if you want to delve further

 

[KeithG]

I don't have Ms Access on my home PC but I can take a look Monday when I get to work if you would like.

 

[David Eagar]

If you dont get an error and the record is not Appended are you sure there is a record to Append? Have you tried to view the results before running the append query?

Ran it all in select mode first and data looked OK

When converting to append mode, gives notice that I am about to append 1 record, but checking the table, data ain't there

 

[KeithG]

Interesting, you actually hit okay to the message but the row is not Appended?

 

[David Eagar]

Interesting, but not life threatening - certainly not worth taking up your time at work - I'll battle on and post any progress

Thanks muchly anyway

 

[KeithG]

No problem, have a nice weekend!

 

[David Eagar]

Update - a lot of these problems were due to a typo, appently queries run better when the contain the correct field names!! (idiot)

However, am back to original problem, the append query works when all fields have non null/0 data, but fails when the Allocation field is 0

Any further suggestions?

 

[ajetrumpet]

David,

Have you tried just changing all of your NULL field values in your table (source of records appended) to blanks?? There is no problem using the APPEND for records with blank values, because they ARE still values...much different than NULLS. If you want to change the NULLS, just update the table...

UPDATE table SET
[table].[allocation] = "" 
WHERE [table].[allocation] IS NULL;

I don't think you can do this with number or currency fields. You might have to change the field to the "text" data type before you update the NULLS (I think "blanks" are viewed as text strings, which would be invalid data for a number field)....

 

[David Eagar]

Actually, the data for this field in the table is 0, so I may have been a bit misleading in my title, the query still fails if the value in this field = 0

 

[KeithG]

Is there a validation rule defined in the field of the table your appending too? If so are you violating it with the 0 value?

 

[David Eagar]

Not that I have conciously added, but will go & double check

 

[David Eagar]

There are no validation rules on this field in the table