[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

VBA code for Function that returns numbers

Hello experts ,

i have an access database and a table . In this particular table i store dates as long with other data in its columns . All i want to do is when i insert a record to the table ( which stores records using descending order for the dates field) is to make the following comparisons :

1. The top date should be compared to the current year and will return the result of current_year - top_date (integer)
2. The second record below the top date should be compared to the top_date like : top_date - second_date and i get the result (integer)
3.the third record should be compared to the second like second_date-third_date and i get the result (integer)
4...... etc

This should be done for all records of the table until EOF.

Can you help with this ?
1 Solution
Attach a sample database with few records and the expected.
Explain more on what to do to get the output.
We need to see some examples of the date values you are storing.
stathisxAuthor Commented:
i attach the sample database and instructions what i want to do

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Jeffrey CoachmanCommented:
This is confusing...

Can you first, take a step back and explain what this system's ultimate goal is?

My feeling is that there may be a better interface option...
stathisxAuthor Commented:
ok let me be more clear . the only thing to do is to get a result of a simple calculation in a field (unbound) . in the sample database there is a diff text box. given a query with years sorted with descending order i want to do the following :

1. the first row of the query (i name it top year) should be compared to the current date. the comparison is : me.diff.value= datepart(date(),"yyyy") - top year value

2. the second row of the query should be compared to the first. the comparison is : me.diff.value=top year - second year value

3. the third row of the query should be compared to the second. the comparison is ;
me.diff.value=second year value - third year value

4. tha same until EOF (no more records)

is it possible ? i can use dao to move to the next record but how can i assign variable to textbox using dao ?
Jeffrey CoachmanCommented:
Still confused...
The recordsource for the form is basically the same as the query. (only the query is sorted by year, descending)
Anytime you think you need a recordset to loop through the same records as are in the form, ....something seems a bit awry...

So, instead of "Telling" us how you want this issue solved (Recordsets, Loops, Comparisons, ...etc)
Why not just tell us what each "Diff" should be for each of the 5 records in the form?  Then we can perhaps determine what the best method might be.

Remember, I asked for the "Ultimate goal" of this system.
You simply explained "How" you want us to solve the issue...

In other words, I wanted you to say something like:
    "The Diff textbox should display the difference in years between the current year and the year_in  value..."
    "The Diff textbox should always be the difference between the Highest year and the Year_In field"

My guess is that a simple formula or function will be able to get you what you want
Here is an example (sample attached)
1. It basically compares the "Year_In" with the current year, and determines whether it is the current year, a past year or a future year.
2. It calculates the diff between the Highest Year and the current year

So you can see here, that you can adapt these same techniques to display pretty much whatever you want.

(Other notes: It is not clear why tblDates has two primary keys)


Jeffrey CoachmanCommented:
sorry, her is the file
stathisxAuthor Commented:
in fact i want the textbox diffrommax to act like difffromprevious .(i call it *previous cause it will be compared to the previous record available in the table )
example :
the data in the query are sorted by descending year order so if i insert for example a year e.g 2004
year_in =2004
table dates -> 2009 ,2007 ,2001 (already there)

What i want : after inserting the record -> difffromprevious = 3 (2007-2004)
if i enter 1990 : diffreomprevious = 2001-1990 = 11

I hope you understand my point.
Jeffrey CoachmanCommented:
...but the form is sorted by the date in the other direction...?
stathisxAuthor Commented:
the dates ( year_in )will be sorted in descending order .
stathisxAuthor Commented:
i followed the solution provided here :

In this sample database i found what i was looking for. I modified the query to fit my needs and this solved the problem .
Thank you all for the guidance.
stathisxAuthor Commented:
This sample database did the trick. thank you !

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now