Update certain field of one table to another table

[taurusgal83]

Hi there..

I need some help on this problem I'm facing. I'm selecting Receipt No., Item Code, Quantity from Receipt table and wish to update the data into the same field in the Inventory table.

What is the query I should write in order for me to achieve this?

Thanks in advance~



Eli

 

[apr pillai]

UPDATE Inventory INNER JOIN Receipt ON Inventory.IItemCode = Receipt.RItemCode SET Inventory.IReceiptNo = [RReceiptNo], Inventory.IQuantity = [RQuantity];

You must define IItemCode Field in Inventory Table as Primary Key. Item Code must be unique in that Table to do this.

 

[taurusgal83]

Actually this is what I done. I use a queries and select few fields from 2 tables and save as Receipt. Then from this Receipt, I need to update the info into Inventory table. But when i run the update query, it gives me an error message as per attachment.


Can you guide me on this and tell me what should I do?


Thanks in advance~


Eli

 

[jal]

I'm guessing that some of the columns have different datatypes and hence the error. Typically, when working with two columns of different datatypes, you may to cast one column to be of the same datatype as the other. For example if I have a Text column and an integer column:

SET numCol = CLng(textCol)

Here we cast it to a "Long" number which is much like an integer.

 

[taurusgal83]

I have check the data types. Both field are using Text data types but the index part got differences. 1 allow dupplications and another one doesn't allow.

So what can I do?


Thanks in advance~


Eli

 

[jal]

You should post your SQL code.


How many fields are you updating? Just one? The error says, "Type conversion failure" - you say you have checked the datatypes, but for ALL the fields being updated? One of those fields probably has a type mismatch.