Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: Adding an Is Not Null stmt

    I have a form .. and I need to add some piece of the code into it.. I figured out how to do it.. but don't know how to do it...

    heres the code:

    Private Sub Command6_Click()
    Dim sqlString As String
    sqlString = ""

    If Not (IsNull(Me.txtStartOpen3) Or IsNull(Me.txtStartEnd3)) Then
    sqlString = " tblInspections.strDate Between #" & Me.txtStartOpen3 & "# And #" & Me.txtStartEnd3 & "# "
    End If
    '...

    If sqlString <> "" Then sqlString = " Where" & sqlString
    sqlString = "SELECT Count(tblInspections.FLT) AS CountOfFLT, Left([FLT],2) AS FaultType FROM tblInspections INNER JOIN tblQR ON (tblInspections.strReference = tblQR.strReference) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strProject = tblQR.strProject) " & sqlString & " GROUP BY Left([FLT],2);"

    Debug.Print sqlString
    CurrentDb.QueryDefs.Delete "query5"
    CurrentDb.CreateQueryDef "query5", sqlString
    Me.Refresh
    Me.Requery
    'DoCmd.OpenReport "Graph3", acViewPreview
    End Sub

    This is what I need to add to the code...

    WHERE FLT IS NOT NULL


    thanks

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I would bracket your first evaluated SQL string:

    If Not (IsNull(Me.txtStartOpen3) Or IsNull(Me.txtStartEnd3)) Then
    sqlString = " ((tblInspections.strDate Between #" & Me.txtStartOpen3 & "# And #" & Me.txtStartEnd3 & "#) "
    End If

    and then AND in your FLT requirement before your GROUP BY clause:

    If sqlString <> "" Then sqlString = " Where" & sqlString
    sqlString = "SELECT Count(tblInspections.FLT) AS CountOfFLT, Left([FLT],2) AS FaultType FROM tblInspections INNER JOIN tblQR ON (tblInspections.strReference = tblQR.strReference) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strProject = tblQR.strProject) " & sqlString & " AND (NOT IsNull(FLT))) GROUP BY Left([FLT],2);"

    From your code, it appears that the first evaluated SQL string is designed to be used with your If sqlString <> "" ... section, so you can set up the additional ( when you first define it, then close it off with ) before the GROUP BY.

    --> Of course you could just paste the SELECT Count(... SQL into a SQL pane, add the FLT requirement and switch back to the SQL pane to see how access manages it (ex the unnecessary table delimiters).
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Jul 2003
    Posts
    292
    Thanks for the response !

    I made the changes as you suggested... and when i run the code.. I get errors..

    Run-Time error '3296'
    Join expression not supported

    Did i do something wrong ?


    thanks

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    put a stop in your code just after you've built the final SQL statement, go to your debug window and ?sqlString.

    Post what you get.
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

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