Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL syntax (2000)

    Sorry not certain if this post should be in VBA. No less I have a function to build a string to hold an sql statement. I keep getting a syntax error the minute I implement a join, I have tried all sorts, copied and pasted from the qbe still no luck. The SQL is passed to a function that maniuplate a querydef. Currently the error is saying eror in from clause;

    If ckTM Then
    sSELECT = sSELECT & ",Teams.[Team Manager] "
    sFROM = sFROM & " INNER JOIN Individuals INNER JOIN tblmain ON Individuals.[Individual ID] = tblmain.Customer ON Teams.[Team ID] = Individuals.Team;"

    sWHERE = " and Teams.[Team Manager]= " & cmbtm

    Can anybody please help, i am at the point of despair, thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: SQL syntax (2000)

    You haven't posted only a fragment of your code, so we have no way of understanding how it fits together, but one thing that sticks out is that you shouldn't have a semicolon at the end of sFROM. The semicolon signifies the end of the SQL string, but you want to add a WHERE clause, so the FROM clause can't the end.

    And shouldn't the line with sWHERE be sWHERE = sWHERE & " and ..."? Not sure about this one, though.

    If that doesn't help, you will have to provide more of your code - apparently sSELECT and sFROM are already populated before the fragment you posted.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax (2000)

    Thanks Hans, the semi colon 'crept' in there as a result of my n th attempt and creeping syntax blindness! Here is the full code

    Function buildSQLstring(strSQL As String) As Boolean

    Dim sSELECT As String
    Dim sFROM As String
    Dim sWHERE As String

    sSELECT = "tblmain.* "

    sFROM = "tblmain "

    'extract for string TM details

    If ckTM Then

    sSELECT = sSELECT & ",Teams.[Team Manager] "
    sFROM = sFROM & " INNER JOIN Individuals INNER JOIN tblmain ON Individuals.[Individual ID] = tblmain.Customer ON Teams.[Team ID] = Individuals.Team"

    sWHERE = " and Teams.[Team Manager]= " & cmbtm


    End If

    'other selections that are all in main table
    If ckCust Then
    sWHERE = sWHERE & " AND tblmain.customer = " & cmbind
    End If

    If ckCoach Then
    sWHERE = sWHERE & " AND tblmain.coach = " & cmbcoach
    End If

    If ckds Then
    If Not IsNull(cmbstart) Then
    sWHERE = sWHERE & " AND tblmain.Date >= " & _
    "#" & Format$(cmbstart, "mm/dd/yy") & "#"
    End If
    If Not IsNull(cmbend) Then
    sWHERE = sWHERE & " AND tblmain.Date <= " & _
    "#" & Format$(cmbend, "mm/dd/yy") & "#"
    End If
    End If

    If ckBand Then
    sWHERE = sWHERE & " AND tblmain.band = " & cmbBand
    End If

    If ckGoal Then
    sWHERE = sWHERE & " AND tblmain.[goal achieved?] = " & optgoal
    End If

    If ckcomp Then
    sWHERE = sWHERE & " AND tblmain.complete = " & optcomp
    End If

    strSQL = "SELECT " & sSELECT
    strSQL = strSQL & "FROM " & sFROM
    If sWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(sWHERE, 6)

    buildSQLstring = True

    End Function

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: SQL syntax (2000)

    Try this line:

    sFROM = sFROM & " INNER JOIN (Individuals INNER JOIN Teams ON Individuals.Team = Teams.[Team ID]) ON tblmain.Customer = Individuals.[Individual ID])"

    I added parentheses and changed the order.

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax (2000)

    Phew, thanks that works, I am 4/5 there! I can't get this to work;

    If ckTM Then


    sSELECT = sSELECT
    sFROM = sFROM & " INNER JOIN (Individuals INNER JOIN Teams ON Individuals.Team = Teams.[Team ID]) ON tblmain.Customer = Individuals.[Individual ID]"

    sWHERE = " and Teams.[Team Manager]= " & cmbtm


    End If

    'extract for string smt details

    If ckSMT Then

    sSELECT = sSELECT
    sFROM = sFROM & " INNER JOIN (Individuals INNER JOIN Teams ON Individuals.Team = Teams.[Team ID]) ON tblmain.Customer = Individuals.[Individual ID]"


    sWHERE = " And [Teams].[smt]= " & cmbsmt

    End If

    All my other criteria work fine in all combinations. the syntax breaks when the query has the two selections above i.e SMT and TM.

    thanks Darren.

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax (2000)

    I have taken a break, helps you to think clearer, resolved the issue with:

    'extract for string TM details

    If ckTM And Not ckSMT Then


    sSELECT = sSELECT
    sFROM = sFROM & " INNER JOIN (Individuals INNER JOIN Teams ON Individuals.Team = Teams.[Team ID]) ON tblmain.Customer = Individuals.[Individual ID]"

    sWHERE = " and Teams.[Team Manager]= " & cmbtm


    End If


    'extract for string smt details

    If ckSMT And Not ckTM Then

    sSELECT = sSELECT
    sFROM = sFROM & " INNER JOIN (Individuals INNER JOIN Teams ON Individuals.Team = Teams.[Team ID]) ON tblmain.Customer = Individuals.[Individual ID]"


    sWHERE = " And [Teams].[smt]= " & cmbsmt

    End If

    'extract SMT and TM together


    If ckSMT And ckTM Then

    sSELECT = sSELECT
    sFROM = sFROM & " INNER JOIN (Individuals INNER JOIN Teams ON Individuals.Team = Teams.[Team ID]) ON tblmain.Customer = Individuals.[Individual ID]"


    sWHERE = " And [Teams].[smt]= " & cmbsmt & " And [teams].[team manager]= " & cmbtm

    End If

    Strung the query with three if's, one for TM but not SMT, for SMT not TM, then tm and SMT#

    Thanks Darren

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL syntax (2000)

    If your code refers to checkboxes, you are creating memory leaks by referring to them as if they were boolean values, which they are not. Instead of this

    <code>If ckSMT And ckTM Then</code>

    Use this

    <code>If ckSMT = True And ckTM = True Then</code>

    and save yourself some headaches down the road.
    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax (2000)

    Thanks Charlotte.

    Darren.

Posting Permissions

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