By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 2,030 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

Update SQL Statement

P: n/a
Can anyone help with this statement? I get a syntax error message.....not
good at this at all.
Thanks

DoCmd.RunSQL "Update JOTable" _
& "INNER JOIN [Attendance Follow Up] ON JOTable.JOID = [Attendance Follow Up].
JOID " _
& "Set [Attendance Follow Up].ActEndDate = "jotable.ActEndDate "" _
& "WHERE ((([Attendance Follow Up].JOID)=[forms]![clientinformation]!
[IJ/JOInformation].form.[joid]));"

--
Message posted via http://www.accessmonster.com

Sep 11 '08 #1
Share this Question
Share on Google+
16 Replies


P: n/a
rebecky via AccessMonster.com wrote:
Can anyone help with this statement? I get a syntax error message.....not
good at this at all.
Thanks

DoCmd.RunSQL "Update JOTable" _
& "INNER JOIN [Attendance Follow Up] ON JOTable.JOID = [Attendance Follow Up].
JOID " _
& "Set [Attendance Follow Up].ActEndDate = "jotable.ActEndDate "" _
& "WHERE ((([Attendance Follow Up].JOID)=[forms]![clientinformation]!
[IJ/JOInformation].form.[joid]));"
Sometimes we might do something like this.
Dim strSQL as String
strSQL = "Update JOTable..."
Docmd.RunSQL strSQL

If it blows up or doesn't work right we might add
Debug.Print strSQL
prior to running the query. We can then open up the Debug's Immediate
Window and see the value of strSQL. We then copy it to the clipboard
and then create a new query (Query/New/Design/Close/View/SQL) and paste
it in the SQL window and then run it. Access will do its best to
identify where the error is.

What I did notice is that you have the lines
"Update JOTable" _
& "INNER JOIN
That would translate to Update JOTableINNER JOIN... There's no space
between Table and Inner.

Also, if you are using date fields you might do something like
DateField = #" & Me.DateField & "#"

I think the best thing to do at this point is try a Debug.Print.

One last thing...I would attempt to avoid spaces in my field
names...unless you like typing [] around everything. I would prefer
using/entering AttendanceFollowUp than [Attendance Follow Up]. Why add
problems to your life?
Sep 11 '08 #2

P: n/a
On Sep 11, 4:30*pm, "rebecky via AccessMonster.com" <u40765@uwe>
wrote:
Can anyone help with this statement? *I get a syntax error message.....not
good at this at all.
Thanks

DoCmd.RunSQL "Update JOTable" _
& "INNER JOIN [Attendance Follow Up] ON JOTable.JOID = [Attendance Follow Up].
JOID " _
& "Set [Attendance Follow Up].ActEndDate = "jotable.ActEndDate "" _
& "WHERE ((([Attendance Follow Up].JOID)=[forms]![clientinformation]!
[IJ/JOInformation].form.[joid]));"

--
Message posted viahttp://www.accessmonster.com
It looks like you may have tested this in Query Designer interface and
saved the SQL. Good idea, but there are a few details that need to be
changed.

Try adding a space between Update JOTable and INNER JOIN, and remove
the inner quotes around jotable.ActEndDate. That will get you closer
at least.

Good luck.
Sep 11 '08 #3

P: n/a
rebecky via AccessMonster.com wrote:
Can anyone help with this statement? I get a syntax error message.....not
good at this at all.
Thanks

DoCmd.RunSQL "Update JOTable" _
& "INNER JOIN [Attendance Follow Up] ON JOTable.JOID = [Attendance Follow Up].
JOID " _
& "Set [Attendance Follow Up].ActEndDate = "jotable.ActEndDate "" _
& "WHERE ((([Attendance Follow Up].JOID)=[forms]![clientinformation]!
[IJ/JOInformation].form.[joid]));"
1. Skip the quotes around jotable.ActEndDate.
2. Skip the "form" in the control reference.
3. Add a blank between JOTable and INNER JOIN.
4. Drop the parentheses, not necessary in this case.
5. Use the .Execute method of the DB object instead of DoCmd.RunSQL.

