Updating the date

[encohen]

I have an Access 2000 db and I am having trouble trying to create the right update sql. Here are the two tables I am interested in

ArchInvoices
------------------
InvoiceID, Long <--- PK
InvoiceNumber, Long
SaleType, char(1)
TransactionDate, DateTime
Discount, Integer
WholesaleFlag, Boolean
PaymentType, Char(6)
CustPONumber, Char(30)
ShortName, Char(30)
MdCountyCode, Char(2)
TaxFreeID, Char(30)
Paid, Boolean
FestInvoiceNumber, long

ArchLineItems
----------------------
TransactionType, Char(1)
Units, Integer
UnitCost, Double
BottleSize, Double
InvoiceNumber, Long <----- Foreign Key from ArchInvoices.InvoiceNumber
LineItemID, Long <----- PK
ProductCode, Char(6)
TransactionDate, DateTime
MdCountyCode, Char(2)
ShortName, Char(30)
WholesaleFlag, Boolean
Discount, Integer
TaxFree, Boolean

Ok, this is a 1 to many relationship, with the ArchInvoices on the 1 side and the ArchLineItems on the many side. The InvoiceNumber in both tables is the key -- foreign key link.

For every Invoice there are many line items.

What I want to do is to update the TransactionDate in the ArchLineItems table with the TransactionDate from the ArchInvoices table. So, far I keep on getting syntax errors from Access.

Here is one of my many attempts at this

UPDATE ArchLineItems SET ArchLineItems.TransactionDate = ArchInvoices.TransactionDate WHERE ArchInvoices.InvoiceNumber = ArchLineItems.InvoiceNumber

Access keeps giving me a missing one parameter error. I need some help here. Thank you

Edgar N. Cohen | [email address removed]

[Scott Price]

Hi Edgar and welcome to the Scripts!

Two thoughts... First is to change the order of your WHERE criteria, ArchLineItems should come first, then ArchInvoices.

Second, does it work if you change the WHERE criteria to point to a control on a form or to a manually entered InvoiceNumber instead of pointing the two tables at each other?

UPDATE ArchLineItems SET ArchLineItems.TransactionDate = ArchInvoices.TransactionDate WHERE ArchInvoices.InvoiceNumber = ArchLineItems.InvoiceNumber

Regards,
Scott

p.s. I removed your email address for your own safety! The site rules prohibit posting of email addresses for several reasons. MODERATOR

[ADezii]

I have an Access 2000 db and I am having trouble trying to create the right update sql. Here are the two tables I am interested in

ArchInvoices
------------------
InvoiceID, Long <--- PK
InvoiceNumber, Long
SaleType, char(1)
TransactionDate, DateTime
Discount, Integer
WholesaleFlag, Boolean
PaymentType, Char(6)
CustPONumber, Char(30)
ShortName, Char(30)
MdCountyCode, Char(2)
TaxFreeID, Char(30)
Paid, Boolean
FestInvoiceNumber, long

ArchLineItems
----------------------
TransactionType, Char(1)
Units, Integer
UnitCost, Double
BottleSize, Double
InvoiceNumber, Long <----- Foreign Key from ArchInvoices.InvoiceNumber
LineItemID, Long <----- PK
ProductCode, Char(6)
TransactionDate, DateTime
MdCountyCode, Char(2)
ShortName, Char(30)
WholesaleFlag, Boolean
Discount, Integer
TaxFree, Boolean

Ok, this is a 1 to many relationship, with the ArchInvoices on the 1 side and the ArchLineItems on the many side. The InvoiceNumber in both tables is the key -- foreign key link.

For every Invoice there are many line items.

What I want to do is to update the TransactionDate in the ArchLineItems table with the TransactionDate from the ArchInvoices table. So, far I keep on getting syntax errors from Access.

Here is one of my many attempts at this

UPDATE ArchLineItems SET ArchLineItems.TransactionDate = ArchInvoices.TransactionDate WHERE ArchInvoices.InvoiceNumber = ArchLineItems.InvoiceNumber

Access keeps giving me a missing one parameter error. I need some help here. Thank you

Edgar N. Cohen | [email address removed]

Your UPDATE is probably not working because you need a Unique Index defined for the Referenced Field in the Primary Table. What this means is that the [InvoiceNumber] Field in ArchInvoices must be set to Indexed ==> No Duplicates in order for the SQL Update to work.
.