Increment a number by "1"... once everyday

[MikeWooZ]

I have a database that I import Excel data into. Within the imported data, I have a field that holds a number that indicates how many days a person has been in a program. I don't want this number to increment every time I open the form, but I want it to increment on the next days date change.



In other words, today, the data on the form would reflect "10" and if my supervisor views the report a week from now, I want it to reflect "17"... even though I've opened the form a hundred times.

Is there a fairly easy way to accomplish this task without setting a bunch of queries and sql tables?

Thanks in advance for any and all advice.

[Rainlover]

Simply Substract one Date from the other.

Use the Function Date Diff.

Search Functions for Date.

[MikeWooZ]

That would be easy if I had some date fields to compute off of. All I have is data extracted from a data source that only provides the amount of days, in other words, the source already used the date to calculate the numerical field provided.

As stated, all I have to work with is a number, and I need to be able to increment that number by one.... tomorrow.

Thanks for your feedback.

[Rainlover]

Does this number represent a Date.

Dates are usually just number of Days since 30 Dec 1899.

If this number is really a Date then you could convert Today's Date to a Number and then find the Difference.

The Function to do this is "DateValue". (But just check help to see if I have the correct Value.

Also look here

http://www.techonthenet.com/access/f.../datevalue.php

[MikeWooZ]

From all the responses, I wish it was a date, but unfortunately it is not. It is a calculated number whereas the source has already performed the "total days" calculated value. This almost seems like it should be too easy... just pick a field and 1 to it everyday.

Please keep pumping me your thoughts for I hope one of you will present a really good solution.

Thanks again Rainlover for all your responses thus far.

[hertfordkc]

you could schedule a command line to start your database, execute a macro that increments the day, and closes the database.

[MikeWooZ]

Could you elaborate a bit on how to do this... if not, would you know of a good source for this info?

Thank you

[hertfordkc]

First of all, you can run a scheduler from windows. I can't remember whether you can specify a command line or if you need a batch file, so let's assume you create a batch file that will be started by the scheduler.
Mybatch.bat
C:\Pathname\Mydatabase /x MydateMacro //Google Access Command Line for your options here

This starts Mydatabase and runs MydateMacro

MydateMacro // Google macro actions for explanation of runcode
Runcode MyVBADateFunction()
CloseDatabase

The Runcode assumes that you can write the VBA code needed to increment the days. Perhaps this won't be necessary, i.e. maybe running a query can do the job. I wasn't sure from the posts exactly where the days to be incremented were located.

[Rainlover]

I could make a hundred guess and still get it wrong. So I am not going to do that.

It would help to have a sample of a few lines of your Data.

Also an explanation of how you know what Data to display. There must be something unique in your data source or you would show all the data all the time.

[ChrisO]

MsgBox CLng(Date) – 40868

Edit:
Or more correctly:-
MsgBox CLng(Date) - 40878 + field_that_holds_a_number

It may be safer to get the date from the server to stop people interfering with the result by changing the date on their local machine.

Chris.

[hertfordkc]

whether you opened the database or not.
If you really wanted to count the days on which you opened the database, create RefData Table
LastUpdate
Count

When you open the database, first thing happens is the Lastupdate is checked to see if it is less than today. If it is, make lastupdate = today, and increment the count.
Go on about your business.