Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Code Help (2002)

    Hi,

    Francoise gave me the following code a few years back and it works great. My problem is that I have more years than columns now and need to eliminate the year 2000. I went into the query that is referred to in the code and set it to only show years >2000. The query runs fine but the report still starts with year 2000. I'm wondering where in the code I'd set that filter. I've attached a report to show you how the end result looks.

    Private Sub Report_Open(Cancel As Integer)
    Const conNumColumns = 11
    Dim rst As DAO.Recordset
    Dim intColumnCount As Integer
    Dim intX As Integer

    On Error GoTo Handle_Err

    ' Open recordset.
    Dim qdf As DAO.QueryDef, rs As DAO.Recordset, sSql As String, i As Integer
    Dim sSqlL As String, sSqlR As String, iPosP As Integer, iPosG As Integer
    Dim iPosW As Integer
    Set qdf = CurrentDb.QueryDefs("qryGLHoursByYear")
    sSql = "SELECT Min(Year) as MinYear, Max(Year) as MaxYear"
    sSql = sSql & " FROM tblGeneralLedger"
    Set rs = CurrentDb.OpenRecordset(sSql)
    sSql = qdf.SQL
    iPosW = InStr(1, sSql, "WHERE")
    If iPosW = 0 Then
    iPosW = InStr(sSql, "GROUP")
    End If
    sSqlL = Left(sSql, iPosW - 1)
    iPosP = InStr(1, sSql, "PIVOT")
    iPosP = InStr(iPosP, sSql, ";") - 1
    iPosG = InStr(1, sSql, "GROUP")
    sSqlR = mid(sSql, iPosG, iPosP - iPosG + 1)
    sSql = sSqlL & " WHERE Month=" & Chr(34) & Forms![frmGeneralLedger]!cmbReportMonth & Chr(34)
    sSql = sSql & " " & sSqlR & " in ("
    Dim iMinYear As Integer, iMaxYear As Integer
    iMinYear = rs!minyear
    iMaxYear = rs!maxyear
    If iMaxYear - iMinYear + 1 > conNumColumns - 3 Then
    iMinYear = iMaxYear - conNumColumns + 4
    End If
    For i = iMinYear To iMaxYear
    sSql = sSql & i
    If i <> iMaxYear Then
    sSql = sSql & ", "
    End If
    Next i
    sSql = sSql & ")"
    Set rst = CurrentDb.OpenRecordset(sSql, dbOpenDynaset)
    Set qdf = Nothing
    Set rs = Nothing
    Me.RecordSource = sSql

    ' Don't open report if there are no data.
    If rst.RecordCount = 0 Then
    MsgBox "No records found.", vbInformation
    Cancel = True
    GoTo Handle_Exit
    End If

    ' Fix number of columns in crosstab query and limit to max available.
    intColumnCount = rst.Fields.Count
    If intColumnCount >= conNumColumns Then
    intColumnCount = conNumColumns - 1
    End If

    ' Set control source of text box in group footer to first field in crosstab query.
    txtGroupName.ControlSource = rst(0).Name

    For intX = 1 To intColumnCount
    ' Set caption of label in page header to field name.
    Me("txtHeading" & intX).Caption = rst(intX - 1).Name
    ' Set control source of text box in detail section to field name; replace nulls by 0.
    Me("txtColumn" & intX).ControlSource = "=Nz([" & rst(intX - 1).Name & "], 0)"
    Next intX

    ' Start totals in column 4 (the first column with a crosstab value).
    For intX = 4 To intColumnCount
    ' Set control source of text box in group footer to sum of corresponding field; replace nulls by 0.
    Me("txtSubtotal" & intX).ControlSource = "=Nz(Sum([" & rst(intX - 1).Name & "]), 0)"
    ' Set control source of text box in report footer to sum of corresponding field.
    Me("txtTotal" & intX).ControlSource = "=Sum([" & rst(intX - 1).Name & "])"
    Next intX

    DoCmd.Maximize

    Handle_Exit:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Exit Sub

    Handle_Err:
    MsgBox Err.Description, vbExclamation
    Resume Handle_Exit
    End Sub


    Thanks,
    Leesha
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Help (2002)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> You probably mean Francois (male)

    The lines

    sSql = "SELECT Min(Year) as MinYear, Max(Year) as MaxYear"
    sSql = sSql & " FROM tblGeneralLedger"

    specify the SQL to retrieve the first and last year from the table tblGeneralLedger, not from the query. You could change the second line to

    sSql = sSql & " FROM qryGLHoursByYear"

    If that doesn't work, change the second line to

    sSql = sSql & " FROM tblGeneralLedger WHERE Year > 2000"

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code Help (2002)

    LOL!!! Thanks for the hint! Made my day.

    I tried both pieces of code you gave me. I get an error on both. With sSql = sSql & " FROM tblGeneralLedger WHERE Year > 2000"
    I get an error stating that the data mismatch in criteria expression and with sSql = sSql & " FROM qryGLHoursByYear"
    I get that the microsoft yet ...... does not recognize year as a valid field name or expression.
    The query runs fine so I'm not sure where that is coming from. The query is a crosstabe query and I tried the filter both in that query and the select query it is based off of and the error remains.

    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Help (2002)

    What kind of field is Year? Number or Text?

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code Help (2002)

    It comes from Year: Year([beg_dt]) where beg_dt is a date format.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Help (2002)

    Does this work?

    sSql = sSql & " FROM tblGeneralLedger WHERE Year([beg_dt]) > 2000"

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code Help (2002)

    You are the best!! Worked beautifully!

    Thanks,
    Leesha

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code Help (2002)

    Back again. I have the similar code in a different report and in the query [year] is formatted as text and isn't formatted off of another date.

    Thanks,
    Leesha

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Help (2002)

    It'd have to be something like this:
    <code>
    sSql = sSql & " FROM ... WHERE [Year] > '2000'"
    </code>
    The single quotes around 2000 are necessary for a text value.

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code Help (2002)

    Thanks for the explanation on the single quotes and why. I was thinking I'd need to use that CH (char I think its called) thing, which I avoid like the plague because it just doesn't make sense to me!

    Thanks,
    Leesha

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Help (2002)

    Using Chr(...) would have been possible too <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code Help (2002)

    "Possible" maybe, but oh so painful had I tried it. At least I was on the right track, although I had no idea where I was going!!!

    Thanks again,
    Leesha

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Help (2002)

    See <post:=230,516>post 230,516</post:> for an overview of the possibilities, with an attempt (however misguided) to explain the why and how.

Posting Permissions

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