Decision on calculated field in Report (1 Viewer)

[wasim_sono]

Dear All

I have a program about "complaints". It has a table-field "comp_status" which holds the data either "Pending" or " Resolved" and comp_recvd_date and comp_resolved_date. I have also a report for the program.

In this report I used a calculated field "Aging" which calculate the aging of the complain by difference of today and complain date.

I need to calculate aging of complain by following"

if comp_status = " pending then 
Aging = now() - comp_recvd_date
elseif comp_status = "Resolved" then
Aging = comp_resolved_date - comp_recvd_date

Is it possible to use this code on opening of the report or any other procedure is required?

 

[The_Doc_Man]

There are several places this could be computed, but we cannot answer the question without knowing things like how many group headers are involved and whether this is in a detail section or a header section that shows the value of Aging.

To answer the direct question, it is possible to do this when opening the report, but if and only if the same aging value applies everywhere in the report. From the discussion, I'm not willing to give a definitive yes or no.

 

[Cronk]

Is Aging a variable or the name of the text box on your report?

Anyway, use the OnFormat event of the section of the report where the information is displayed.

 

[wasim_sono]

Thanks a lot Cronk and The_Doc_Man for your early replies.

Aging is name of text box in detail section. I have two group headers in this report.

Can you please tell the exact code to use in OnFormat event.

 

[Cronk]

if me.comp_status = "pending" then      
    me.Aging = now() - me.comp_recvd_date 
elseif me.comp_status = "Resolved" then    
    me.Aging = me.comp_resolved_date - me.comp_recvd_date
else
     me.aging ="????"
endif

 

[wasim_sono]

I used following code:

If Me.Comp_Status = "pending" Then
    Me.Range = DateDiff("d", Me.Comp_Date, Now())
ElseIf Me.Comp_Status = "Resolved" Then
    Me.Range = DateDiff("d", Me.Comp_Date, Me.Resolve_Date)
End If

Aging is replaced by Range. When I run the report it gives run time error as attached.

 

[Mark_]

Dear All

I have a program about "complaints". It has a table-field "comp_status" which holds the data either "Pending" or " Resolved" and comp_recvd_date and comp_resolved_date. I have also a report for the program.

In this report I used a calculated field "Aging" which calculate the aging of the complain by difference of today and complain date.

I need to calculate aging of complain by following"

if comp_status = " pending then 
Aging = now() - comp_recvd_date
elseif comp_status = "Resolved" then
Aging = comp_resolved_date - comp_recvd_date

Is it possible to use this code on opening of the report or any other procedure is required?

On behalf of the department of redundancy department, I do need to ask, if you have "Comp_Resolved_date" and only two statuses, why do you need a status? If Comp_Resolved_date will tell you if it is pending or resolved. If it has a value, it is resolved, else it isn't.

This does mean that your "Aging" calculation becomes simply

IF nz(Me.Comp_Resolved_Date,0) > 0 then
   Me.Aging = DateDiff('d', Me.Comp_Resolved_Date, Me.Comp_Recvd_date)   
Else
   Me.Aging = DateDiff('d', Date(), Me.Comp_Recvd_date)
End If

I'd use that so I could also fill in a "status" if needed.

If you are planning to extend your statuses this would make sense, unless each status also has its own associated date (such as Comp_Assgn_date) or the such, but then you'll be better served with a child table to track when status changes occur.

 

[wasim_sono]

Actually "Aging" field tells the status that how long was it "pending" or what time it took to "Resolved".

 

[Mark_]

But status is still not needed if you can derive it from Comp_Resolved_Date.

 

[wasim_sono]

yes of course, but how to print on report?

please see attached report.

 

[Cronk]

What happens if you click on the Print button (shown on your jpeg on the left hand side towards the top)?

 

[Pat Hartman]

Now() = current date + time of day
Date() = current date

You NEVER want to use Now() in this situation. You are adding a time component to your date for no reason and having time could lead to later problems with comparisons.

Now() is used when you want to record the date and time something happened such as LastChangedDT for a record or clocking IN or OUT.

Date() is used in all other cases.

 

[Mark_]

yes of course, but how to print on report?

please see attached report.

IF nz(Me.Comp_Resolved_Date,0) > 0 then
   Me.Aging = DateDiff('d', Me.Comp_Resolved_Date, Me.Comp_Recvd_date)   
   Me.TxtStatus = "Resolved"
Else
   Me.Aging = DateDiff('d', Date(), Me.Comp_Recvd_date)
   Me.TxtStatus = "Pending"
End If

Unless you are planning for more values for "status", you have effectively saved a calculated value as you can calculated it based on the presence of absence of Comp_Resolved_Date. If you have already noticed this, you would also not be allowing a user to actually enter "status" but simply default the value at record creation and update when a resolved date is entered.

 

[wasim_sono]

Thanks a lot Pat Hartman for your valuable guidance. but when I used "Date()" in my code it never accepted and automatically converted to "Date" only. Now I'll use the same in my coding.

Hello Mark

I really appreciate your code but when I used this in my program I got following run-time error. (see attached file). I run "Compact & Repair Database" option but still found error.

my coding is as under:

If Nz(Me.Comp_Resolved_Date, 0) > 0 Then
    Me.Range = DateDiff("d", Me.Comp_Date, Me.Comp_Resolved_Date)
    Me.Comp_Status = "Resolved"
Else
    Me.Range = DateDiff("d", Date, Me.Comp_Date)
    Me.Comp_Status = "Pending"
End If

 

[JHB]

Post your database with some sample data.

 

[wasim_sono]

Re: [RESOLVE] Decision on calculated field in Report

Thanks all of you for your valuable help.

I was using Aging field as bound field. I just unbound it got result.

 

[wasim_sono]

Re: [RESOLVED]Decision on calculated field in Report

Thanks for every one for your valuable help.

It resolved. The error was due to using "Aging" field as bound field. I just unbound it and got result.

 

[Pat Hartman]

Standard convention for functions is to use () at the end regardless of whether or not the function takes an argument. Some IDIOT programmer at MS decided to deviate from this for the Date function and so in VBA, the () are automatically dropped but you will need them in all other cases which is why I always include them.

Date() is converted to Date automatically in VBA.