Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-10-2014, 01:59 AM   #1
aman
Newly Registered User
 
Join Date: Oct 2008
Posts: 1,169
Thanks: 51
Thanked 3 Times in 2 Posts
aman is an unknown quantity at this point
Creating Report when a button is clicked with appropriate colour of each record.

Hi All

I have access table that stores details as attached. Just to give an example how data is stored , I copied data from Access table to excelsheet. Now I would like to create a report that will run when a button is clicked on the form and the vba code should display the last 7 dates from todays dates in the report along with all the data and colour the record Green,Amber and Red depending upon the Update Date and Target Date. If the Target date exceeds then that records should be coloured "Red" and if todays date equals to Date then the record should be amber and if greater than Date but less than Target Date then it should be Green.

So the report should display the last 7 dates records from todays date and the appropriate colur of each recor to let everyone know if its under target, meets target etc.

Any help would be much appreciated.

Thanks
Attached Files
File Type: xls Data.xls (18.5 KB, 107 views)


Last edited by aman; 12-10-2014 at 02:49 AM.
aman is offline   Reply With Quote
Old 12-10-2014, 02:22 AM   #2
DavidAtWork
Newly Registered User
 
Join Date: Oct 2011
Location: leighton Buzzard, UK
Posts: 699
Thanks: 1
Thanked 166 Times in 164 Posts
DavidAtWork is on a distinguished road
Re: Creating Report when a button is clicked with appropriate colour of each record.

If you go the report design and select a suitable field such as your Target Date, use the conditional formatting (under the Format menu) to set the back colour or text colour. You can create an expression in the condition as criteria to set Red/Amber/Green

David
__________________
Help is only a forum question away
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
DavidAtWork is offline   Reply With Quote
Old 12-10-2014, 02:52 AM   #3
aman
Newly Registered User
 
Join Date: Oct 2008
Posts: 1,169
Thanks: 51
Thanked 3 Times in 2 Posts
aman is an unknown quantity at this point
Re: Creating Report when a button is clicked with appropriate colour of each record.

Thanks David. I have created a report using report wizard which selected all the fields of tblupdate and then made few changes using design view. but how can i specify all the conditions in the report. I have cut and paste the Access report in excelsheet as attached . pls guide me. Thanks
Attached Files
File Type: xls Book1.xls (34.0 KB, 67 views)

aman is offline   Reply With Quote
Old 12-10-2014, 03:37 AM   #4
DavidAtWork
Newly Registered User
 
Join Date: Oct 2011
Location: leighton Buzzard, UK
Posts: 699
Thanks: 1
Thanked 166 Times in 164 Posts
DavidAtWork is on a distinguished road
Re: Creating Report when a button is clicked with appropriate colour of each record.

do you know how to use conditional formatting in Excel? The Access CF works in a similar way.
When you say:
If the Target date exceeds then that records should be coloured "Red" and if todays date equals to Date then the record should be amber and if greater than Date but less than Target Date then it should be Green.

What exactly do you want to colour?
Also I notice you have the date field named as Date and this will cause problems when you're trying to evaluate the expression "if todays date equals to Date"

I suggest you choose an appropriate field to reflect this colour status as I don't think you can conditionally format a section of a report, you have to select a field in the detail section. You will need to set 3 formats to achieve what you want

David
__________________
Help is only a forum question away
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
DavidAtWork is offline   Reply With Quote
Old 12-10-2014, 04:53 AM   #5
aman
Newly Registered User
 
Join Date: Oct 2008
Posts: 1,169
Thanks: 51
Thanked 3 Times in 2 Posts
aman is an unknown quantity at this point
Re: Creating Report when a button is clicked with appropriate colour of each record.

Actually, I wanted to create dashboard to to show all the information discusse above. A dashboard that will show charts/graphs to let us know how many meet target(Green Colour),how many exceed target(Red Colour) Please can anyone help me in this.

Thanks

aman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add record when command button is clicked RenaG Forms 0 01-18-2012 02:21 PM
new record should be saved only when a button clicked Rajashekar_S Forms 4 06-21-2011 06:10 PM
Open Specific record when button is clicked Djblois Forms 6 02-02-2009 01:17 PM
creating a new record button loki Modules & VBA 1 05-10-2001 08:52 AM
why form opens lower down when 'add new record' command button clicked russi Forms 0 02-26-2001 12:27 PM




All times are GMT -8. The time now is 06:57 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World