Error 3075 w/ query

C

cmarsh

I cant figure out why this errors out upon Current.DB.QueryDefs line listed
below. Any help would be appreciated!!

**(This is a form module)
Private Sub Command17_Click()
Dim strSQL As String

If Not Buildsqlstring(strSQL) Then
MsgBox "There was an error!!"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("Reportq").SQL = strSQL
End Sub


Function Buildsqlstring(strSQL As String) As Boolean
Dim strselect As String
Dim strfrom As String
Dim strwhere As String
strselect = "s.* "
strfrom = "[Doc Review T] s "
If chkprogram Then
strwhere = strwhere & " AND s.Program = " & cmbprogram
End If
If chkdocno Then
strwhere = strwhere & " AND s.[Doc No] = " & txtdocno
End If
If chkhwpn Then
strwhere = strwhere & " AND s.[HW PN] = " & txthwpn
End If
If chkqae Then
strwhere = strwhere & " AND s.QAE = " & cmbqae
End If
If chkacc Then
strwhere = strwhere & " AND s.Accepted =" & cmbacc
End If
If chkdate Then
If Not IsNull(txtdatefrom) Then
strwhere = strwhere & " AND s.[Initial Review] >= " & "#"
End If
If Not IsNull(txtdateto) Then
strwhere = strwhere & " AND s.[Initial Review] <= " & "#"
End If
End If
strSQL = "SELECT " & strselect
strSQL = strSQL & "FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & "WHERE " & Mid$(strwhere, 6)
Buildsqlstring = True
End Function
 
D

Douglas J. Steele

You have no space between the table name and the keyword WHERE, nor between
the SQL string and the keyword FROM.

Change

strSQL = strSQL & "FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & "WHERE " & Mid$(strwhere, 6)

to

strSQL = strSQL & " FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & " WHERE " & Mid$(strwhere, 6)

You might also need to change

strfrom = "[Doc Review T] s "

to

strfrom = "[Doc Review T] AS s "
 
C

cmarsh

I made the change and still have the same problem. It is in the WHERE part
of the query. This is the message I get.

--Syntax error (missing operator) in query expression '[Doc Review
T].Program = 1'

I wonder if it has to do with what is selected needs to be put in " " or
something along those lines? Created a basic query and what is displayed in
the SQL view is the criteria is in " ".

Douglas J. Steele said:
You have no space between the table name and the keyword WHERE, nor between
the SQL string and the keyword FROM.

Change

strSQL = strSQL & "FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & "WHERE " & Mid$(strwhere, 6)

to

strSQL = strSQL & " FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & " WHERE " & Mid$(strwhere, 6)

You might also need to change

strfrom = "[Doc Review T] s "

to

strfrom = "[Doc Review T] AS s "



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


cmarsh said:
I cant figure out why this errors out upon Current.DB.QueryDefs line listed
below. Any help would be appreciated!!

**(This is a form module)
Private Sub Command17_Click()
Dim strSQL As String

If Not Buildsqlstring(strSQL) Then
MsgBox "There was an error!!"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("Reportq").SQL = strSQL
End Sub


Function Buildsqlstring(strSQL As String) As Boolean
Dim strselect As String
Dim strfrom As String
Dim strwhere As String
strselect = "s.* "
strfrom = "[Doc Review T] s "
If chkprogram Then
strwhere = strwhere & " AND s.Program = " & cmbprogram
End If
If chkdocno Then
strwhere = strwhere & " AND s.[Doc No] = " & txtdocno
End If
If chkhwpn Then
strwhere = strwhere & " AND s.[HW PN] = " & txthwpn
End If
If chkqae Then
strwhere = strwhere & " AND s.QAE = " & cmbqae
End If
If chkacc Then
strwhere = strwhere & " AND s.Accepted =" & cmbacc
End If
If chkdate Then
If Not IsNull(txtdatefrom) Then
strwhere = strwhere & " AND s.[Initial Review] >= " & "#"
End If
If Not IsNull(txtdateto) Then
strwhere = strwhere & " AND s.[Initial Review] <= " & "#"
End If
End If
strSQL = "SELECT " & strselect
strSQL = strSQL & "FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & "WHERE " & Mid$(strwhere, 6)
Buildsqlstring = True
End Function
Click to expand...
Click to expand...
 
