Update Query with MAX(Date) from another table

[Swi]

I have the following query I am trying to perform:

conn.Execute "UPDATE Master SET Master.Scan_Date = (SELECT Max(Scan_Date) From IMB_Tracing INNER JOIN IMB_Tracing.IMB_Tracking_Code = Master.IM_Barcode_Digits);"

I get the error "Operation must use an updateable query".

Any ideas on how I can resolve this?

Thanks.

Swi

 

[PHV]

What about this ?

conn.Execute "UPDATE Master M" _
 & " INNER JOIN (SELECT IMB_Tracking_Code,MAX(Scan_date) AS LastDate FROM IMB_Tracing GROUP BY IMB_Tracking_Code) I" _
 & " ON M.IM_Barcode_Digits=I.IMB_Tracking_Code" _
 & " SET M.Scan_Date=I.LastDate"

or this ?

conn.Execute "UPDATE Master" _
 & " SET Scan_Date=DMax(""Scan_Date"",""IMB_Tracing"",""IMB_Tracking_Code='"" & IM_Barcode_Digits & ""'"")"

If IM_Barcode_Digits and IMB_Tracking_Code are numeric then get rid of the single quotes.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Swi]

On the first bit of code I get the same error as my code above.

On the second bit of code I get the following error:

"Undefined function 'DMax' in expression.

Thank you for the help.

Swi

 

[PHV]

And this ?

conn.Execute "UPDATE Master" _
 & " SET Scan_Date=(SELECT Max(Scan_Date) FROM IMB_Tracing WHERE IMB_Tracking_Code=IM_Barcode_Digits)"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Swi]

I also get the error "Operation must use an updateable query".

Swi

 

[Andrzejek]

Do you get the Date you want if you run:

[tt]SELECT Max(Scan_Date)
From IMB_Tracing INNER JOIN
IMB_Tracing.IMB_Tracking_Code = Master.IM_Barcode_Digits[/tt]

I assume [tt]Master.Scan_Date [/tt]is declared as Date

Can you run this without an error:

[tt]UPDATE Master SET Master.Scan_Date = #3/21/2014#[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Swi]

I get a syntax error in the from clause of your first select

Yes, they are declared as dates.

Yes, it will update the table just fine with your last statement.

Thanks.

Swi

 

[PHV]

My last attempt.
Create a query named, say, qryGetLastDate:

SELECT IMB_Tracking_Code,MAX(Scan_date) AS LastDate FROM IMB_Tracing GROUP BY IMB_Tracking_Code

Then, try this:

conn.Execute "UPDATE Master M INNER JOIN qryGetLastDate I" _
 & " ON M.IM_Barcode_Digits=I.IMB_Tracking_Code" _
 & " SET M.Scan_Date=I.LastDate"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

My first Select statement was taken from your original post, it is your Select.
So first thing I would do is make sure my inner-select statement returns what I want - the Max Date

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Swi]

Thanks for the attempts to help. PHV, thanks but I get the updateable error yet again.

Andrzejek, using PHV's code I get the max date but on the connection execute code I receive the error.

I am updating the database in other areas in my app as well with no issue. This is driving me nuts.



Swi