Dim Db As DAO.Database
Set Db = CurrentDb

Db.Execute "UPDATE JOTable INNER JOIN [Attendance Follow Up] " & _
" ON JOTable.JOID = [Attendance Follow Up].JOID " & _
" SET [Attendance Follow Up].ActEndDate = JOTable.ActEndDate " & _
" WHERE [Attendance Follow Up].JOID =" & _
" [Forms]![ClientInformation]![IJ/JOInformation]![joid];", _
dbFailOnError

Set Db = Nothing

--
Peter Doering [MVP Access]
Sep 11 '08 #4

P: n/a
Peter Doering <no****@doering.orgwrote:
>Dim Db As DAO.Database
Set Db = CurrentDb

Db.Execute ....

Set Db = Nothing
Why not just use Currentdb.execute ....?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Sep 11 '08 #5

P: n/a
Thank you. I DO have MUCH to learn (:

Salad wrote:
>Can anyone help with this statement? I get a syntax error message.....not
good at this at all.
[quoted text clipped - 6 lines]
>& "WHERE ((([Attendance Follow Up].JOID)=[forms]![clientinformation]!
[IJ/JOInformation].form.[joid]));"

Sometimes we might do something like this.
Dim strSQL as String
strSQL = "Update JOTable..."
Docmd.RunSQL strSQL

If it blows up or doesn't work right we might add
Debug.Print strSQL
prior to running the query. We can then open up the Debug's Immediate
Window and see the value of strSQL. We then copy it to the clipboard
and then create a new query (Query/New/Design/Close/View/SQL) and paste
it in the SQL window and then run it. Access will do its best to
identify where the error is.

What I did notice is that you have the lines
"Update JOTable" _
& "INNER JOIN
That would translate to Update JOTableINNER JOIN... There's no space
between Table and Inner.

Also, if you are using date fields you might do something like
DateField = #" & Me.DateField & "#"

I think the best thing to do at this point is try a Debug.Print.

One last thing...I would attempt to avoid spaces in my field
names...unless you like typing [] around everything. I would prefer
using/entering AttendanceFollowUp than [Attendance Follow Up]. Why add
problems to your life?
--
Message posted via http://www.accessmonster.com

Sep 11 '08 #6

P: n/a
Tony Toews [MVP] wrote:
Peter Doering <no****@doering.orgwrote:
>>Dim Db As DAO.Database
Set Db = CurrentDb

Db.Execute ....

Set Db = Nothing

Why not just use Currentdb.execute ....?
Because someone else would have asked "why not use a DB variable". <g>

I consider it better style, although in this case it wouldn't make a
difference, as we are not re-using the instance.
See Marsh Barton's comments on the subject: http://groups.google.com/group/micro...ee006846?hl=en

--
Peter Doering [MVP Access]
Sep 11 '08 #7

P: n/a
Peter Doering <no****@doering.orgwrote:
>Because someone else would have asked "why not use a DB variable". <g>
<chuckle>
>I consider it better style, although in this case it wouldn't make a
difference, as we are not re-using the instance.
See Marsh Barton's comments on the subject: http://groups.google.com/group/micro...ee006846?hl=en
Agreed in this case it wouldn't make a difference. Now if I was ever in a loop then
I'd agree with you. And now that I think about it I know of some code I wrote about
five years ago where I was doing recordset opens inside the loop. Depending on the
volumes it could run for upwards of an hour calculating preliminary pricing for
invoices.

Hmm, I'll have to think about this a bit more. Next time I encounter this scenario
I'll do some testing.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Sep 12 '08 #8

P: n/a
"Tony Toews [MVP]" <tt****@telusplanet.netwrote in
news:2v********************************@4ax.com:
Now if I was ever in a loop then
I'd agree with you. And now that I think about it I know of some
code I wrote about five years ago where I was doing recordset
opens inside the loop. Depending on the volumes it could run for
upwards of an hour calculating preliminary pricing for invoices.
Er, what?

Why would anyone with half a brain *ever* this inside a loop:

Do Until ...
Set db = CurrentDB()
Loop

