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 16  2175 
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?
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.
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]
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/
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
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]
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/
"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/
"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/
"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/
"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/
"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/
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]
"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/
"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/
"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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Mark A Framness |
last post by:
Greetings,
I am working on a project and we need to write a conversion script to
initialize a new field on a table. The number of records on this table
is on the order of millions so routine...
|
by: Dave |
last post by:
I have 2 tables, one with names, and another with addresses, joined by their
CIVICID number (unique to the ADDRESSINFO table) in Oracle.
I need to update a field in the NAMEINFO table for a...
|
by: Lauren Quantrell |
last post by:
In VBA, I constructed the following to update all records in
tblmyTable with each records in tblmyTableTEMP having the same
UniqueID:
UPDATE
tblMyTable RIGHT JOIN tblMyTableTEMP ON...
|
by: pb648174 |
last post by:
I have a single update statement that updates the same column multiple
times in the same update statement. Basically i have a column that
looks like .1.2.3.4. which are id references that need to...
|
by: Mike Leahy |
last post by:
Hello all,
This question is related to updating tables - is there any way to calculate or
update the values in a column in a table to the values in a field produced by
a query result? An...
|
by: amitbadgi |
last post by:
HI i am getting the foll error while conv an asp application to
asp.net
Exception Details: System.Runtime.InteropServices.COMException: Syntax
error in UPDATE statement.
Source Error:
Line...
|
by: Grant McLean |
last post by:
Hi
First a simple question ...
I have a table "access_log" that has foreign keys "app_id" and
"app_user_id" that reference the "application_type" and "app_user"
tables. When I insert into...
|
by: FayeC |
last post by:
I really need help figuring this out.
i have a db with mostly text fields but 2.
The user_id field is an autonumber (key) and the user_newsletter is a
number (1 and 0) field meaning 1 yes the ...
|
by: Steve |
last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement
I got ASP error number 13 when I use the SELECT...FOR UPDATE statement
as below.
However, if I use SELECT statement without...
|
by: Michel Esber |
last post by:
Hi all,
DB2 V8 LUW FP 15
There is a table T (ID varchar (24), ABC timestamp). ID is PK.
Our application needs to frequently update T with a new value for ABC.
update T set ABC=? where ID...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
