Link to home
Start Free TrialLog in
Avatar of perennial
perennial

asked on

Run-time error 3075 - Syntax error (missing operator) in query expression

I am trying to us a function to get my field from a different table, the following is the code that I am using:

Function Ccoment(s As Variant) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset, sCom As String

    Set db = CurrentDb

    Set rs = db.OpenRecordset("SELECT GrowerComment FROM tblGrowerComment " _
                              & " WHERE GCPk=" & s & "")

    If rs.EOF Or rs.BOF Then Exit Function

        rs.MoveFirst
        Do Until rs.EOF

            If Len(rs("GrowerComment") & "") > 0 Then sCom = sCom & rs("GrowerComment") & vbCrLf

            rs.MoveNext

        Loop

    If Len(Trim(sCom)) > 0 Then
        Ccoment = sCom
    Else
        Ccoment = "No Comments"
    End If

rs.Close
db.Close
Set db = Nothing
End Function

In my sql...this is how I use it:

MyComment: Ccoment([GCPk])

When I try to run the SQL it give me the run-time error 3075.

When clicking on debug the following line of code is highlight in yellow:

    Set rs = db.OpenRecordset("SELECT GrowerComment FROM tblGrowerComment " _
                              & " WHERE GCPk=" & s & "")

Any Ideas???

perennial


Avatar of Eric Sherman
Eric Sherman
Flag of United States of America image

Try this ...

   Set rs = db.OpenRecordset("SELECT GrowerComment FROM tblGrowerComment " _
                              & " WHERE GCPk='" & s & "'")


ET
Avatar of perennial
perennial

ASKER

etsherman;

Got another error:

Run-time error 3464:

Data type mismatch in criteria expression.

??
What is the field type for GCPK???

ET
try it without breaking the line

 Set rs = db.OpenRecordset("SELECT GrowerComment FROM tblGrowerComment WHERE GCPk=" & s & "")
GCPK is the primary key a text field.
SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
capricorn1,

Try without breaking the line and without the single quote, this give me the same error (run-time error 3075).

Same thing without breaking the line but with a single quote, this give me the error (Run-time error 3464).


SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you very much for your help...
My apology...it was my fault. This morning after carefully looking at the sql again, the field in the SQL should be 'PSDPk' and not 'GCPk' per rockiroads comment.

I suppose after looking at the screen all day my brain begin to accept what I type in the sql ;-(

perennial