vb6 - Rtime Error 3075: Syntax error - Missing Operator in query expression


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 13 of 13

Thread: vb6 - Rtime Error 3075: Syntax error - Missing Operator in query expression

Hybrid View

  1. #1
    Join Date
    Aug 2007
    Location
    NYC
    Posts
    60

    vb6 - Rtime 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
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    When I break your query up into something a bit more easily readible, I find that the end parenthese on the ORDER BY clause throws me an error.
    Code:
    Dim sSQL As String
    sSQL = "SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, "
    sSQL = sSQL & "EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, "
    sSQL = sSQL & "EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, "
    sSQL = sSQL & "tblUnclaimed.Original_Check_Date, "
    sSQL = sSQL & "tblUnclaimed.Amount_of_Check, EmployeeInfo.Status "
    sSQL = sSQL & "FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = blUnclaimed.L1) "
    sSQL = sSQL & "AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) "
    sSQL = sSQL & "WHERE (((EmployeeInfo.Status) Like 'N*') "
    sSQL = sSQL & "AND ((EmployeeInfo.Union_Code) In (Unioncode) "
    sSQL = sSQL & "AND ((tblUnclaimed.Status) In ('R','P','N'))) "
    sSQL = sSQL & "OR (((EmployeeInfo.Union_Code) In (Unioncode) "
    sSQL = sSQL & "AND ((tblUnclaimed.Status) In ('R','P','N')) "
    sSQL = sSQL & "AND ((Len([EmployeeInfo].[Status]))=1)) "
    sSQL = sSQL & "AND tblUnclaimed.ReIssued_Check_Date Between #Begindat# And #Enddate# "
    sSQL = sSQL & "ORDER BY EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.PassNumber, tblUnclaimed.Original_Check_Date;"
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Aug 2007
    Location
    NYC
    Posts
    60
    Good morning Hack.
    Thanks for the sql cleanup. I ran the sql and got:
    syntax error in date in query expression on
    Code:
    strSQL = strSQL & "AND tblUnclaimed.ReIssued_Check_Date 
    Between # Begindate# And #Enddate#
    Then I changed the date strings to be dynamic as so:

    Code:
    strSQL = strSQL & "AND tblUnclaimed.ReIssued_Check_Date 
    Between #" & Begindate & "# And #" & Enddate & "# "
    Then I got another error: 3061 Too few parameters. Expected 2.

    could you check for my syntax error? I'm not too good with placing quotes.
    Thanks.
    Giftx.
    Last edited by Hack; 10-22-2008 at 10:25 AM.

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Add "Debug.Print strSQL" after the last line and before you execute it.

    What is goes into your Immediate Window?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  5. #5
    Join Date
    Aug 2007
    Location
    NYC
    Posts
    60
    Hack,
    I noticed something when I did
    Code:
    Debug.Print strSQL
    It picked user 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"

    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.

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by GiftX
    It picked user user-supplied input: for Begindate and enddate but did not pick up user-supplied input for Unioncode and CheckStatus.
    I knew it had to be something like that, and, therein, lies your snytax error.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  7. #7
    Join Date
    Aug 2007
    Location
    NYC
    Posts
    60
    So, how do I fix it? I changed this to add quotes to Checkstatus and I had the same error. This time it is not even picking up the variable name, rather it has empty parenthesis ().

    Giftx.

  8. #8
    Join Date
    Aug 2007
    Location
    NYC
    Posts
    60
    So, how do I fix it? I changed this to add quotes to Checkstatus and I had the same error. This time it is not even picking up the variable name, rather it has empty parenthesis ().
    Code:
    strSQL = strSQL & "AND ((tblUnclaimed.Status) In (" & CheckStatus & "))) "
    Giftx.

  9. #9
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    What is CheckStatus supposed to be?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  10. #10
    Join Date
    Aug 2007
    Location
    NYC
    Posts
    60
    Resolved!!!

    Thanks To All You Guys For Your Help.

    Giftx.

  11. #11
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    How did you resolve it?

    Your solution might help others.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  12. #12
    Join Date
    Aug 2007
    Location
    NYC
    Posts
    60
    Techinically it was not resolved.
    Some of the good people suggested taking a more user-friendly approach to avoid users making mistakes by entering invalid data without proper string formating.

    So I went with using controls so that they can simply select values from listbox and comboboxes.

  13. #13
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    If it isn't resolved, then what are the lingering questions?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. Please help me with the Syntax for this Sql Query.
    By yasinirshad in forum Database
    Replies: 0
    Last Post: 08-29-2007, 04:54 PM
  2. Run Access Query with VBA function in VB6?
    By Ron Weller in forum VB Classic
    Replies: 5
    Last Post: 10-12-2006, 06:23 PM
  3. query tuning
    By dhaya in forum Database
    Replies: 11
    Last Post: 08-25-2003, 05:24 PM
  4. VB6 vs VB.NET...Procedure Calling Syntax
    By Patrick Troughton in forum .NET
    Replies: 78
    Last Post: 08-13-2002, 11:18 AM
  5. Replies: 16
    Last Post: 08-12-2002, 10:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center