Hi,
I am working on a vba app based in ms excel. I write and query data from an ms access db via forms in ms excel.
I am now trying to dynamically populate a OWC Graph based on queried data. For me to achieve the type of graph i want, i need my data to be cumulated in a way that not only does a query show data for when it was last updated but also for when it was not.
For example if data between 01/01/08 and 10/01/08 was only updated once on the 05/01/08, therefore my query should look like this:
Date Value
01/01/08 1 (Stored in Table) (original Value)
02/01/08 1 (Not in table)
03/01/08 1 (Not in table)
04/01/08 1 (Not in table)
05/01/08 2 (Stored in Table) (Updated by user)
06/01/08 2 (Not in table)
07/01/08 2 (Not in table)
08/01/08 2 (Not in table)
09/01/08 2 (Not in table)
10/01/08 2 (Not in table)
Now i understand that it is hard to get a query to do the above based on start and end dates and hence i am having to write to a temporary table for graphing purposes.
I am now stuck where i need to calculate the number of days between the new update and last update in order to enter all missing records. It even gets more complicated if the date range falls between two months hence i would have to know if the original month had 30 days or 31 days?
Please assist me in determing how i can increment the dates and insert into the table based on start and end dates plus data retrieved from query so that i can get a complete set of data which can then be used to plot a graph.
Many thanks,
Nav
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi Rob,
Thats exactly what i am trying to do.
I have just figured out how to increment dates using a simple for loop using start and end dates.
In principle i can now insert records in the table for all the missing dates.
I would like to see how you present this and may be it might make my solution better.
regards,
Nav
Hi Nav,
Without seeing your code I can only offer a basic version of the function you seem to be looking for however i'm sure you can tweak it to do what you need with the variables.
Instead of using a For loop i've used a Do Until loop which pretty much does the same thing. My code simply gets two dates from an Excel sheet (you could get these from your database table or anywhere else you have htem stored) and works out the number of days in between then lists all those days on the worksheet but you could write them anywhere you need them.
Let me know if you have any questions.
Cheers,
Rob
Business Accounts
Answer for Membership
by: RobPalPosted on 2008-09-17 at 07:15:49ID: 22498882
Hi there,
I think I can help you but I need some clarification on what you're trying to do.
I'm assuming the following:-
1. You have a table with only the dates when an update occurred.
2. You want an output to combine the information from this table and the missing dates where nothing occurred.
Please confirm that this is the case or correct me where I have misunderstood.
Thanks
Rob