What's wrong with this update query?

[spenz]

Dim strSQL as string

strSQL = "UPDATE tblReceivables SET TotalDays = Date() - (SELECT max(PayDate) FROM tblReceivables as rs WHERE tblReceivables.TransactionID = rs.TransactionID) WHERE TransactionID = " & me.TransactionID & _
"And Active = -1" 

Currentdb.execute strSQL

The code above should be able to update TotalDays field with the calculated value coming from current date minus latest payment date. Unfortunately, Iam encountering an error which states "You must choose an updateable query".

Is there any way i can get this update query to work? Please help me thanks.

 

[boblarson]

1. I think you need a space before your AND

And

2. if that doesn't sort it, use the DateDiff function instead of directly subtracting.

 

[spenz]

1. I think you need a space before your AND

And

2. if that doesn't sort it, use the DateDiff function instead of directly subtracting.

Thanks bob

i tried your solution number one adjusted space before AND but it didn't work

i have a question with your solution number 2 though. How can i get the maximum date as per my example above using datediff?

 

[boblarson]

i have a question with your solution number 2 though. How can i get the maximum date as per my example above using datediff?

You still do what you are doing but,

strSQL = "UPDATE tblReceivables SET TotalDays = DateDiff("d",(SELECT max(PayDate) FROM tblReceivables as rs WHERE tblReceivables.TransactionID = rs.TransactionID), Date()) WHERE TransactionID = " & me.TransactionID & _
" And Active = -1"

 

[spenz]

You still do what you are doing but,

strSQL = "UPDATE tblReceivables SET TotalDays = DateDiff("d",(SELECT max(PayDate) FROM tblReceivables as rs WHERE tblReceivables.TransactionID = rs.TransactionID), Date()) WHERE TransactionID = " & me.TransactionID & _
" And Active = -1"

i think the quote above between "d" causes a syntax error. so i deleted it. after it compiled well i got this error after executing it. Error 3061 Too Few Parameters. Expected 1

 

[boblarson]

i think the quote above between "d" causes a syntax error. so i deleted it. after it compiled well i got this error after executing it. Error 3061 Too Few Parameters. Expected 1

Try this instead:

strSQL = "UPDATE tblReceivables SET TotalDays = " & DateDiff("d",(SELECT max(PayDate) FROM tblReceivables as rs WHERE tblReceivables.TransactionID = rs.TransactionID), Date()) & " WHERE TransactionID = " & me.TransactionID & _
" And Active = -1"

 

[spenz]

Try this instead:

strSQL = "UPDATE tblReceivables SET TotalDays = " & DateDiff("d",(SELECT max(PayDate) FROM tblReceivables as rs WHERE tblReceivables.TransactionID = rs.TransactionID), Date()) & " WHERE TransactionID = " & me.TransactionID & _
" And Active = -1"

Tried this one but got a
compile error: Syntax error

I think subqueries cannot be used to update queries? because if i ran this without using a subquery it worked fine. like using SET TotalDays = date() - TransactionDate but my problem is getting the latest payment to be subtracted on the current date.

the other alternative is opening a recordset i guess but iam hoping to get it through update query so it would execute faster.

 

[boblarson]

Tried this one but got a
compile error: Syntax error

I think subqueries cannot be used to update queries? because if i ran this without using a subquery it worked fine. like using SET TotalDays = date() - TransactionDate but my problem is getting the latest payment to be subtracted on the current date.

the other alternative is opening a recordset i guess but iam hoping to get it through update query so it would execute faster.

You could use a DMax:

strSQL = "UPDATE tblReceivables SET TotalDays = " & DateDiff("d", DMax("PayDate","tblReceivables","[TransactionID] =" & rs.TransactionID), Date()) & " WHERE TransactionID = " & me.TransactionID & _
" And Active = -1"

 

[spenz]

Dmax solution worked! Thank you for your patience bob. rep for you. ^_^

 

[boblarson]

Dmax solution worked! Thank you for your patience bob. rep for you. ^_^

Glad we could get there. I think since it is Monday, the real solution did not come to me as quickly as it should have. But we got there in the end