# Thread: Calculation in Access Report

1. Registered User
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. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Jan 2004
Posts
37

## Calculation in Access Report

I tried that and ran into another problem...see attach screen shot

6. Registered User
Join Date
Nov 2002
Posts
154

## 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. Registered User
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. Registered User
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.

9. Registered User
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.

10. Registered User
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]

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. Registered User
Join Date
Jan 2004
Posts
37

## 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. Registered User
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. Registered User
Join Date
Jan 2004
Posts
37

## 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
•