Given that the code is running in the MDB that CurrentDB() points
to, it can't change during the duration of the loop, so setting a db
variable inside the loop is just, well, er, um, BLOODY STUPID.

I use a cached database variable that is initialized to point to
CurrentDB() in the app's startup routine, so I never ever code Set
db = CurrentDB(), instead just using my LocalDB function directly.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Sep 13 '08 #9

P: n/a
"David W. Fenton" <XX*******@dfenton.com.invalidwrote:
>Now if I was ever in a loop then
I'd agree with you. And now that I think about it I know of some
code I wrote about five years ago where I was doing recordset
opens inside the loop. Depending on the volumes it could run for
upwards of an hour calculating preliminary pricing for invoices.

Er, what?

Why would anyone with half a brain *ever* this inside a loop:

Do Until ...
Set db = CurrentDB()
Loop
I was thinking more in terms of
currentdb.execute
or
Set rs = CurrentDb.OpenRecordset
if they were inside a loop.

It would occur to me to put the set db = currentdb outside the loop. Quite obvious
actually. But it's a lot less obvious to put the currentdb or set rs = currentdb as
database variables outside the loop.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Sep 13 '08 #10

P: n/a
"Tony Toews [MVP]" <tt****@telusplanet.netwrote in
news:bm********************************@4ax.com:
"David W. Fenton" <XX*******@dfenton.com.invalidwrote:
>>Now if I was ever in a loop then
I'd agree with you. And now that I think about it I know of
some code I wrote about five years ago where I was doing
recordset opens inside the loop. Depending on the volumes it
could run for upwards of an hour calculating preliminary pricing
for invoices.

Er, what?

Why would anyone with half a brain *ever* this inside a loop:

Do Until ...
Set db = CurrentDB()
Loop

I was thinking more in terms of
currentdb.execute
or
Set rs = CurrentDb.OpenRecordset
if they were inside a loop.

It would occur to me to put the set db = currentdb outside the
loop. Quite obvious actually.
That's a bad idea though, because of the collections refresh that
happens when CurrentDB is called.
But it's a lot less obvious to put the currentdb or set rs =
currentdb as database variables outside the loop.
Er, what? You mean Set db = CurrentDB? Saying "set rs = currentdb"
is nonsense.

If you're running a series of Executes inside a loop, it should be
done like this:

Set db = CurrentDB
Do Until ...
db.Execute ...
Loop

The calling of CurrentDB should never fall inside a loop, I think,
as you never need refreshed collections within the loop.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Sep 13 '08 #11

P: n/a
"David W. Fenton" <XX*******@dfenton.com.invalidwrote:
>It would occur to me to put the set db = currentdb outside the
loop. Quite obvious actually.

That's a bad idea though, because of the collections refresh that
happens when CurrentDB is called.
We're agreeing here. so I'm a bit confused.
>But it's a lot less obvious to put the currentdb or set rs =
currentdb as database variables outside the loop.

Er, what? You mean Set db = CurrentDB? Saying "set rs = currentdb"
is nonsense.
No, that's not what I'm saying. Let me rephrase that.

But it's a lot less obvious to put the currentdb as a database variable outside of
the loop than it is to use
currentdb.execute ...
or
set rs =currentdb. ...
inside the loop.
>If you're running a series of Executes inside a loop, it should be
done like this:

Set db = CurrentDB
Do Until ...
db.Execute ...
Loop

The calling of CurrentDB should never fall inside a loop, I think,
as you never need refreshed collections within the loop.
Agreed. That's what I was trying to state.

But I will repeat that it's not at all obvious that you should do so.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Sep 14 '08 #12

P: n/a
"Tony Toews [MVP]" <tt****@telusplanet.netwrote in
news:ge********************************@4ax.com:
"David W. Fenton" <XX*******@dfenton.com.invalidwrote:
>>It would occur to me to put the set db = currentdb outside the
loop. Quite obvious actually.

That's a bad idea though, because of the collections refresh that
happens when CurrentDB is called.

