Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Posts
    37

    Unanswered: Calculation in Access Report

    I have the following three fields in a report that I'm working on:

    Assigned Date
    RC Sent Date
    Days (calculated field)

    I want to populate the Days field with the number of days between the date the report is run and the Assigned Date, but if the Assigned Date field is empty I want to know the number of days between the date the report is run and the RC Sent Date.

    If both the Assigned Date and the RC Sent Date are populated, I want the system to base the calculation on the Assigned Date.

    With some help, ( treeless ) this is what I have so far, but it doesn’t give me what I’m looking for:
    In the control source for the control named Days I entered:

    =DateDiff("d",[RC Sent Date],IIf(IsNull([Assigned Date]),Date(),[Assigned Date]))

    When I use this I get the correct number of days if there is no Assigned Date and only an RC Sent Date
    But when there is an Assigned Date and no RC Sent Date I get nothing (no days, blank field)

    Can anyone help me ?

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    First off, your immediate if statement will calculate the difference between:
    1. [RC Sent Date] and Today if [Assigned Date] is Null
    2. [RC Sent Date] and [Assigned Date] if [Assigned Date] is Not Null
    3. and will return Null if [RC Sent Date] is Null (which is probably why you're getting nothing) no matrter what [Assigned Date] is.

    A far easier way to construct your conditional calculation is to use a function inside the module of the report (easier to write, easier to debug and easier to change and especially easier for the person after you to figure it out when the time comes.

    In the control source for the "Days" control, set it to =CalcDays()

    In the module for the report, add something similar to the following:
    Code:
    Function CalcDays() As Variant
        ' Logic:
        ' If value in 'Asigned Date' then find delta between it and today
        ' If not, if value in 'RC Date' then find delta between it and today
        ' If not, return zero
        Dim intD As Integer
        If Not IsNull(Me("Assigned Date")) Then
            intD = DateDiff("d", Me("Assigned Date").Value, Now())
        ElseIf Not IsNull(Me("RC Sent Date")) Then
            intD = DateDiff("d", Me("RC Sent Date").Value, Now())
        Else
            intD = 0
        End If
        
        CalDays = intD
    End Function
    If you insist on using an immediate if, here's the single line equivalent of the function I wrote above:
    = DateDiff("D", IIf(IsNull([Assigned Date]), IIf(IsNull([RC Sent Date]), Now(), [RC Sent Date]), [Assigned Date]), Now())

    Have fun

  3. #3
    Join Date
    Jan 2004
    Posts
    37

    Calculation in Access Report--getting closer....

    When I made the changes you suggested I got a compile error "invalid use of Me keyword" How do I correct this ?

    Function CalcDays() As Variant
    ' Logic:
    ' If value in 'Asigned Date' then find delta between it and today
    ' If not, if value in 'RC Date' then find delta between it and today
    ' If not, return zero
    Dim intD As Integer
    If Not IsNull(Me("Assigned Date")) Then
    intD = DateDiff("d", Me("Assigned Date").Value, Now())
    ElseIf Not IsNull(Me("RC Sent Date")) Then
    intD = DateDiff("d", Me("RC Sent Date").Value, Now())
    Else
    intD = 0
    End If

    CalDays = intD
    End Function

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Sure can; I wasn't specific enough in my instructions - sorry!

    You can use the module behind the report, in which case the "Me" keyword refers to the report it's behind.

    Sound like your function is in it's own module - which is probably a better place because many people wouldn't know to look for code behind a report. In the module, however, it doesn't know what "Me" refers to.

    2 things you could do:
    Replace Me with the Report's name

    or (this is probably better):
    1. Replace the first line of the function with Function CalcDays(FirstDate As Variant, SecondDate As Variant) As Variant
    2. In the rest of the function, replace Me("Assigned Date").Value with FirstDate
    3. In the rest of the function, replace Me("RC Sent Date").Value with SecondDate
    4. In your function call back on the report, change =CalcDays() with =CalcDays([Assign Date],[RC Sent Date])

    This is a better way becasue it doesn't require the report to be open, and you can use it elswhere - it's now a general function that you send 2 dates to, and it will return the days between the First Date and today, unless the first date is null in which case it'll return the days between the second date and today, and if that is also null, it will return 0.

    Enjoy!

  5. #5
    Join Date
    Jan 2004
    Posts
    37

    Calculation in Access Report

    I tried that and ran into another problem...see attach screen shot
    Attached Thumbnails Attached Thumbnails FUNCTION.jpg  

  6. #6
    Join Date
    Nov 2002
    Posts
    154

    Thumbs up Try the Print Event on the Report instead

    A solution can be this: click on the detail section that has the date fields that you want to work with, Assigned Date, RC Sent Date, Days. Select the On Print event on the events tab. There, put code something like this:
    dim dteDate as date

    if not isnull(Assigned Date) then

    dteDate = Assigned Date

    else

    dteDate = RC Sent Date

    end if

    Days = datediff("d",dteDate,Now())

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Quote:
    2. In the rest of the function, replace Me("Assigned Date").Value with FirstDate
    3. In the rest of the function, replace Me("RC Sent Date").Value with SecondDate

  8. #8
    Join Date
    Jan 2004
    Posts
    37

    Calculation in Access Report compile error

    Tcace
    That didn't work either, got another compile error--here is the screen shot...I'm going to post a striped down db for you to look at, if you would.
    Attached Thumbnails Attached Thumbnails another complie error.jpg  

  9. #9
    Join Date
    Jan 2004
    Posts
    37

    Calculation in Access Report--here is the db

    Tcace

    Attached is a striped down db

    Here is what I'm trying to do:

    I have the following three fields in a report that I'm working on:

    Assigned Date
    RC Sent Date
    Days (caculated field)

    I want to populate the Days field with the number of days between the date the report is run and the Assigned Date, but if the Assigned Date field is empty I want to know the number of days between the date the report is run and the RC Sent Date. If there is both an Assigned Date and an RC Sent Date I want to have the system base the caculation on the Assigned Date.
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    When you replaced [Assigned Date] with FirstDate in my code sample, you dropped the () for the IsNull function (same for SecondDate). If you put the argument for the IsNull statements in () then my code should work fine.

    In the db you posted, your code is:
    Code:
    Public Function MyDays(vDate1, vDate2)
         Dim vDateUse1, vDateUse2
         vDateUse1 = IIf(IsNull(vDate1), Date, vDate1)
         vDateUse2 = IIf(IsNull(vDate2), Date, vDate2)
    
         MyDays = DateDiff("d", vDateUse1, vDateUse2)
    End Function
    where vDate1 is getting [RC Sent Date] passed to it and vDate2 is getting [Assigned Date]

    So, to "read" your code:
    If [RC Sent Date] is null, use the current date, otherwise use [RC Sent Date] as vDateUse1
    If [Assigned Date] is null, use the current date, otherwise use [Assigned Date] as vDateUse2
    Calculate the days between vDateUSe1 and vDateUse2

    So,
    If [RC Sent Date] and [Assigned Date] are both Null, you get 0 (days between today and today).
    If [RC Sent Date] is null and [Assigned Date] is not, you get days between today and [Assigned Date]
    If [RC Sent Date] is not null and [Assigned Date] is, you get days between today and [RC Sent Date]
    If neither is null, you get the days between [RC Sent Date] and [Assigned Date]

    When you say If there is both an Assigned Date and an RC Sent Date I want to have the system base the caculation on the Assigned Date. I assume you want the days between today and [Assigned Date] which is not what you're getting here.

    You need to write your conditions:
    If [Assigned Date] is not null, use it as vDateUse1
    If [Assigned Date] is null, then: If [RC Sent Date] is not null, use it as vDateUse1 otherwise use today ad vDateUse1

    Also, use today as vDate2, so MyDays = DateDiff("d", vDateUse1, Date()).
    When you move on to bigger and fatter databases, you will want to minimize the use of the Variant datatype to improve performance. In this case, your internal dates are explicitly defined as dates when you assign then, so you can Dim them as Date data types.

    Good luck
    Last edited by tcace; 06-29-04 at 09:30.

  11. #11
    Join Date
    Jan 2004
    Posts
    37

    Unhappy Calculation in Access Report -still not right

    Tcace
    I must be stupid....never mind my test db I sent, I took too much out of it....back to my original...

    In the function call back on the report (DAYS field) I have this:

    =CalcDays([Assign Date],[RC Sent Date])

    And my fuction module looks like this:

    Function CalcDays(FirstDate As Variant, SecondDate As Variant) As Variant

    ' Logic:
    ' If value in 'Asigned Date' then find delta between it and today
    ' If not, if value in 'RC Date' then find delta between it and today
    ' If not, return zero
    Dim intD As Integer
    If Not IsNull(FirstDate) Then
    intD = DateDiff("d", FirstDate, Now())
    ElseIf Not IsNull(SecondDate) Then
    intD = DateDiff("d", SecondDate, Now())
    Else
    intD = 0
    End If

    CalDays = intD
    End Function

    Now when I run the report I get a parameter value box asking for the "Assign Date"

    Would you please make the changes I need to make this work in my example shown here? -- I just keep screwing it up!

  12. #12
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    The function =CalcDays([Assign Date],[RC Sent Date]) is looking for two fields, named Assign Date and RC Sent Date on the report.

    Make sure you're using the correct name: Assigned Date versus Assign Date. I'm sure I probably omitted the ed on the end of Assigned.

    It pops up and asks for a parameter when it can't find a control with a matching name.

    I think this may actually do it for you. Good luck.

  13. #13
    Join Date
    Jan 2004
    Posts
    37

    Talking Calculation in Access Report

    Tcace

    I got It! Thanks to you !

Posting Permissions

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