DoCmd.RunSQL woes with update query

[wkaibigan]

Hi

I am trying to get the following to work but with little success

tempsql = Me.Combo26.Value & Me.Area & Me.Text35 & "NP"

DoCmd.RunSQL ("UPDATE WSLevs SET [WSLevs].[NodePointName]=" & tempsql
& "[WSLevs].
[WaterCourseRef] & Replace([WSLevs].[Reference],""."","""");")

If I just try

DoCmd.RunSQL ("UPDATE WSLevs SET [WSLevs].[NodePointName]=[WSLevs].
[WaterCourseRef] & Replace([WSLevs].[Reference],""."","""");")

The command works.

With the original query I am getting a Missing Operator error. I know
I am missing something obvious but for the life of me I can't figure
out what so anyone can guide me towards the correct syntax for the
first query please?

Thanks.

Nick

 

[Dorian]

You are missing single quotes around the last part.
DoCmd.RunSQL ("UPDATE WSLevs SET [WSLevs].[NodePointName]='" & tempsql
& "[WSLevs].
[WaterCourseRef] & Replace([WSLevs].[Reference],""."","""") & "'" ;")

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[wkaibigan]

Hi Dorian,

Thank you for your reply.

I tried your suggesion and copied and pasted from your message but get
the following error.

Compile error:
Expected: List seperator or )

Any other suggestions?

Cheers.

Nick

 

[wkaibigan]

The following line works as expected

DoCmd.RunSQL ("UPDATE WSLevs SET WSLevs.NodePointName =
[WaterCourseRef] & Replace([Reference],""."","""");")

Howver I need to be able to get values from text and combo boxes to
add to the beginning of the string to be entered as part of the update
process.

tempsql = Me.Combo26.Value & Me.Area & Me.Text35 & "NP"

Pulls the values required for the start part of the string but I can't
seem to find the right way of combining it will the SQL above. An
example of the final string to be inserted into the NodePointName
field would be 10204NP40000103799 where the initial 10 would be taken
from Combo26, the 2 from Me.Area the 04 from Me.Text35, NP is a fixed
value, 4000 is from [WaterCourseRef] and the final digits are drom the
Replace() function.

Regards.

Nick

 

[John Spencer]

It is good technique to build the SQL string in a string variable.

Dim strSQL as String
Dim tempSQL as String

tempsql = Me.Combo26.Value & Me.Area & Me.Text35 & "NP"
strSQL = "UPDATE WSLevs SET [WSLevs].[NodePointName]=""" & tempSQL & """" & _
" & [WSLevs].[WaterCourseRef] & Replace([WSLevs].[Reference],""."","""")"

DoCmd.RunSQL (strSQL)

If that errors, then you can add
Debug.Print strSQL
to your code and examine the SQL for errors. You can even cut and paste the
resulting string into a new query (in SQL View) and attempt to run it. You
will generally get a better error message and a better idea of where the
failure is.

By the way, you do realize that this query is going to update EVERY record in
WSLevs. You have no where clause attached.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[wkaibigan]

Thanks John, that worked a treat.

Yes I do relise that it updates every record but that isn't an issue
as I am dealing with temporary tables that won't contain a great deal
of information. This app is more of a data manipulation exercise than
a data storage on.

Regards.

Nick

 

[Michael]

I'm looking for a clue why

Private Sub test()
Dim strSQL As String
strSQL = "select * from Month"
DoCmd.RunSQL (strSQL)
End Sub

does not work and gives an error complaining that docmd.runsql
"requires an argument consisting of an SQL statement", so either the
command is blind or I am....
(Yes the table 'Month' exists ;-)


Thanks!
M.

 

[John Spencer]

You must use an ACTION query with RunSQL. A SELECT query is not an action
query. Action queries add, modify, or delete records. I'm not sure but DDL
queries might also work with RunSQL.

You can create a querydef and open the query or use the SQL as the source for
a recordset if you want to use a SELECT query. It depends on what you are
trying to accomplish.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Eddy]

SQL statement requires an ; at the end of the statement.

Try:

strSQL = "select * from Month;"

Cheers
Eddy

 

[Marshall Barton]

SQL statement requires an ; at the end of the statement.

Try:

strSQL = "select * from Month;"

In Access/Jet where you can not use SQL procedures, the semi
colon is optional.

As John said, RunSQL (and Execute) require an ACTION query.
If all you want to do is look at a query's datasheet, use
DoCmd.OpenQuery

 

[John Spencer]

No, the semicolon is NOT required in Access and as far as I can recall it is
not required in MS SQL (T-SQL)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County