Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    61

    Arrow Unanswered: Need Help In Sql For Genius Programmer

    hi all
    i really need help in this problem
    i have this Tabe
    ---------------------------------------------------------------
    Make--- Model--- ModelYear----Qty----Year Of Sale
    ---------------------------------------------------------------
    Audi------A3------- 2004--------09-------2004
    Audi----- A4--------2004--------35-------2004
    Toyota---Echo------2005--------32-------2005
    Toyota---Corolla----2005--------40-------2005
    BMW-----318-------2006--------60-------2006
    BMW-----320-------2006--------80-------2006

    i worte thi s code to get the Sum Of the Quantities For Spacific Dates
    like 1/1/2004 and 31/12/2006

    List2 Contains Of All The MAKES and i Chhose what MAKEsl i want to see

    here what i do
    Dim i As Integer
    Dim str As String
    M0 = Make
    Fd1 Is the From Date
    Td1 Is The To Date

    If List2.ListCount = 1 Then
    str = S0
    Sql = "Select CYear, Sum(Qty) As Qty From CarSales Where " & M0 & "
    = '" & str & "' and CCDate Between #" & fd1 & "# and #" & td1 & "#
    Group By CYear "
    rs.Open Sql
    Set DataGrid1.DataSource = rs
    Set MSChart1.DataSource = rs
    ------------ Untill Here Works Perfect -------------------
    Else
    For i = 0 To List2.ListCount - 1
    str = str & "," & List2.List(i)
    Next i
    str = Mid(str, 2, Len(str))
    Sql = " Select CYear, Sum(Qty) As Qty From CarSales Where
    CCDate Between #" & fd1 & "# and #" & td1 & "# and " & M0 & " IN ('" & str & "') Group By CarSales.CYear"
    rs.Open Sql
    Set DataGrid1.DataSource = rs
    Set MSChart1.DataSource = rs
    End If
    ------------------- Doesnt WOrk At all -----------------------
    If I Choose One MAke It goes Right
    If i Choose More Than One Its Doesnt Work
    Any Help


    when I Choose 2 or 3 or 4 Between To Dates it Must Be like that

    If the FROM DATE WAS 1/1/2004 and The TO DATE Was 31/12/2004
    it must Gives me the THE SUM OF QUANTITES FOR YEAR 2004 For The MAKES I CHOOSE .

    For EXample
    CYEAR ----------------------- QTY
    2004-------------------------500

    THAT EXAPLE FOR Example for TOYOTA AND BMW AND CHERY


    I hope I WILL GET HELP
    I NEED TO MAKE THIS PROJECT WITHIN ONE WEEK
    I REALLY NEED HELP


    REGARDS
    Last edited by Sam76; 03-14-06 at 09:28.
    Sami Al Damiri ...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is wrong -- In (Audi, BMW, Chery)

    use quotes around literal string values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2005
    Posts
    61
    I Chage It It Becomes ('" & str & "') But Also It Doesnt Work
    AnyIdea
    Sami Al Damiri ...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    maybe you did this -- In ('Audi, BMW, Chery')

    that's wrong

    it should be like this -- In ('Audi', 'BMW', 'Chery')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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