We're agreeing here. so I'm a bit confused.
We may very well be agreeing, but you're stating your position in a
manner that has deeply confused me!
>>But it's a lot less obvious to put the currentdb or set rs =
currentdb as database variables outside the loop.

Er, what? You mean Set db = CurrentDB? Saying "set rs = currentdb"
is nonsense.

No, that's not what I'm saying. Let me rephrase that.

But it's a lot less obvious to put the currentdb as a database
variable outside of the loop than it is to use
currentdb.execute ...
or
set rs =currentdb. ...
inside the loop.
I still don't get it, unless you mean:

Set rs = CurrentDB.OpenRecordset(...)

I never do that, because worry about controlling the scope of the
variables.
>>If you're running a series of Executes inside a loop, it should be
done like this:

Set db = CurrentDB
Do Until ...
db.Execute ...
Loop

The calling of CurrentDB should never fall inside a loop, I think,
as you never need refreshed collections within the loop.

Agreed. That's what I was trying to state.

But I will repeat that it's not at all obvious that you should do
so.
I beg to differ. It may not be obvious to someone who's given it no
thought, but if you understand the code you're writing, it jumps out
as obvious.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Sep 15 '08 #13

P: n/a
David W. Fenton wrote:
"Tony Toews [MVP]" :
>>
But it's a lot less obvious to put the currentdb as a database
variable outside of the loop than it is to use
currentdb.execute ...
or
set rs =currentdb. ...
inside the loop.

I still don't get it, unless you mean:

Set rs = CurrentDB.OpenRecordset(...)
I understand Tony's statement with emphasis on the beginning of the
statement, being

set rs = currentdb. ... (where <...is OpenRecordset)
vs.
set rs = db. ... (dto.)

In his 2nd answer Tony explains it by writing <currentdb><dot><space><3
dots>.
I never do that, because worry about controlling the scope of the
variables.
Now I don't understand. You never do

Set <whatever= CurrentDB.<whatever>
or
Set <whatever= DB.<whatever>
?

--
Peter Doering [MVP Access]
Sep 15 '08 #14

P: n/a
"David W. Fenton" <XX*******@dfenton.com.invalidwrote:
>We're agreeing here. so I'm a bit confused.

We may very well be agreeing, but you're stating your position in a
manner that has deeply confused me!
<smile Ayup.
>I still don't get it, unless you mean:

Set rs = CurrentDB.OpenRecordset(...)
Currect.
>But I will repeat that it's not at all obvious that you should do
so.

I beg to differ. It may not be obvious to someone who's given it no
thought, but if you understand the code you're writing, it jumps out
as obvious.
For me that wasn't at all obvious. For you it might very well have been. For me
one of those blind spots.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Sep 15 '08 #15

P: n/a
"Tony Toews [MVP]" <tt****@telusplanet.netwrote in
news:lm********************************@4ax.com:
"David W. Fenton" <XX*******@dfenton.com.invalidwrote:
>>But I will repeat that it's not at all obvious that you should
do so.

I beg to differ. It may not be obvious to someone who's given it
no thought, but if you understand the code you're writing, it
jumps out as obvious.

For me that wasn't at all obvious. For you it might very well
have been. For me one of those blind spots.
Things that can't change during the loop should not be set during
the duration of the loop -- that's a pretty simple principle, and
it's obvious that CurrentDB can't change its return value during a
loop.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Sep 15 '08 #16

P: n/a
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@74.209.1 36.97:
Things that can't change during the loop should not be set during
the duration of the loop -- that's a pretty simple principle, and
it's obvious that CurrentDB can't change its return value during a
loop.
Multiple CurrentDBs never "return" the same value (point to the same
object).
(CurrentDB is CurrentDB ) is never true.
This is, IMO, the reason for CurrentDB's existence, viz, it always points
to a new fresh instance of the database.
I suppose, if within a loop we made changes to the database, say, created a
new object, we might want a pointer to a new fresh instance of the
database, that is one which contained that new object or was aware of its
existence, but I think such a situation would occur infrequently.

--
-
lyle fairfield
Sep 16 '08 #17

This discussion thread is closed

Replies have been disabled for this discussion.