D

Douglas J. Steele

What is the value of strSQL when the error arises?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


cmarsh said:
I made the change and still have the same problem. It is in the WHERE part
of the query. This is the message I get.

--Syntax error (missing operator) in query expression '[Doc Review
T].Program = 1'

I wonder if it has to do with what is selected needs to be put in " " or
something along those lines? Created a basic query and what is displayed
in
the SQL view is the criteria is in " ".

Douglas J. Steele said:
You have no space between the table name and the keyword WHERE, nor
between
the SQL string and the keyword FROM.

Change

strSQL = strSQL & "FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & "WHERE " & Mid$(strwhere, 6)

to

strSQL = strSQL & " FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & " WHERE " & Mid$(strwhere, 6)

You might also need to change

strfrom = "[Doc Review T] s "

to

strfrom = "[Doc Review T] AS s "



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


cmarsh said:
I cant figure out why this errors out upon Current.DB.QueryDefs line
listed
below. Any help would be appreciated!!

**(This is a form module)
Private Sub Command17_Click()
Dim strSQL As String

If Not Buildsqlstring(strSQL) Then
MsgBox "There was an error!!"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("Reportq").SQL = strSQL
End Sub


Function Buildsqlstring(strSQL As String) As Boolean
Dim strselect As String
Dim strfrom As String
Dim strwhere As String
strselect = "s.* "
strfrom = "[Doc Review T] s "
If chkprogram Then
strwhere = strwhere & " AND s.Program = " & cmbprogram
End If
If chkdocno Then
strwhere = strwhere & " AND s.[Doc No] = " & txtdocno
End If
If chkhwpn Then
strwhere = strwhere & " AND s.[HW PN] = " & txthwpn
End If
If chkqae Then
strwhere = strwhere & " AND s.QAE = " & cmbqae
End If
If chkacc Then
strwhere = strwhere & " AND s.Accepted =" & cmbacc
End If
If chkdate Then
If Not IsNull(txtdatefrom) Then
strwhere = strwhere & " AND s.[Initial Review] >= " & "#"
End If
If Not IsNull(txtdateto) Then
strwhere = strwhere & " AND s.[Initial Review] <= " & "#"
End If
End If
strSQL = "SELECT " & strselect
strSQL = strSQL & "FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & "WHERE " & Mid$(strwhere, 6)
Buildsqlstring = True
End Function
Click to expand...
Click to expand...
Click to expand...
 
C

cmarsh

strSQL : "SELECT [Doc Review T].* FROM [Doc Review T] WHERE [Doc Review
T].Program = 1 Base" : String --(1 Base is a name of a program, which was
selected from cmbprogram on the form)


Douglas J. Steele said:
What is the value of strSQL when the error arises?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


cmarsh said:
I made the change and still have the same problem. It is in the WHERE part
of the query. This is the message I get.

--Syntax error (missing operator) in query expression '[Doc Review
T].Program = 1'

I wonder if it has to do with what is selected needs to be put in " " or
something along those lines? Created a basic query and what is displayed
in
the SQL view is the criteria is in " ".

Douglas J. Steele said:
You have no space between the table name and the keyword WHERE, nor
between
the SQL string and the keyword FROM.

Change

strSQL = strSQL & "FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & "WHERE " & Mid$(strwhere, 6)

to

strSQL = strSQL & " FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & " WHERE " & Mid$(strwhere, 6)

You might also need to change

strfrom = "[Doc Review T] s "

to

strfrom = "[Doc Review T] AS s "



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I cant figure out why this errors out upon Current.DB.QueryDefs line
listed
below. Any help would be appreciated!!

**(This is a form module)
Private Sub Command17_Click()
Dim strSQL As String

If Not Buildsqlstring(strSQL) Then
MsgBox "There was an error!!"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("Reportq").SQL = strSQL
End Sub


