Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Location
    Albuquerque, New Mexico, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access report like Excel (97)

    I have a report that looks like an Excel spreadsheet but that is written in Access. I must reference a cell from the previous line but I don't know how to do this. I can do it in Excel but not in Access. Any ideas?

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report like Excel (97)

    If it "looks like an Excel spreadsheet," you are probably referring to an Access table. Although they look similar, they are very different. One does not normally reference a table's "cell" (which is actually a field (column) value in a record (row) of the database table) from another "cell".

    Access forms can also be made to "look" somewhat like Excel worksheets, but they are typically "controlled" representations of table contents (one of many uses for forms).

    Likewise, Access reports can be made to "look" like an Excel worksheet (but it's really "looks" only), but they are "read only" -- you can't interact with them, change "cell" values, etc.

    If you could describe what you are trying to do (the bigger picture), maybe somebody here can help you down the Access path to a solution. If you try to "speak Excel" while working with Access, I suspect you will become (if you aren't already) very frustrated.

    Hope this helps.

  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Location
    Albuquerque, New Mexico, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report like Excel (97)

    I said that I have an Access REPORT that looks like an Excel Spreadsheet. It has columns and rows just like a spreadsheet but it's an Access REPORT. I need to get a date from the previous line and use it in a computation in the current line. I know how to do this in Excel but not in Access. That's what I want to do: use some information from the previous line of the REPORT in the current line of the report.

    Does anyone have an idea how to do this?

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report like Excel (97)

    Okay, I think I understand better now. Your use of "cell" got me off on a tangent.

    If you're familiar with VBA, you can put some code in your Detail section's On Format or On Print event to process the previous value (saved -- see below --during the last pass through this routine when the previous line of the report was generated). Then before leaving the routine, save the value used in this line for use the next time through. The saved value will need to be declared as a global variable (outside the event routine) to be retained from line-to-line.

    If you need more detail, holler; I'm sure someone here will be able to fill in the gaps or provide an alternative approach.

    Hope this helps (this time!).

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access report like Excel (97)

    Don't even try to think of it as you do Excel. Access reports are created a record at a time, so the other "cells" aren't there to refer to even though they appear to be when you print the report.

    You can put an unbound control into the detail section of the report, make it zero height and hide it by making its foreground, background and border colors all white and at the end of the format event of the detail section, set the value of that unbound textbox to the date for the current record. You'll need to be sure to do that only when FormatCount = 1. Then you can either directly reference that control in a calculated control in the detail section. .

    The other option is to use a module level variable to hold the date instead of an unbound control. In that case, you would set the value of your calculated control instead of having the calculation in the controlsource.
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access report like Excel (97)

    Actually, thinking about it, you should probably put any unbound textbox somewhere ahead of the detail section. That way the value won't be changed before it should be.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report like Excel (97)

    You wrote "You can put an unbound control into the detail section of the report, make it zero height and hide it by making its foreground, background and border colors all white ..."

    Is there an advantage in hiding it this way rather than just setting its visible property to false?

    Peter

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access report like Excel (97)

    I think it depends on the version of Access. I don't recall whether 97 would allow you to reference a hidden control on a report. The color method works in all versions.
    Charlotte

  9. #9
    Star Lounger
    Join Date
    Feb 2002
    Location
    Albuquerque, New Mexico, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report like Excel (97)

    Thanks for the help! Yes, this does look like the right approach. I'm very shaky with VBA and I need a bit more help with this. The VBA I put in is like this:


    Private Static Sub Report_Open(Cancel As Integer)
    Dim PreviousRevisedValue As Date

    If Me![PreviousRevisedValue] Is Null Then
    Me![OriginalValue] = OriginalValue
    Else
    Me![OriginalValue] = PreviousRevisedValue
    End If

    Me![RevisedValue] = compute it here
    PreviousRevisedValue = Me![RevisedValue]
    End Sub

    In English, I want to use the original value in the computation if this is the first time through the routine; there is nothing in the previous revised value which is why I'm checking to see if it is null. If the previous revised value is not null, I want to use that as the original value in the computation.

    I want to make my computation, print the revised value on the report and hold it as the (new) previous revised value.

    In otherwords, I'm trying to make a date on a new line dependent upon the date on the previous line. Am I going about this correctly? Like I said, I'm very shaky with this.

    Thanks!
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access report like Excel (97)

    Since you have only a single variable declared in the procedure, you can simply declare it as static without using the static keyword in the procedure declaration. Just declare it like this:

    Static PreviousRevisedValue As Date

    Is PreviousRevisedValue also a control name? Your variables and your controls should have different names. Otherwise, you'll confuse Access and yourself.
    Charlotte

  11. #11
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report like Excel (97)

    Looks like you are on the right track. A couple suggestions (in addition to Charlotte's suggestion to just declare your "PreviousDate" variable as static):

    The routine needs to be in the Detail_Format or Detail_Print event routine. The Report_Open routine only gets processed once. The Detail_Format routine gets processed each time a record from the source data is processed for output. The latter is what you want to do.

    Since the Detail_Format routine is processed once for each record, the controls in that section have values from the current record only; i.e., you don't have a Me!PreviouslyRevisedValue.

    Your PreviousDate variable will have a value of zero, not null, until it gets assigned the first time.

    So, the routine would be something like this:

    <font face="Georgia"> Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Static PreviousDate as Date

    If PreviousDate > 0 Then
    Me!CalculatedValue = f(Me!CurrentDate,PreviousDate) ' whatever calculation you need
    Else
    Me!CalculatedValue = ** what you want it to be when there's no previous value **
    End If

    ' (you could put the above into an iif() statement)

    PreviousDate = Me!CurrentDate

    End Sub</font face=georgia>

    If your Detail section of the report has a CalculatedValue control (unbound) and a CurrentDate control (bound to the date in your source data), this routine should work.

    Hope this helps (some more).

  12. #12
    Star Lounger
    Join Date
    Feb 2002
    Location
    Albuquerque, New Mexico, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report like Excel (97)

    Gosh! I'm really having problems with this. Can you tell me what I am doing wrong? I'm attaching my routine and I think it should work but it's not. My report contains WrkCtr in the heading and datRevCompDate (unbound) and DaysLeft (bound control). All I want to do is:

    If the WrkCtr is the same as the held WrkCtr, use the previous value for datRevCompDate and the current DaysLeft to compute the new value for datRevCompDate.
    If the WrkCtr has changed, use the current date (plus 31/48 of a day--3:30 pm) as the start date and add the current DaysLeft to it.

    This isn't rocket science but I keep getting errors on this. Can you look at my code and tell me what I'm doing wrong?

    Thanks bunches!
    Attached Files Attached Files

  13. #13
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report like Excel (97)

    It seems you're close. I'm not sure what 'datRevCompDate' is in your routine. It's not defined elsewhere. Is it a function call? It seems that you would want to assign both oldRevComplete and Me!datRevCompDate to holdRevCompDate at the end of the routine.

    (It appears you don't really need to use holdRevComplete at all; just assign oldRevCompDate in your If...Then statement (both branches) instead and then assign Me!datRevCompDate to oldRevCompDate at the end (or vise versa)).

    Also, I'm not sure you'll get the behavior you want the first time through the routine (the first record of your report) since oldWrkCtr hasn't been assigned a value yet. You may want to assign it an initial value in the Report_Open event (declare it outside the subroutines as Private oldWorkCtr As Variant instead of Static... inside the Detail_Format() routine). Assign it a value to ensure the desired branch of the If...Then statement is executed for the first row (or a unique value that you can test against in the Detail_Format() routine to handle the first row in a special way, if needed).

    Hope this helps.

  14. #14
    Star Lounger
    Join Date
    Feb 2002
    Location
    Albuquerque, New Mexico, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report like Excel (97)

    OK. I made the changes you suggested and I think things should work but . . . I am now having a problem with assigning the default WrkCtr to begin. Here is my code:

    Private Sub Report_Open(Cancel As Integer)
    Private oldWrkCtr As Variant
    oldWrkCtr = "000"
    End Sub

    I put this in the report's onOpen event but I'm getting a message telling me the sub or function for onOpen is not define. What on earth am I doing wrong this time?

  15. #15
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report like Excel (97)

    Put the declaration of oldWrkCtr outside the subroutine at the top of the module (just after the Option statements, if any). It should look something like this:
    <font face="Georgia">
    Option Compare Database
    Option Explicit

    Private oldWrkCtr As Variant
    .
    .
    .

    Private Sub Report_Open(Cancel As Integer)

    oldWrkCtr = "000"

    End Sub
    .
    .
    .
    </font face=georgia>
    Hope you're there now...

Page 1 of 2 12 LastLast

Posting Permissions

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