Syntax for Multiple Inner Joins

[CaiSupport]

Original Code
UPDATE CustomerService AS CS
INNER JOIN AR_95_UDF_ARInvoiceLines AS Inv
ON (CS.[Next Date]=Inv.Nextservicedate) AND ((CS.ID)=Inv.Scheduleid)
SET CS.[Previous Date] = CS.[Next Date], CS.[Next Date] = UpdateServiceDates(CS.[Next Date],CS.Interval,CS.Frequency);

Changed To add an additional File and now I get a syntax error
UPDATE CustomerService AS CS
INNER JOIN AR_95_UDF_ARInvoiceLines AS Inv
ON (CS.[Next Date]=Inv.Nextservicedate) AND ((CS.ID)=Inv.Scheduleid)
INNER JOIN ARN_InvHistoryHeader as Hst
On (Inv.InvoiceNumber = Hst.InvoiceNumber)
SET CS.[Previous Date] = CS.[Next Date], CS.[Next Date] = UpdateServiceDates(CS.[Next Date],CS.Interval,CS.Frequency);

This is the query I did to find the linkage I wanted.
INNER JOIN CustomerService ON (AR_95_UDF_ARInvoiceLines.Scheduleid = CustomerService.ID) AND (AR_95_UDF_ARInvoiceLines.Nextservicedate = CustomerService.[Next Date])) INNER JOIN ARN_InvHistoryHeader ON AR_95_UDF_ARInvoiceLines.InvoiceNumber = ARN_InvHistoryHeader.InvoiceNumber

 

[PHV]

You may try this:
UPDATE CustomerService AS CS
[!]([/!]INNER JOIN AR_95_UDF_ARInvoiceLines AS Inv
ON (CS.[Next Date]=Inv.Nextservicedate) AND ((CS.ID)=Inv.Scheduleid)[!])[/!]
INNER JOIN ARN_InvHistoryHeader as Hst
On (Inv.InvoiceNumber = Hst.InvoiceNumber)
SET CS.[Previous Date] = CS.[Next Date], CS.[Next Date] = UpdateServiceDates(CS.[Next Date],CS.Interval,CS.Frequency);


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CaiSupport]

Thank you for your quick response.

I still get Syntax error in Update statement.

 

[PHV]

Another try.
UPDATE CustomerService AS CS
INNER JOIN AR_95_UDF_ARInvoiceLines AS Inv
ON CS.[Next Date]=Inv.Nextservicedate AND CS.ID=Inv.Scheduleid
SET CS.[Previous Date] = CS.[Next Date], CS.[Next Date] = UpdateServiceDates(CS.[Next Date],CS.Interval,CS.Frequency)
WHERE EXISTS (SELECT * FROM ARN_InvHistoryHeader WHERE InvoiceNumber=Inv.InvoiceNumber)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CaiSupport]

That seems to work, no error messages, but I have one last request. The UpdateServiceDates is using CS.[Next Date], I wanted to change it to calculate based on ARN_InvHistoryHeader.InvoiceDate. When I change it as follows, it prompts me for a date. I do not know access well. If I do put in a date, the calculation uses the prompted date.
UPDATE CustomerService AS CS INNER JOIN AR_95_UDF_ARInvoiceLines AS Inv ON (CS.ID=Inv.Scheduleid) AND (CS.[Next Date]=Inv.Nextservicedate) SET CS.[Previous Date] = CS.[Next Date], CS.[Next Date] = UpdateServiceDates([ARN_InvHistoryHeader].[InvoiceDate],CS.Interval,CS.Frequency)
WHERE EXISTS (SELECT * FROM ARN_InvHistoryHeader WHERE InvoiceNumber=Inv.InvoiceNumber);

Thank you so much for your time.

 

[PHV]

Perhaps this ?
UPDATE CustomerService AS CS
INNER JOIN AR_95_UDF_ARInvoiceLines AS Inv ON CS.ID=Inv.Scheduleid AND CS.[Next Date]=Inv.Nextservicedate
SET CS.[Previous Date]=CS.[Next Date]
,CS.[Next Date]=UpdateServiceDates((SELECT InvoiceDate FROM ARN_InvHistoryHeader WHERE InvoiceNumber=Inv.InvoiceNumber),CS.Interval,CS.Frequency)
WHERE EXISTS (SELECT * FROM ARN_InvHistoryHeader WHERE InvoiceNumber=Inv.InvoiceNumber);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CaiSupport]

I never thought this would be so hard and thank you for all your help. Now it says it is going to run and am I sure I want to run it and the it says "Operation must use
an Updateable query".

Thank you again.

 

[PHV]

And this ?
UPDATE CustomerService AS CS
INNER JOIN AR_95_UDF_ARInvoiceLines AS Inv ON CS.ID=Inv.Scheduleid AND CS.[Next Date]=Inv.Nextservicedate
SET CS.[Previous Date]=CS.[Next Date]
,CS.[Next Date]=UpdateServiceDates(DLookUp("InvoiceDate","ARN_InvHistoryHeader","InvoiceNumber='" & Inv.InvoiceNumber & "'"),CS.Interval,CS.Frequency)
WHERE EXISTS (SELECT * FROM ARN_InvHistoryHeader WHERE InvoiceNumber=Inv.InvoiceNumber);

If InvoiceNumber is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CaiSupport]

Now when I update it says it did not update the 2 fields due to a type conversion failure. I tried it with and without the single quotes. Both dates are defined as Date/Time.

Thank you again for your help.