Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    43

    Unanswered: SQL Thread with Access

    I have a statement (see below) for a graph on a form that is created by the users choices, I want to sort on

    qry_squareft WHERE qry_squareft.itm_serial = '04' "
    tmp_Str = tmp_Str & "AND qry_squareft.squ_value BETWEEN '" & OpenStart & "' AND '" & EndStart & "')"

    the following for the output, can anyone assist, I have tried a bunch of things. The itm_serial = 04 is the serial key field for the year of the project, and the dates are Open and End. So I would like it so that any output is sorted by the squ_value (ie the year)





    Private Sub cmdSubmit_Click()

    Dim Group As String
    Dim State As String
    Dim Ptype As String
    Dim OpenStart As String
    Dim EndStart As String
    Dim Sqrstr As String
    Dim Sqrend As String
    Dim tmp_Str As String

    Me.grp_desc.SetFocus
    Group = grp_desc.Text 'Pass value of group to string

    Me.cmbState.SetFocus
    State = cmbState.Text 'Pass value of state to string

    Me.cmbPType.SetFocus
    Ptype = cmbPType.Text 'Pass value of project type to string

    Me.txtOpenStr.SetFocus
    OpenStart = txtOpenStr 'Pass value of Open Start Date to String

    Me.txtOpenEnd.SetFocus
    EndStart = txtOpenEnd 'Pass value to Open End Start Range Date to String

    Me.txtSqrStr.SetFocus
    Sqrstr = txtSqrStr 'Pass value of Start SqrFt

    Me.txtSqrEnd.SetFocus
    Sqrend = txtSqrEnd 'Pass value of End SqrFt

    If radTotal.Value = True Then 'Pull all total cost

    tmp_Str = "TRANSFORM Sum(fig_cost) AS SumOffig_cost SELECT sto_name FROM qry_conditions "
    tmp_Str = tmp_Str & "WHERE sto_name IN (SELECT sto_name FROM qry_squareft WHERE qry_squareft.itm_serial = '02' AND qry_squareft.squ_value LIKE '" & State & "' )"

    tmp_Str = tmp_Str & "AND sto_name IN(SELECT sto_name FROM qry_squareft WHERE qry_squareft.itm_serial = '05' "
    tmp_Str = tmp_Str & "AND qry_squareft.squ_value LIKE '" & Ptype & "')" 'Pull the item serial and results for Project Type

    tmp_Str = tmp_Str & "AND sto_name IN(SELECT sto_name FROM qry_squareft WHERE qry_squareft.itm_serial = '01' "
    tmp_Str = tmp_Str & "AND qry_squareft.squ_value BETWEEN '" & Sqrstr & "' AND '" & Sqrend & "')" 'Pull the SqrFt

    tmp_Str = tmp_Str & "AND sto_name IN(SELECT sto_name FROM qry_squareft WHERE qry_squareft.itm_serial = '04' "
    tmp_Str = tmp_Str & "AND qry_squareft.squ_value BETWEEN '" & OpenStart & "' AND '" & EndStart & "')" 'Pull the Opening Date

    tmp_Str = tmp_Str & "GROUP BY sto_name, fig_cost "

    tmp_Str = tmp_Str & "ORDER BY fig_cost " 'Order results by

    tmp_Str = tmp_Str & "PIVOT '" & Group & "' ;"

    ElseIf radcost.Value = True Then 'Pull all total cost/sqrft

    tmp_Str = "TRANSFORM Sum(qry_totalvssqrft.Cost_SqrFt) AS SumOffig_cost SELECT qry_totalvssqrft.sto_name FROM qry_totalvssqrft "
    tmp_Str = tmp_Str & "WHERE sto_name IN (SELECT sto_name FROM qry_squareft WHERE qry_squareft.itm_serial = '02' AND qry_squareft.squ_value LIKE '" & State & "' )"

    tmp_Str = tmp_Str & "AND sto_name IN(SELECT sto_name FROM qry_squareft WHERE qry_squareft.itm_serial = '05' "
    tmp_Str = tmp_Str & "AND qry_squareft.squ_value LIKE '" & Ptype & "')" 'Pull the item serial and results for Project Type

    tmp_Str = tmp_Str & "AND sto_name IN(SELECT sto_name FROM qry_squareft WHERE qry_squareft.itm_serial = '01' "
    tmp_Str = tmp_Str & "AND qry_squareft.squ_value BETWEEN '" & Sqrstr & "' AND '" & Sqrend & "')" 'Pull the SqrFt

    tmp_Str = tmp_Str & "AND sto_name IN(SELECT sto_name FROM qry_squareft WHERE qry_squareft.itm_serial = '04' "
    tmp_Str = tmp_Str & "AND qry_squareft.squ_value BETWEEN '" & OpenStart & "' AND '" & EndStart & "')" 'Pull the Opening Date

    tmp_Str = tmp_Str & "GROUP BY qry_totalvssqrft.sto_name "

    tmp_Str = tmp_Str & "ORDER BY qry_totalvssqrft.sto_name " 'Order results by

    tmp_Str = tmp_Str & "PIVOT ('" & Group & "') ;" 'create the pivot table

    End If

    DoCmd.SetWarnings False

    Me.Graph1.RowSource = tmp_Str

    Me.grp_desc.SetFocus

    Me.Graph1.Visible = True




    End Sub

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Maybe I'm not reading this right but isn't it to sort this...

    qry_squareft WHERE qry_squareft.itm_serial = '04' "
    tmp_Str = tmp_Str & "AND qry_squareft.squ_value BETWEEN '" & OpenStart & "' AND '" & EndStart & "')"

    on squ_value just...

    qry_squareft WHERE qry_squareft.itm_serial = '04' "
    tmp_Str = tmp_Str & "AND qry_squareft.squ_value BETWEEN '" & OpenStart & "' AND '" & EndStart & "' Order by squ_value"

    unless I'm misreading this and squ_value is not 1 simple date value field (in which I'd make one of it and sort on that - or just take the int year part of squ_value.)
    Last edited by pkstormy; 03-11-08 at 19:31.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2003
    Posts
    43
    squ_value is a dynamic date field, and what I mean by that is the user can enter in a start year and end year which will pull all records based on that year. And yes it is not just a year, based on what key field was pulled it can be a number as well. However if the key field equals 4 (which is always pulled as part of the sub querey, then it is the year and as such I want to sort by year.

    Bill

  4. #4
    Join Date
    Oct 2003
    Posts
    43

    Found Answer

    Turns out that the programmer before me made a mistake in the table structure and so when I was including the fields for sorting in the agerated function it did not sort on them correctly.

    Bill

Posting Permissions

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