error with WHERE clause

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In the following code my WHERE clause has no effect on query results , ie, I
get results that are not = 4. Also if I change the WHERE to Nameof ="A" I get
an error message indicating that " should be end of statment, So I need to
change to Nameof=A which does nothing to the query results (same as Valueof
problem).

any hints?



Private Sub Command13_Click()

On Error Resume Next
DAO.Workspaces(0).Databases(0).QueryDefs.Delete qry.query3
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("query3")
qdf.SQL = "SELECT Table1.Yearof, Table1.Dateof, Table1.Nameof,
Table1.Valueof FROM Table1 INNER JOIN Table5 ON Table1.Yearof = Table5.Yearof
WHERE (((Table1.Valueof)=4));"
strSql = "DELETE * FROM [query3 DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [query3 DB] SELECT [query3].* FROM [query3]"
CurrentDb.Execute strSql, dbFailOnError
Set qdf = Nothing
Set db = Nothing


End Sub
 
Remove this line
On Error Resume Next

and see if you are getting any error messages that won't change the string
 
Okay this make sense. The "On Error Resume Next" was covering up other errors
in the code - the second line
"DAO.Workspaces(0).Databases(0).QueryDefs.Delete qry.query3" if don't have
that I get an error later on stating that object already exist. But I want
to continue if the query does or does not exist already.

If I delete the query query3 manually and remove the first two line the code
does work for Valueof=4, but does not for Nameof=A (query then treat it as
[A]) or Nameof="A".


--
David McKnight


Ofer said:
Remove this line
On Error Resume Next

and see if you are getting any error messages that won't change the string

--
\\// Live Long and Prosper \\//
BS"D


David McKnight said:
In the following code my WHERE clause has no effect on query results , ie, I
get results that are not = 4. Also if I change the WHERE to Nameof ="A" I get
an error message indicating that " should be end of statment, So I need to
change to Nameof=A which does nothing to the query results (same as Valueof
problem).

any hints?



Private Sub Command13_Click()

On Error Resume Next
DAO.Workspaces(0).Databases(0).QueryDefs.Delete qry.query3
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("query3")
qdf.SQL = "SELECT Table1.Yearof, Table1.Dateof, Table1.Nameof,
Table1.Valueof FROM Table1 INNER JOIN Table5 ON Table1.Yearof = Table5.Yearof
WHERE (((Table1.Valueof)=4));"
strSql = "DELETE * FROM [query3 DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [query3 DB] SELECT [query3].* FROM [query3]"
CurrentDb.Execute strSql, dbFailOnError
Set qdf = Nothing
Set db = Nothing


End Sub
Click to expand...
Click to expand...
 
David McKnight said:
In the following code my WHERE clause has no effect on query results
, ie, I get results that are not = 4. Also if I change the WHERE to
Nameof ="A" I get an error message indicating that " should be end of
statment, So I need to change to Nameof=A which does nothing to the
query results (same as Valueof problem).

any hints?



Private Sub Command13_Click()

On Error Resume Next
DAO.Workspaces(0).Databases(0).QueryDefs.Delete qry.query3
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("query3")
qdf.SQL = "SELECT Table1.Yearof, Table1.Dateof, Table1.Nameof,
Table1.Valueof FROM Table1 INNER JOIN Table5 ON Table1.Yearof =
Table5.Yearof WHERE (((Table1.Valueof)=4));"
strSql = "DELETE * FROM [query3 DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [query3 DB] SELECT [query3].* FROM [query3]"
CurrentDb.Execute strSql, dbFailOnError
Set qdf = Nothing
Set db = Nothing


End Sub
Click to expand...

You probably want something like this:

'----- start of revised (but untested) code -----
Private Sub Command13_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb()

' Load querydef if it exists; create it if it doesn't.
On Error Resume Next ' disable error-handling
Set qdf = db.QueryDefs("query3")
On Error GoTo Err_Handler ' establish error-handling
If qdf Is Nothing Then
Set qdf = db.CreateQueryDef("query3")
End If

' Set the query's SQL
qdf.SQL = _
"SELECT Table1.Yearof, Table1.Dateof, " & _
"Table1.Nameof, Table1.Valueof FROM Table1 " & _
"INNER JOIN Table5 " & _
"ON Table1.Yearof = Table5.Yearof " & _
"WHERE (((Table1.Valueof)=4));"

strSql = "DELETE * FROM [query3 DB]"
db.Execute strSql, dbFailOnError
strSql = "INSERT INTO [query3 DB] SELECT [query3].* FROM [query3]"
db.Execute strSql, dbFailOnError

Exit_Point:
Set qdf = Nothing
Set db = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'----- end of code -----

You could also drop query3 altogether, if you don't need it for some
other purpose, and just load [query3 DB] like this:

strSql = "INSERT INTO [query3 DB] " & _
"SELECT Table1.Yearof, Table1.Dateof, " & _
"Table1.Nameof, Table1.Valueof FROM Table1 " & _
"INNER JOIN Table5 " & _
"ON Table1.Yearof = Table5.Yearof " & _
"WHERE (((Table1.Valueof)=4));"

db.Execute strSql, dbFailOnError
 
Change this line
DAO.Workspaces(0).Databases(0).QueryDefs.Delete qry.query3
To
DAO.Workspaces(0).Databases(0).QueryDefs.Delete ("query3")

and try
--
\\// Live Long and Prosper \\//
BS"D


David McKnight said:
Okay this make sense. The "On Error Resume Next" was covering up other errors
in the code - the second line
"DAO.Workspaces(0).Databases(0).QueryDefs.Delete qry.query3" if don't have
that I get an error later on stating that object already exist. But I want
to continue if the query does or does not exist already.

If I delete the query query3 manually and remove the first two line the code
does work for Valueof=4, but does not for Nameof=A (query then treat it as
[A]) or Nameof="A".


--
David McKnight


Ofer said:
Remove this line
On Error Resume Next

and see if you are getting any error messages that won't change the string

--
\\// Live Long and Prosper \\//
BS"D


David McKnight said:
In the following code my WHERE clause has no effect on query results , ie, I
get results that are not = 4. Also if I change the WHERE to Nameof ="A" I get
an error message indicating that " should be end of statment, So I need to
change to Nameof=A which does nothing to the query results (same as Valueof
problem).

any hints?



Private Sub Command13_Click()

On Error Resume Next
DAO.Workspaces(0).Databases(0).QueryDefs.Delete qry.query3
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("query3")
qdf.SQL = "SELECT Table1.Yearof, Table1.Dateof, Table1.Nameof,
Table1.Valueof FROM Table1 INNER JOIN Table5 ON Table1.Yearof = Table5.Yearof
WHERE (((Table1.Valueof)=4));"
strSql = "DELETE * FROM [query3 DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [query3 DB] SELECT [query3].* FROM [query3]"
CurrentDb.Execute strSql, dbFailOnError
Set qdf = Nothing
Set db = Nothing


End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Control query code with a text box 1
Format query date field with code 2
Multi-Select List Box 2
Multi-Select List Box Code Problem 5
Error 3144 - Syntax Error in UPDATE Statement 1
Code - Can someone tell me what is wrong? 5
Incorrect syntax for dynamic Update query 1
Compile Error Help 1

Back
Top