Syntax error (missing operator)

  • Thread starter Thread starter PHisaw
  • Start date Start date
P

PHisaw

Hi,

I used the following code supplied by Rick Brandt to a message from a 2005
post "Open Forms in Hidden Mode" where user was using one form to open
another form to extract data.

Dim db as Database
Dim rs as Recordset
Dim strSQL as String
strSQL = "SELECT FieldName " & _
"FROM TableName " & _
"WHERE SomeField = SomeValue"
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.EOF = True Then
'No matching records found (do whatever)
Else
'In here values can be extracted by...
SomeVariable = rs!FieldName
End If
rs.Close
Set rs = Nothing
Set db = Nothing

Rick Brandt suggested not using the hidden form but search from table. I'm
trying to do this and keep getting an error. I hope Rick or someone will
help me figure out what I'm doing wrong.

Here's my code below that errors on this line

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

with Run-timer error 3075 - Syntax error (missing operator) in query
expression "tech "FROM tWorkLog WHERE tworklog.tech = "John Smith" and
tworklog.StopTime is Null'.


Dim db As Database
Dim rs As Recordset
Dim strSQL As String
strSQL = "SELECT tech'" & _
"FROM tWorkLog " & _
"WHERE tworklog.tech ='" & Forms!fttswitchboard!CboTech & "'" & _
"and tworklog.StopTime = Null"
Set db = CurrentDb
Debug.Print strSQL

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.EOF = True Then
DoCmd.OpenForm "fGeninfoTT", , , "jobnumber=" & Me.JobNumber 'No matching
records found (do whatever)
Else
DoCmd.OpenForm "fttWorkLogReminder", , , "tech='" &
Forms!fttswitchboard!CboTech & "'" 'In here values can be extracted by...
'SomeVariable = rs!FieldName
End If
rs.Close
Set rs = Nothing
Set db = Nothing

I've changed the code several different ways, have searched related posts,
changed quotes per related posts and still can't get anything to work. I
would really appreciate any help.

Thanks in advance,
Pam
 
PHisaw said:
Hi,

I used the following code supplied by Rick Brandt to a message from a 2005
post "Open Forms in Hidden Mode" where user was using one form to open
another form to extract data.

Dim db as Database
Dim rs as Recordset
Dim strSQL as String
strSQL = "SELECT FieldName " & _
"FROM TableName " & _
"WHERE SomeField = SomeValue"
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.EOF = True Then
'No matching records found (do whatever)
Else
'In here values can be extracted by...
SomeVariable = rs!FieldName
End If
rs.Close
Set rs = Nothing
Set db = Nothing

Rick Brandt suggested not using the hidden form but search from table.
I'm
trying to do this and keep getting an error. I hope Rick or someone will
help me figure out what I'm doing wrong.

Here's my code below that errors on this line

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

with Run-timer error 3075 - Syntax error (missing operator) in query
expression "tech "FROM tWorkLog WHERE tworklog.tech = "John Smith" and
tworklog.StopTime is Null'.


Dim db As Database
Dim rs As Recordset
Dim strSQL As String
strSQL = "SELECT tech'" & _
"FROM tWorkLog " & _
"WHERE tworklog.tech ='" & Forms!fttswitchboard!CboTech & "'" & _
"and tworklog.StopTime = Null"
Set db = CurrentDb
Debug.Print strSQL

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.EOF = True Then
DoCmd.OpenForm "fGeninfoTT", , , "jobnumber=" & Me.JobNumber 'No matching
records found (do whatever)
Else
DoCmd.OpenForm "fttWorkLogReminder", , , "tech='" &
Forms!fttswitchboard!CboTech & "'" 'In here values can be extracted by...
'SomeVariable = rs!FieldName
End If
rs.Close
Set rs = Nothing
Set db = Nothing

I've changed the code several different ways, have searched related posts,
changed quotes per related posts and still can't get anything to work. I
would really appreciate any help.
Click to expand...


You appeat to be building your SQL string incorrectly. Try this:

strSQL = "SELECT tech " & _
"FROM tWorkLog " & _
"WHERE tworklog.tech ='" & Forms!fttswitchboard!CboTech & "'" & _
" and tworklog.StopTime Is Null"

If CboTech might contain a single-quote character ('), try this instead:

strSQL = "SELECT tech " & _
"FROM tWorkLog " & _
"WHERE tworklog.tech =" & _
Chr(34) & Forms!fttswitchboard!CboTech & Chr(34) & _
" and tworklog.StopTime Is Null"

If CboTech might contain both single-quote characters and double-quote
characters ("), try this:

Const Q As String = """"
Const QQ = Q & Q

strSQL = "SELECT tech " & _
"FROM tWorkLog " & _
"WHERE tworklog.tech =" & _
Q & Replace(Forms!fttswitchboard!CboTech, Q, QQ) & Q & _
" and tworklog.StopTime Is Null"

That last is the most universally valid form.
 
Dirk,

Thank you for the quick reply. I originally had the SQL string written the
first way you indicated - with tech first and then stop time and still
received missing operator error. I thought maybe swapping where clauses may
help, but didn't. Anyway, I copied code you supplied and each time I get
error 13 - type mismatch.

Do you have other suggestions I can try? This is getting frustrating and I
didn't think it would be this difficult to trap open time on a job. I can't
make it work with a hidden form or searching for records directly from the
table.

Pam
 
PHisaw said:
Dirk,

Thank you for the quick reply. I originally had the SQL string written
the
first way you indicated - with tech first and then stop time and still
received missing operator error. I thought maybe swapping where clauses
may
help, but didn't. Anyway, I copied code you supplied and each time I get
error 13 - type mismatch.
Click to expand...

That is a completely different error from the one you reported in your post.
I take it you got the Type Mismatch error, and then tried various
modifications to your SQL statement in an attempt to get past that,
resulting in the syntax error.

It's too bad you didn't post first about the Type Mismatch error, because
I'm pretty sure I know where that is coming from. If I'm right, all you
have to do -- aside from correcting the syntax errors you introduced -- is
change these lines:
Dim db as Database
Dim rs as Recordset
Click to expand...

.... to these:

Dim db as DAO.Database
Dim rs as DAO.Recordset

Alternatively, you could open the Tools -> References... dialog and remove
the reference to "Microsoft ActiveX Data Objects 2.x Library". The problem
here is that both that library (known as ADO for short) and the "Microsoft
DAO 3.6 Object Library" (DAO for short) define a Recordset object (among
others), but those two object types aren't compatible. If you have both
references set, and you declare an unqualified Recordset object, Access is
liable to make an incorrect assumption about which type of Recordset you are
declaring. So it interprets your declaration "Dim rs as Recordset" as an
ADO Recordset, but db.OpenRecordset returns a *DAO* Recordset; so bang!
Type Mismatch.
 
Dirk,

I'm sorry - I may have worded my reply wrong, but I had never received the
Type Mismatch error only the syntax missing operator error when I wrote my
original post. When I said I tried rearranging the where clause, I was
thinking if I put the "null" portion first it might make a difference to the
syntax error. The type mismatch error came when I used either of the three
suggestions you made, but your suggestion for DAO. seems to have corrected
it. Once I changed the order of the forms to open, all appears to be working
well.

Again, thanks so much for your help. I've spent a great deal of time trying
to work thru this.

Pam
 
Back
Top