Function Buildsqlstring(strSQL As String) As Boolean
Dim strselect As String
Dim strfrom As String
Dim strwhere As String
strselect = "s.* "
strfrom = "[Doc Review T] s "
If chkprogram Then
strwhere = strwhere & " AND s.Program = " & cmbprogram
End If
If chkdocno Then
strwhere = strwhere & " AND s.[Doc No] = " & txtdocno
End If
If chkhwpn Then
strwhere = strwhere & " AND s.[HW PN] = " & txthwpn
End If
If chkqae Then
strwhere = strwhere & " AND s.QAE = " & cmbqae
End If
If chkacc Then
strwhere = strwhere & " AND s.Accepted =" & cmbacc
End If
If chkdate Then
If Not IsNull(txtdatefrom) Then
strwhere = strwhere & " AND s.[Initial Review] >= " & "#"
End If
If Not IsNull(txtdateto) Then
strwhere = strwhere & " AND s.[Initial Review] <= " & "#"
End If
End If
strSQL = "SELECT " & strselect
strSQL = strSQL & "FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & "WHERE " & Mid$(strwhere, 6)
Buildsqlstring = True
End Function
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Since Program is apparently a text field, you need quotes around the value.

Change

strwhere = strwhere & " AND s.Program = " & cmbprogram

to

strwhere = strwhere & " AND s.Program = '" & cmbprogram & "'"

Exagerated for clarity, that's

strwhere = strwhere & " AND s.Program = ' " & cmbprogram & " ' "

Do the same for all other text fields.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


cmarsh said:
strSQL : "SELECT [Doc Review T].* FROM [Doc Review T] WHERE [Doc Review
T].Program = 1 Base" : String --(1 Base is a name of a program, which was
selected from cmbprogram on the form)


Douglas J. Steele said:
What is the value of strSQL when the error arises?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


cmarsh said:
I made the change and still have the same problem. It is in the WHERE
part
of the query. This is the message I get.

--Syntax error (missing operator) in query expression '[Doc Review
T].Program = 1'

I wonder if it has to do with what is selected needs to be put in " "
or
something along those lines? Created a basic query and what is
displayed
in
the SQL view is the criteria is in " ".

:

You have no space between the table name and the keyword WHERE, nor
between
the SQL string and the keyword FROM.

Change

strSQL = strSQL & "FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & "WHERE " & Mid$(strwhere, 6)

to

strSQL = strSQL & " FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & " WHERE " & Mid$(strwhere, 6)

You might also need to change

strfrom = "[Doc Review T] s "

to

strfrom = "[Doc Review T] AS s "



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I cant figure out why this errors out upon Current.DB.QueryDefs line
listed
below. Any help would be appreciated!!

**(This is a form module)
Private Sub Command17_Click()
Dim strSQL As String

If Not Buildsqlstring(strSQL) Then
MsgBox "There was an error!!"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("Reportq").SQL = strSQL
End Sub


Function Buildsqlstring(strSQL As String) As Boolean
Dim strselect As String
Dim strfrom As String
Dim strwhere As String
strselect = "s.* "
strfrom = "[Doc Review T] s "
If chkprogram Then
strwhere = strwhere & " AND s.Program = " & cmbprogram
End If
If chkdocno Then
strwhere = strwhere & " AND s.[Doc No] = " & txtdocno
End If
If chkhwpn Then
strwhere = strwhere & " AND s.[HW PN] = " & txthwpn
End If
If chkqae Then
strwhere = strwhere & " AND s.QAE = " & cmbqae
End If
If chkacc Then
strwhere = strwhere & " AND s.Accepted =" & cmbacc
End If
If chkdate Then
If Not IsNull(txtdatefrom) Then
strwhere = strwhere & " AND s.[Initial Review] >= " & "#"
End If
If Not IsNull(txtdateto) Then
strwhere = strwhere & " AND s.[Initial Review] <= " & "#"
End If
End If
strSQL = "SELECT " & strselect
strSQL = strSQL & "FROM " & strfrom
If strwhere <> "" Then strSQL = strSQL & "WHERE " & Mid$(strwhere,
6)
Buildsqlstring = True
End Function
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

Building a Query by Form Interface 3
Help with VBA (open query) 1
Run Time Error 3464 While Executing An Append Query 3
Dynamic query problem 15
Building A Filter 3
My code seems to hate me.... 2
Creating data via code 12
Error 2465 setting recordsource to SQL 4

Top