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

    Unanswered: Some code help ......

    Hi,

    I have the code.. which here it searches for records within dates.. "txtStartOpen" and "txtStartEnd"

    Then it puts the data in a query.."query3"

    Code:
    Private Sub Command0_Click()
    Dim sqlString As String
    sqlString = ""
    
    If Not (IsNull(Me.txtStartOpen) Or IsNull(Me.txtStartEnd)) Then
    sqlString = " tblInspections.strDate Between #" & Me.txtStartOpen & "# And #" & Me.txtStartEnd & "# "
    End If
    '...
    
    If sqlString <> "" Then sqlString = " Where" & sqlString
    sqlString = "SELECT tblInspections.strDate, tblInspections.DATECLOSE, tblInspections.INSP FROM tblInspections INNER JOIN tblQR ON (tblInspections.strReference = tblQR.strReference) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strProject = tblQR.strProject)" & sqlString & ";"
    
    Debug.Print sqlString
    CurrentDb.QueryDefs.Delete "query3"
    CurrentDb.CreateQueryDef "query3", sqlString
    
    'DoCmd.OpenQuery "qryQualityStatus"
    End Sub
    I want to add something in my query to Count the first 2 letters of INSP field.. INSP is a field in my table "tblInspections" In the field INSP I have data as follows.. DR,DR3,DR9,SO7,SO6,SO,SO10.

    In my query I want to add a couple more columns to add in a count of INSP field.. like soo...

    column1 column2
    DR...............3
    SO...............4

    I came up with something like...

    DCount("[INSP]", "[tblInspections]", "left([INSP],2)

    How would I put that piece in my code.. ?

    thanks

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    build the query the way you want in using the Query By Example screen then copy the SQL out of the View SQL screen.

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

    This is what I have came up with...

    Code:
    Private Sub Command0_Click()
    Dim sqlString As String
    sqlString = ""
    
    If Not (IsNull(Me.txtStartOpen) Or IsNull(Me.txtStartEnd)) Then
    sqlString = " tblInspections.strDate Between #" & Me.txtStartOpen & "# And #" & Me.txtStartEnd & "# "
    End If
    '...
    
    If sqlString <> "" Then sqlString = " Where" & sqlString
    sqlString = "SELECT Count(tblInspections.INSP) AS CountOfINSP, Left([INSP],2) AS InspectionType FROM tblInspections INNER JOIN tblQR ON (tblInspections.strReference = tblQR.strReference) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strProject = tblQR.strProject) GROUP BY Left([INSP],2)" & sqlString & ";"
    
    Debug.Print sqlString
    CurrentDb.QueryDefs.Delete "query3"
    CurrentDb.CreateQueryDef "query3", sqlString
    
    End Sub
    The problem that I now face is.. In my form.. If I leave the date fields blank then.. It pulls all records and does a count of the first two letters fine...

    but if I enter in specific dates its comes back that I have an error..

    Run-time Error '3075'

    Syntax Error (missing operator) in query expression 'Left([INSP],2) Where tblInspections.strDate Between #10/01/2003# And #10/31/2003#'.

    Do have any ideas why and how to fix this ?

    thanks for the reply

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    It looks like you have WHERE after GROUP BY for cases when -- Not (IsNull(Me.txtStartOpen) Or IsNull(Me.txtStartEnd)) : --

    GROUP BY Left([INSP],2)" & sqlString & ";"

    sqlString = " tblInspections.strDate Between #" & Me.txtStartOpen & "# And #" & Me.txtStartEnd & "# "

    If sqlString <> "" Then sqlString = " Where" & sqlString

    That's where I would start. GROUP BY is preceded by WHERE and can only be succeeded by HAVING then ORDER BY.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  5. #5
    Join Date
    Jul 2003
    Posts
    292
    Could you explain in detail.. I'm new to all this... thanks for your time =)

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    He is saying to change this:
    "SELECT Count(tblInspections.INSP) AS CountOfINSP, Left([INSP],2) AS InspectionType FROM tblInspections INNER JOIN tblQR ON (tblInspections.strReference = tblQR.strReference) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strProject = tblQR.strProject) GROUP BY Left([INSP],2)" & sqlString & ";"

    to this:

    "SELECT Count(tblInspections.INSP) AS CountOfINSP, Left([INSP],2) AS InspectionType FROM tblInspections INNER JOIN tblQR ON (tblInspections.strReference = tblQR.strReference) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strProject = tblQR.strProject) " & sqlString & " GROUP BY Left([INSP],2);"

  7. #7
    Join Date
    Jul 2003
    Posts
    292
    Wow.. !! ok the˙˙

  8. #8
    Join Date
    Jul 2003
    Posts
    292
    Thanks so much... The code seems to be working now.. I do have another question if you guys dont mind... Now that I have this data ... I'm looking to graph this as a pie chart.. My question is.. Is there a way I can add another section to the pie chart..

    What I want to do is add a section to the pie .. "Civil" and this would consist of all fields with an AB and CO.. and from the sample below this would equal 2....

    So my pie chart would look something like.. a section for AB = 1, CO = 1, Civil = 2, DR = 1, etc...


    CountOfINSP...............InspectionType
    1......................................AB
    1......................................CO
    1......................................DR
    1......................................EL
    1......................................IN
    1......................................IS
    1......................................ME
    1......................................PC
    2......................................PF
    1......................................PS

    Thanks so much for your help !! =)



    Sorry if I posted this twice.. somethings wrong with dbforums.. I'm getting an error on page .. on this page..

Posting Permissions

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