[RESOLVED] VB6 - RunTime error 3075: Syntax error - Missing operator in query expression-VBForums
Results 1 to 25 of 25

Thread: [RESOLVED] VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Resolved [RESOLVED] VB6 - RunTime error 3075: Syntax error - Missing operator in query expression



    Gentlemen,
    they say more eyes are better than two . Can anyone see if they can see where my syntax error is in the code below:
    Code:
    Set rsin = Dbs.OpenRecordset("SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.Original_Check_Date, tblUnclaimed.Amount_of_Check, EmployeeInfo.Status " _
    & "FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) " _
    & "WHERE (((EmployeeInfo.Status) Like 'N*') AND ((EmployeeInfo.Union_Code) In (Unioncode) AND ((tblUnclaimed.Status) In ('R','P','N'))) OR (((EmployeeInfo.Union_Code) In (Unioncode) AND ((tblUnclaimed.Status) In ('R','P','N')) AND ((Len([EmployeeInfo].[Status]))=1)) " _
    & "AND tblUnclaimed.ReIssued_Check_Date Between #Begindate# And #Enddate# " _
    & "ORDER BY EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.PassNumber, tblUnclaimed.Original_Check_Date;")
    Any quick response will be appreciated.
    Giftx.

  2. #2
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,135

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Try doing something like this:
    Code:
    Dim strSQL As String
    
        strSQL = "SELECT DISTINCT" & vbNewLine
        strSQL = strSQL & "    tblUnclaimed.PassNumber," & vbNewLine
        strSQL = strSQL & "    tblUnclaimed.EmployeeName," & vbNewLine
        strSQL = strSQL & "    EmployeeInfo.Address," & vbNewLine
        strSQL = strSQL & "    EmployeeInfo.City," & vbNewLine
        strSQL = strSQL & "    EmployeeInfo.State," & vbNewLine
        strSQL = strSQL & "    EmployeeInfo.ZIP," & vbNewLine
        strSQL = strSQL & "    EmployeeInfo.L3," & vbNewLine
        strSQL = strSQL & "    EmployeeInfo.L5," & vbNewLine
        strSQL = strSQL & "    tblUnclaimed.Original_Check_Date," & vbNewLine
        strSQL = strSQL & "    tblUnclaimed.Amount_of_Check," & vbNewLine
        strSQL = strSQL & "    EmployeeInfo.Status" & vbNewLine
        strSQL = strSQL & "From" & vbNewLine
        strSQL = strSQL & "    EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1 AND" & vbNewLine
        strSQL = strSQL & "                                             EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber)" & vbNewLine
        strSQL = strSQL & "Where" & vbNewLine
        strSQL = strSQL & "    EmployeeInfo.Status) Like 'N*'" & vbNewLine
        strSQL = strSQL & "AND EmployeeInfo.Union_Code In (" & Unioncode & ")" & vbNewLine
        strSQL = strSQL & "AND (tblUnclaimed.Status In ('R','P','N') OR EmployeeInfo.Union_Code In (" & Unioncode & "))" & vbNewLine
        strSQL = strSQL & "AND tblUnclaimed.Status) In ('R','P','N')" & vbNewLine
        strSQL = strSQL & "AND Len(EmployeeInfo.Status)=1" & vbNewLine
        strSQL = strSQL & "AND tblUnclaimed.ReIssued_Check_Date Between #" & Begindate & "# And #" & Enddate & "#" & vbNewLine
        strSQL = strSQL & "ORDER BY" & vbNewLine
        strSQL = strSQL & "    EmployeeInfo.L3," & vbNewLine
        strSQL = strSQL & "    EmployeeInfo.L5," & vbNewLine
        strSQL = strSQL & "    tblUnclaimed.PassNumber," & vbNewLine
        strSQL = strSQL & "    tblUnclaimed.Original_Check_Date;"
        
        Debug.Print strSQL
        Set rsin = Dbs.OpenRecordset(strSQL)
    Place break pointer directly on the last line, check if sql looks good before trying to populate recordset - you may even execute it directly in your database.
    This way is easy to spot what part of sql has the error.

    But to answer your question - I think your problem was 3 variables (Unioncode, Begindate, Enddate) that are embeded in the sql instead of dynamically concatenated.
    There are also way too many parenthesis [I think].

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Hello Rhino,
    thanks for your input. Your query executed okay, but when I added a new variable (CheckStatus) to the query, I got the same error. I must confess, I am not really good with placing single/double quotes ("" or '').
    Please see if the quotes around the new variables are incorrect.
    Code:
    strSQL = "SELECT DISTINCT" & vbNewLine
        strSQL = strSQL & "tblUnclaimed.PassNumber," & vbNewLine
        strSQL = strSQL & "tblUnclaimed.EmployeeName," & vbNewLine
        strSQL = strSQL & "EmployeeInfo.Address," & vbNewLine
        strSQL = strSQL & "EmployeeInfo.City," & vbNewLine
        strSQL = strSQL & "EmployeeInfo.State," & vbNewLine
        strSQL = strSQL & "EmployeeInfo.ZIP," & vbNewLine
        strSQL = strSQL & "EmployeeInfo.L3," & vbNewLine
        strSQL = strSQL & "EmployeeInfo.L5," & vbNewLine
        strSQL = strSQL & "tblUnclaimed.ReIssued_Check_Date," & vbNewLine
        strSQL = strSQL & "tblUnclaimed.ReIssued_Check_Amount," & vbNewLine
        strSQL = strSQL & "EmployeeInfo.Status" & vbNewLine
        strSQL = strSQL & "From" & vbNewLine
        strSQL = strSQL & "EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1 AND" & vbNewLine
        strSQL = strSQL & "EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber)" & vbNewLine
        strSQL = strSQL & "Where" & vbNewLine
        strSQL = strSQL & "EmployeeInfo.Status Like 'N*'" & vbNewLine
        strSQL = strSQL & "AND EmployeeInfo.Union_Code In (" & Unioncode & ")" & vbNewLine
        strSQL = strSQL & "AND (tblUnclaimed.Status In (" & CheckStatus & ") OR EmployeeInfo.Union_Code In (" & Unioncode & "))" & vbNewLine
        strSQL = strSQL & "AND tblUnclaimed.Status In (" & CheckStatus & ")" & vbNewLine
        strSQL = strSQL & "AND Len(EmployeeInfo.Status)=1" & vbNewLine
        strSQL = strSQL & "AND tblUnclaimed.ReIssued_Check_Date Between #" & Begindate & "# And #" & Enddate & "#" & vbNewLine
        strSQL = strSQL & "ORDER BY" & vbNewLine
        strSQL = strSQL & "tblUnclaimed.PassNumber;"
        
        'Debug.Print strSQL
        
        Set rsin = Dbs.OpenRecordset(strSQL)
    Thanks.
    Giftx.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Mr. RBull,
    To be more specific, it is not picking up the new variable I added to the query
    Code:
    CheckStatus
    It is saying Missing operator in query expression:
    Code:
    tblUnclaimed.status In ()

  5. #5
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    10,829

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Perhaps a missing space?

    Code:
    strSQL = strSQL & "ORDER BY " & vbNewLine
    strSQL = strSQL & "tblUnclaimed.PassNumber;"
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,230

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    That's not a problem - the NewLine is whitespace, so is a perfectly valid separator (and IMO is better - as it makes the SQL easier to read if you print to debug, etc).

    To be more specific, it is not picking up the new variable I added to the query
    Have you checked what that variable contains when this part of the code runs?

    For it to be empty in the SQL statement, the variable itself must evaluate to an empty string.

  7. #7
    Member
    Join Date
    Sep 2008
    Posts
    46

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Always remove parens from SQL code when using it in VB. It makes life so much easier.

    Those parens around your variables are uneeded. That is most likely the error.

    Something like this, I did this while on the phone so forgive...you may need the parens around the Len statements through, try it both ways...you may need em around the R,P,N piece as well...you get the point, remove all of the uneeded ones that T-SQL adds.


    Code:
    Set rsin = Dbs.OpenRecordset("SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.Original_Check_Date, tblUnclaimed.Amount_of_Check, EmployeeInfo.Status " _
    & "FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON EmployeeInfo.L1 = tblUnclaimed.L1 AND EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber " _
    & "WHERE EmployeeInfo.Status Like 'N*' AND EmployeeInfo.Union_Code In Unioncode AND tblUnclaimed.Status In 'R','P','N' OR EmployeeInfo.Union_Code In Unioncode AND tblUnclaimed.Status In 'R','P','N' AND Len([EmployeeInfo].[Status])=1 " _
    & "AND tblUnclaimed.ReIssued_Check_Date Between #Begindate# And #Enddate# " _
    & "ORDER BY EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.PassNumber, tblUnclaimed.Original_Check_Date")
    Last edited by Beall49; Oct 21st, 2008 at 05:55 PM.

  8. #8
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    10,829

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Providing us the value of CheckStatus should be helpful. Likewise, posting the result of Debug.Print should be of help...

    Quote Originally Posted by si_the_geek
    That's not a problem - the NewLine is whitespace, so is a perfectly valid separator (and IMO is better - as it makes the SQL easier to read if you print to debug, etc).
    Oooppsss... Sorry, didn't read the code carefully and never used vbNewLine when formatting my sqls...
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,230

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Quote Originally Posted by Beall49
    Those parens around your variables are uneeded. That is most likely the error.
    They are needed, they are part of the syntax for IN. I'm not sure if they are needed in the Join clauses, but I always use them as it makes them easier to read.

  10. #10
    Lively Member
    Join Date
    Jun 2006
    Posts
    79

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    What datatypes are:

    EmployeeInfo.Union_Code
    tblUnclaimed.Status
    EmployeeInfo.Status

    ???

    If they are string, then the comparatives MUST be enclosed within single quotes.

    If SQL is expecting quoted strings, and you don't have quotes around them, you WILL get that error message you got.

  11. #11
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,230

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    That would cause a Type Mismatch error, or something with a similar meaning, so is not the issue at hand (but it may be an issue later!).

    The last we heard from the OP was back in post #4 - and the error there was due to the value of CheckStatus not being in the SQL statement.

    I suspect that is because of a scope issue, which is not being noticed due to a lack of Option Explicit, but until we hear more we can only make educated guesses.

  12. #12
    Lively Member
    Join Date
    Jun 2006
    Posts
    79

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Si,
    I just ran a query against a MSAccess database using DAO3.6. The SQL statement has missing single quotes surrounding a comparative.

    The error message is "Run time error -2147217904 (80040e10)"
    "No value given for one or more parameters"

    Which is obviously not the error message he quoted.

    I stand corrected.

    But notice, the error message is not really meaningful either.
    I notice this more with MSAccess rather than SQLServer.

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Hi Si,
    Below is my complete module. If you look at my variable definitions, Unioncode, Begindate, enddate, the sql is picking up all other variables except "Checkstatus".

    Code:
    bPath = "\\Livctrls03-08\Data1\APP\UNCLAIMW\UCCTest\Databases\"
    DbName = "Unclaimed Checks.mdb"
    
    'Open Database/Recordset:
    '------------------------
    Set Dbs = OpenDatabase(DbPath & DbName)
    
    'Prompt User to Enter required Values: Union codes and Dates:
    '------------------------------------------------------------
    Unioncode = vbEmptyString
    Begindate = vbEmptyString
    Enddate = vbEmptyString
    CheckStatus = vbEmptyString
    
    Unioncode = InputBox("Please Enter Union Codes for this report. Enter each value enclosed in single quotes. if you have more than one union code, separate them with commas (,):Example: '01','00'")
    If IsNull(Unioncode) Or Unioncode = "" Then
       MsgBox "Please Enter valid union code(s), enclosed with Single quotes: ' ' " _
       & "If entering more than one union code, separated by commas (,)"
       Screen.MousePointer = vbNormal
       Exit Sub
    End If
       
    Begindate = InputBox("Please Enter the Unclaimed Check ReIssue Begining date range with format mm/dd/yyyy:")
    If Not IsDate(Begindate) Or IsNull(Begindate) Then
       MsgBox "Please Enter valid date with format mm/dd/yyyy", vbInformation, "Unclaimed Checks System"
       Screen.MousePointer = vbNormal
       Exit Sub
    End If
    
    Enddate = InputBox("Please Enter the Unclaimed Check ReIssue Ending date range with format mm/dd/yyyy:")
    If Not IsDate(Enddate) Or IsNull(Enddate) Then
       MsgBox "Please Enter valid date with format mm/dd/yyyy", vbInformation, "Unclaimed Checks System"
       Screen.MousePointer = vbNormal
       Exit Sub
    End If
    
    CheckStat = InputBox("Please Enter the Unclaimed Check Status enclosed in single quotes ('')- R=ResIssued,P=Paid thru PRAP, N=Not Entitled,U=Unclaimed:") _
                        & " If entering multiple check status,enclose each in single quotes, separated by commas"
    If IsNull(CheckStat) Or CheckStat = "" Then
       MsgBox "Please Enter Check Status, R,N,P,U,", vbInformation, "Unclaimed Checks System"
       Screen.MousePointer = vbNormal
       Exit Sub
    End If
    
    
    strSQL = "SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, "
    strSQL = strSQL & "EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, "
    strSQL = strSQL & "EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, "
    strSQL = strSQL & "tblUnclaimed.Original_Check_Date, "
    strSQL = strSQL & "tblUnclaimed.Amount_of_Check, EmployeeInfo.Status "
    strSQL = strSQL & "FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) "
    strSQL = strSQL & "AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) "
    strSQL = strSQL & "WHERE (((EmployeeInfo.Status) Like 'N*') "
    strSQL = strSQL & "AND ((EmployeeInfo.Union_Code) In (Unioncode) "
    strSQL = strSQL & "AND ((tblUnclaimed.Status) In (CheckStatus))) "
    strSQL = strSQL & "AND ((Len([EmployeeInfo].[Status]))=1)) "
    strSQL = strSQL & "AND tblUnclaimed.ReIssued_Check_Date Between #" & Begindate & "# And #" & Enddate & "# "
    strSQL = strSQL & "ORDER BY tblUnclaimed.PassNumber;"
        
    Set rsin = Dbs.OpenRecordset(strSQL)
    Thanks.

  14. #14
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,230

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Quote Originally Posted by Antithesus
    But notice, the error message is not really meaningful either.
    I notice this more with MSAccess rather than SQLServer.
    That message is actually meaningful, just not worded particularly clearly... As the text is not in quotes it is assumed to be the name of a field or other object, but as there is no field etc with that name, it is apparently assumed to be a parameter - and as no kind of value has been given for it (not even Null), the error is shown.

    I think it would be better if there was an alternative word instead of "parameter", and the item it doesn't recognise was shown in the message... but it is how it is!
    Last edited by si_the_geek; Oct 23rd, 2008 at 10:54 AM. Reason: bad timing of post - added quote for clarity

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Si,
    so how do I properly enclose "CheckStatus" is quotes within the sql?
    Thanks.
    Giftx.

  16. #16
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,230

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Quote Originally Posted by Giftx
    Hi Si,
    Below is my complete module. If you look at my variable definitions, Unioncode, Begindate, enddate, the sql is picking up all other variables except "Checkstatus".
    That is not your complete module, or even the complete sub/function... among other things it does not contain the variable declarations.

    One problem there is that you have not changed this part back to how it was (as I said to do in the other thread):
    Code:
    strSQL = strSQL & "AND ((tblUnclaimed.Status) In (CheckStatus))) "
    It needs to be like this:
    Code:
    strSQL = strSQL & "AND ((tblUnclaimed.Status) In (" & CheckStatus & "))) "
    Presumably you were getting the error that Antithesus mentioned, and making this change will get rid of that error.


    As to why the value is not being put into the SQL statement, it actually is... the problem is that the value will always be an empty string, because that is the only value you ever put in CheckStatus.

    If you don't have it already, add Option Explicit (and always use it), as it should point out the mistake to you.

    You would also be able to spot the problem by debugging, which is a very useful skill to have - most programmers consider it to be mandatory.

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Si,
    I noticed something when I did
    Code:
    Debug.Print strSQL
    The Debug.Print output picked up user-supplied input: for Begindate and enddate but did not pick up user-supplied input for Unioncode and CheckStatus.

    Below is the output of the "Debug.Print strSQL"

    Code:
    SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.Original_Check_Date, tblUnclaimed.Amount_of_Check, EmployeeInfo.Status FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) WHERE (((EmployeeInfo.Status) Like 'N*') AND ((EmployeeInfo.Union_Code) In (Unioncode) AND ((tblUnclaimed.Status) In (CheckStatus))) AND ((Len([EmployeeInfo].[Status]))=1)) AND tblUnclaimed.ReIssued_Check_Date Between #03/1/2008# And #03/31/2008# ORDER BY tblUnclaimed.PassNumber;
    Giftx.

  18. #18
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,230

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    I hadn't checked Unioncode before, but as the SQL statement contains the name of the variable rather than a value (ie: "... In (Unioncode) .."), the problem is the same thing I showed how to correct in the code snippets of my previous post.

    Unioncode should be OK then, it does not have the other problem that CheckStatus has.

  19. #19

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Si,
    So, how do I fix it? I changed this to add quotes to Checkstatus as you specified and I had the same error. This time it is not even picking up the variable name, rather Checkstatus has empty parenthesis ().
    See Debug.Print output below:

    Code:

    strSQL = strSQL & "AND ((tblUnclaimed.Status) In (" & CheckStatus & "))) "

    Debug.Print Output:
    Code:
    SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.Original_Check_Date, tblUnclaimed.Amount_of_Check, EmployeeInfo.Status FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) WHERE (((EmployeeInfo.Status) Like 'N*') AND ((EmployeeInfo.Union_Code) In (Unioncode) AND ((tblUnclaimed.Status) In ())) AND ((Len([EmployeeInfo].[Status]))=1)) AND tblUnclaimed.ReIssued_Check_Date Between #03/1/2008# And #03/31/2008# ORDER BY tblUnclaimed.PassNumber;
    Giftx.

  20. #20
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,230

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    That is half of what I said back in post #16, and as mentioned in my previous post you need to do the same correction for Unioncode - when you have done that, the error "No value given for one or more parameters" will be gone (as you will have fixed both things that were causing it).

    Re-read the second half of post #16 (from "As to why" onwards), as it explains why it seems like the value of CheckStatus is not being used.

  21. #21

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Si,
    I made the changes to both variables as you said how I have: Syntax error in String in query expression.

    Note that I am supplying the values thru an inputbox, but the Debug.Print is not showing any of the values I entered for Unioncode and CheckStatus

    Below are the changes I made:
    Code:
    strSQL = strSQL & "AND ((EmployeeInfo.Union_Code) In (" & Unioncode & ") "
    strSQL = strSQL & "AND ((tblUnclaimed.Status) In (" & CheckStatus & "))) "
    This is the Debug.Print output:
    Code:
    SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.Original_Check_Date, tblUnclaimed.Amount_of_Check, EmployeeInfo.Status FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) WHERE (((EmployeeInfo.Status) Like 'N*') AND ((EmployeeInfo.Union_Code) In (Unioncode) AND ((tblUnclaimed.Status) In ())) AND ((Len([EmployeeInfo].[Status]))=1)) AND tblUnclaimed.ReIssued_Check_Date Between #03/1/2008# And #03/31/2008# ORDER BY tblUnclaimed.PassNumber;

  22. #22
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,230

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    CheckStatus is blank because you have not put any value into the variable (other than an empty string), see post #16.

    You seem to have copied the wrong Debug.Print output, unless what the Unioncode variable contains is actually "Unioncode".

    The code you showed in that post is correct, so the values of Unioncode and CheckStatus should be in strSQL - the problem is that the value of CheckStatus is an empty string.

  23. #23

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Si,
    please take a look at my inputBox syntax. Maybe my problem is here. Something looks funky within the codes.

    Code:
    Unioncode = InputBox("Please Enter Union Codes for this report. Enter each value enclosed in single quotes. if you have more than one union code, separate them with commas (,):Example: '01','00'")
    
    If IsNull(Unioncode) Or Unioncode = "" Then
       MsgBox "Please Enter valid union code(s), enclosed with Single quotes: ' ' " _
       & "If entering more than one union code, separate them with commas (,)"
       Screen.MousePointer = vbNormal
       Exit Sub
    End If
       
    CheckStatus = InputBox("Please Enter the Unclaimed Check Status enclosed in single quotes ('') - R=ResIssued,P=Paid thru PRAP, N=Not Entitled,U=Unclaimed:") _
    & " If entering multiple check status,enclose each value in single quotes, separated by commas"
    
    If IsNull(CheckStatus) Or CheckStatus = "" Then
       MsgBox "Please Enter Check Status, R,N,P,U,", vbInformation, "Unclaimed Checks System"
       Screen.MousePointer = vbNormal
       Exit Sub
    End If

  24. #24
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,230

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    You've sorted out the name of the CheckStatus variable, nice one.

    You have a minor issue on the second InputBox, as you have appended text to the result, rather than appending it to the message - you need to move the ) to the very end (after commas" ).


    Note that there is no need to use the IsNull function in your If statements, as a String can never be Null (so that part will always return False). This line:
    Code:
    If IsNull(Unioncode) Or Unioncode = "" Then
    should simply be this:
    Code:
    If Unioncode = "" Then

  25. #25
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    10,829

    Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression

    Quote Originally Posted by si_the_geek
    Code:
    If Unioncode = "" Then
    To optimize it...

    Code:
    If Len(Trim$(Unioncode)) = 0 Then
    Trim$ would remove the possibility of white space and using LEN is slightly faster than using equal comparison...
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.