Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    dynamic report (97)

    Hi All,
    I have modified some code that was found through a search on on this board to create a dynamic report. The column columnalias is suppose to get updated with A, B, C etc. and the bytLevel should be updated with 0 for a given amount of columns then a 1 for the rest of the columns. When the functions runs, it fills in an A in every row and a 0 in every row. The code does not seem to add 1 to the ascii value as it is looping or add 1 tp the bytlevel after so many rows. Does anyone see what I have done wrong? Thank you in advance.
    John
    Option Compare Database
    Option Explicit
    Function UpdateReqDateAlias(pbytNumColumns As Byte) As Long
    '================================================= ===========
    ' Purpose:
    ' Copyright: 1999 Business Results
    ' Company: Business Results
    ' Phone: 715-835-8130
    ' E-Mail: dhookom@invisibleinc.com
    ' Programmer: Duane Hookom
    ' Called From:
    ' Date: 1/22/00
    ' Parameters:
    '================================================= ===========
    ' On Error GoTo UpdateReqDateAlias_Err
    Dim strErrMsg As String 'For Error Handling
    Dim strSQL As String
    Dim intAlias As Integer
    Dim bytLevel As Byte
    Dim ShipDate As Date
    Dim bytMaxColumns As Byte
    Dim db As Database
    Dim rs As Recordset
    strSQL = "Delete * from tblReqDateAlias"
    'DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.OpenQuery "qappDates" 'create table of unique dates and levels
    ' DoCmd.SetWarnings True
    bytMaxColumns = 12
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblReqDateAlias") 'table used to redefine/alias the column headings
    With rs
    If Not (.EOF And .BOF) Then
    .MoveFirst
    Do While Not .EOF
    ShipDate = !SDate
    bytLevel = 0
    intAlias = 65 'ascii value of 'A'
    Do While !SDate = ShipDate
    .Edit
    !Level = bytLevel
    !ColumnAlias = Chr(intAlias) 'assign alias A - whatever
    .Update
    intAlias = intAlias + 1
    If intAlias = 65 + bytMaxColumns Then
    bytLevel = bytLevel + 1
    intAlias = 65
    End If
    .MoveNext
    If .EOF Then
    Exit Do
    End If
    Loop
    Loop
    End If
    End With
    UpdateReqDateAlias_Exit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Function
    UpdateReqDateAlias_Err:
    Select Case Err
    Case Else
    UpdateReqDateAlias = Err.Number
    Resume UpdateReqDateAlias_Exit
    End Select
    End Function

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

    Re: dynamic report (97)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: dynamic report (97)

    Hi Hans,
    Thank you for looking at the db for me. As you can see the column columnalias has been updated to all As and column Level has been updated to all 0. When it loops correctly it should fill in A through G and 0 for first 7 records and 1 for next records.
    Thanks again.
    John

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

    Re: dynamic report (97)

    The idea behind the code was to increase the column alias as long as a specific field (SDate in your example) stays the same, then start over again when the field changes.
    Your table tblReqDateAlias contains unique dates, so the column alias starts over at every record.
    See if the attached simplified version does what you want. I took out the check for SDate remaining the same.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: dynamic report (97)

    This is exactly what I was looking for. Thanks again. Now to move on and get the report designed using this data. Wish me luck.
    John

Posting Permissions

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