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
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
ASKER
etsherman;
Got another error:
Run-time error 3464:
Data type mismatch in criteria expression.
??
Got another error:
Run-time error 3464:
Data type mismatch in criteria expression.
??
What is the field type for GCPK???
ET
ET
try it without breaking the line
Set rs = db.OpenRecordset("SELECT GrowerComment FROM tblGrowerComment WHERE GCPk=" & s & "")
Set rs = db.OpenRecordset("SELECT GrowerComment FROM tblGrowerComment WHERE GCPk=" & s & "")
ASKER
GCPK is the primary key a text field.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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).
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Set rs = db.OpenRecordset("SELECT GrowerComment FROM tblGrowerComment " _
& " WHERE GCPk='" & s & "'")
ET