calculated field on a report

Mi

Board Regular
Joined
Sep 19, 2004
Messages
77
Hello,
I have a report that is opens with via a filter form. User selects week date and unit. Report then shows the number of accounts. What I would like to do is have next to the account field the number of times that account has been on the listings. Listings are pulled weekly. Thus if on 10/17, acount "alpha" is on the list and it has been on the previous 5 weeks, to show 5 next to it in another field. This is the last step needed for this project to be completed. Any help would be appreciated.

Mi
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
A report is not based on a form. Its record source is either a table or query; likewise a form relates to a table or query. I'm guessing that it's a query. So you want to modify that query to also grab a field with the number-of-times-on-listing value, which you should save in some distinct table.

Now as to the value, which is how many times an account has been on the listings, I don't think there's an easy solution. What comes to mind is to have Visual Basic code under a button, that increments the table keeping those account counts. It would run an update query. This would wrap around an aggregate (group by) subquery that produces a unique list of accounts.

Is this already too far over the top for you or do you want more details?
 
Upvote 0
I sort of having it working now. It totals the number times the account number is listed. that works but I want it to only count the number of times listed prior to a certain date ( the selected filtered date). Just need to figure that part out.

Mi
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top