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

    query by form (2000)

    Hello, I am trying to develop a query by form approach in my project. I have built the string function to be passed to my query def but keeping the error inVB, missing operator. I have scanned and scanned my code and am at a loss to determine the problem. Here is the code, could somebody please guide me on the errror of my ways?

    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

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

    sWHERE = " And [Team Managers].[Team Manager ID]= " & cmbtm

    End If

    'extract for string smt details

    If ckSMT Then

    sFROM = sFROM & " INNER JOIN Teams" & "ON [SMTs].[SMT ID] = Teams.[SMT] " & " INNER JOIN Individuals" & _
    " ON Teams.[Team ID] =Individuals.Team" _
    & " INNER JOIN tblmain " & " ON Individuals.[Individual ID] = tblmain.Customer " & "oN Teams.[Team ID] = Individuals.Team"

    sWHERE = " And [smts].[smt ID]= " & cmbsmt




    End If

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

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

    If ds Then
    If Not IsNull(cmbstart) Then
    sWHERE = sWHERE & " AND tblmain.Date >= "
    End If
    If Not IsNull(cmbend) Then
    sWHERE = sWHERE & " AND tblmain.Date <= "
    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


    Thanks Darren.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query by form (2000)

    Put a breakpoint at the line
    buildSQLstring = True
    When the code stop go to the Immediate Window (CTRL-G) and type :
    ? strSQL
    Look at the string and try to find the error in the sql or post the string so someone here can find it.
    Francois

Posting Permissions

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