Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-05-24
10
Medium Priority
?
2,762 Views
Last Modified: 2012-06-27
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


0
Comment
Question by:perennial
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 16755069
Try this ...

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


ET
0
 
LVL 1

Author Comment

by:perennial
ID: 16755105
etsherman;

Got another error:

Run-time error 3464:

Data type mismatch in criteria expression.

??
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 16755160
What is the field type for GCPK???

ET
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16755241
try it without breaking the line

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

Author Comment

by:perennial
ID: 16755263
GCPK is the primary key a text field.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 664 total points
ID: 16755295

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

Author Comment

by:perennial
ID: 16755314
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).


0
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 664 total points
ID: 16755516
In the function change the variable from Variant to String ...

Function Ccoment(s As String) As String

Then ....

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


ET



0
 
LVL 65

Accepted Solution

by:
rockiroads earned 672 total points
ID: 16755875
If u ran the modified code

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


and you say u got a type mismatch, can you double check what GCPk is?
if it is indeed a textfield like u say, then the above code should work without a data type mismatch error


If u still getting problems, try this

Dim sSql as String

sSql = "SELECT GrowerComment FROM tblGrowerComment WHERE GCPk = '" & s & "'"

or if number

sSql = "SELECT GrowerComment FROM tblGrowerComment WHERE GCPk = " & s

then do this

debug.print sSql

One other thing, does the variable s definitely have a value? You will soon see when u dump the sql

then look in immediate window, u wil see some sql, copy/paste that into a new query and see what happens

0
 
LVL 1

Author Comment

by:perennial
ID: 16760119
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question