Results 1 to 3 of 3

Thread: Chart Problems

  1. #1
    Join Date
    May 2005
    Posts
    1,191

    Unanswered: Chart Problems

    Hey all,

    I'm trying out the whole chart thing from vb for the first time, so bare with my if I'm more incoherent than normal.

    All the data will be based off of a table called Tbl_NCM, with the important fields called Fld_Qty_Rejected, Fld_Date_Entered, and Fld_Product_Line. The field Fld_Qty_Rejected is a nuber field with the number of parts rejected; the field Fld_Date_Entered is a date field with the date the record was entered; the field Fld_Product_Line is a text field that is restricted to a drop down of about 6 different options.

    What I'd like to happen is to let the user enter in which product line he/she is interested in and over what date range (from x to y). They press a button, and the chart then displays a line graph with how many parts were rejected for that product line each month (sum) by month. For example; 5 parts in Jan 06, 7 parts in Feb 06, 12 in Mar 06, etc.

    Is this possible? If so, how? Below is what I have so far on the click of the button event, based largely on what ms defaulted to when I tried to create the chart from scratch:
    Code:
    str_SQL_Graph = "SELECT (Format([Fld_Date_Entered],'mmm'' '''yy')) " & _
                    "AS Expr1, Sum(Tbl_NCM.Fld_Qty_Rejected) AS SumOfFld_Qty_Rejected " & _
                    "FROM Tbl_NCM " & _
                    "WHERE (((Tbl_NCM.Fld_Product_Line) = '" & Me.CmbBox_Product_Line & "')) " & _
                    "GROUP BY (Format([Fld_Date_Entered],'mmm'' '''yy')), " & _
                    "(Year([Fld_Date_Entered])*12+Month([Fld_Date_Entered])-1);"
    
    'update chart
    With Forms.Item("Frm_Report1").Controls.Item("Gph_Dept_Rej")
        .RowSource = str_SQL_Graph
        .Action = acOLEActivate
        .Verb = acOLEVerbHide
    End With
    *Note, this code doesn't even work, it gives me an error and says the SQL is stupid (in not so many words).
    Me.Geek = True

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    So for anyone else with a similar problem, here's the final code I have (thanks to RedNeckGeek, off of which I started my new SQL):
    Code:
    Private Sub CmdBtn_Update_Click()
    
    On Error GoTo Err_CmdBtn_Update_Click
    
    DoCmd.Hourglass True
    DoCmd.Echo False
    
    'update label
    Me.Lbl_Header.Caption = "Report"
    If Me.CmbBox_Product_Line <> "" Then
        Me.Lbl_Header.Caption = Me.CmbBox_Product_Line & " " & Me.Lbl_Header.Caption
    End If
    
    'Validate the data
    If Me.CmbBox_Product_Line = "" Or IsNull(Me.CmbBox_Product_Line) Then Me.CmbBox_Product_Line = "*"
    If Me.TxtBox_Date_From = "" Or IsNull(Me.TxtBox_Date_From) Then Me.TxtBox_Date_From = #1/1/1900#
    If Me.TxtBox_Date_To = "" Or IsNull(Me.TxtBox_Date_To) Then Me.TxtBox_Date_To = Date
    
    'define data sets
    Dim str_SQL_Graph As String, str_SQL_Form As String
    str_SQL_Form = "SELECT Tbl_NCM.Fld_NCM_No, Tbl_NCM.Fld_Date_Entered, Tbl_NCM.Fld_Qty_Rejected, " & _
                    "Tbl_NCM.Fld_Part_No, Tbl_NCM.Fld_Description " & _
                    "FROM Tbl_NCM " & _
                    "WHERE (((Tbl_NCM.Fld_Product_Line) = '" & Me.CmbBox_Product_Line & "')) " & _
                    "ORDER BY Tbl_NCM.Fld_NCM_No DESC;"
    str_SQL_Graph = "SELECT Format([Fld_Date_Entered],'mm/yy') AS [Date], Sum(Tbl_NCM.Fld_Qty_Rejected) AS SumOfRej " & _
                    "FROM Tbl_NCM " & _
                    "WHERE (((Tbl_NCM.Fld_Date_Entered) Between #" & Me.TxtBox_Date_From & "# And #" & Me.TxtBox_Date_To & "#) " & _
                        "AND ((Tbl_NCM.Fld_Product_Line) Like '" & Me.CmbBox_Product_Line & "')) " & _
                    "GROUP BY Format([Fld_Date_Entered],'mm/yy') " & _
                    "ORDER BY Format([Fld_Date_Entered],'mm/yy');"
    
    'change back for appearences
    If Me.CmbBox_Product_Line = "*" Then Me.CmbBox_Product_Line = ""
    
    'update form
    Me.Form.RecordSource = str_SQL_Form
    
    'update chart
    With Forms.Item("Frm_Report1").Controls.Item("Gph_Dept_Rej")
        .RowSource = str_SQL_Graph
        .Action = acOLEUpdate
    End With
    
    Me.CmdBtn_Update.SetFocus
    
    Exit_CmdBtn_Update_Click:
        DoCmd.Hourglass False
        DoCmd.Echo True
        Exit Sub
    
    Err_CmdBtn_Update_Click:
        DoCmd.Hourglass False
        DoCmd.Echo True
        Msg = "Error # " & str(Err.Number) & " was generated by " & Err.Source _
            & vbNewLine & vbNewLine & "Description: " & Err.Description _
            & vbNewLine & vbNewLine & "Please contact the database administrator."
        Select Case Err.Number
            Case 2771
                MsgBox "Your criteria yielded no results." & vbNewLine & vbNewLine & _
                        "Please try again."
                Me.TxtBox_Date_From = ""
                Me.TxtBox_Date_To = ""
                Me.CmbBox_Product_Line = ""
            Case Else
                MsgBox Msg, vbMsgBoxHelpButton, "Error", Err.HelpFile, Err.HelpContext
        End Select
        Resume Exit_CmdBtn_Update_Click
    
    End Sub
    I now only have one problem. I've attached a picture of what my data looks like from the row source of my graph, and a pdf of what appears on the chart. Note the stupidity (either on my part or the computer's, not sure who's yet). Can anyone tell my why it's doing this? Thanks.

    *Note, for now I just have sample data entered into the table to see if I can make this work. The real table will eventually have hundreds/thousands of records.
    Attached Thumbnails Attached Thumbnails table.JPG   report.JPG  
    Last edited by nckdryr; 01-26-07 at 12:25.
    Me.Geek = True

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    Does anyone have any ideas or do I need to rephrase/clarify something?
    Me.Geek = True

Posting Permissions

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