error with WHERE clause

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
 
G

Guest

Remove this line
On Error Resume Next

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

Guest

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...
 
D

Dirk Goldgar

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
 
G

Guest

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

Code to search all columns in a table for a single search word 4
Query from a Form 1
Querydef Syntax 1
Update table from another table 4
Run time error 2001 5
error in recordset code 1
Opening Query from Form 4
multi field recordset error 1

Top