473,327 Members | 2,055 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

get diff in access 2007 between two dates

Please can you help me, I have two dates e.x:2008_11 and 1999_02 and i want to get the difference between these two dates in months or in years, please can you help me solve this?
Thank you!
Nov 8 '10 #1
2 2302
Stewart Ross
2,545 Expert Mod 2GB
Ahh, but these are not dates; if they were, you could have used the built-in DateDiff function to compute the difference for you.

The values you show are text strings, and you can manipulate these using the Left and Right functions to extract the year and month components accordingly, and the Val function to take the numeric value of these text substrings.

The year and the month can be recovered from the string like this:

Expand|Select|Wrap|Line Numbers
  1. Val(Left([name of the field concerned], 4)) 
  2. Val(Right([name of the field concerned], 2))
Once you've got these you can manipulate the result to find the difference between two such values. I leave it for you to work out how to do this once you've extracted the year and month components for each 'date' concerned.

-Stewart
Nov 8 '10 #2
Thank you Stewart, it works but only for years, because for months it gives me the difference between months like they were in the same year.
Anyway it was very helpful and i used only year_diff.
Thanks
Nov 10 '10 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

27
by: Wayne | last post by:
I've been clicking around Access 2007 Beta 2 and can't see the custom menu bar designer. Is it in the beta? Maybe I'm blind. The question that comes to mind is: Will custom menu bars be the same...
1
prn
by: prn | last post by:
Hi folks, I'm relatively new to Access, but I seem to have drawn the short straw, so I have the assignment for my workplace of looking for problems/inconsistencies in migrating applications to...
2
by: Brad Williams | last post by:
Greetings, I have a Access 2007 App and I want to have a Config File. I have several unbound Text Boxes that are in small Date format that will be used as a Filter for several forms. I would like...
17
by: Neil | last post by:
A client of mine likes some of the new bells and whistles in Access 2007, and is thinking about converting our A03 format MDB to an A07 format file. However, while some of the users have A07, many...
1
by: mvlt | last post by:
I recently made the switch from Access 2003 to Access 2007. The rest of our office is still operating on 2003, so the database is still a 2003 file. We're having trouble with a few forms that work...
12
by: pballou | last post by:
The is a simple example of code that worked in access 2003 but does not in access 2007. I have checked it with the debugger and the reason is the value of formField1 does not change in ver. 2007...
2
by: Mimi | last post by:
In Access 2007, I have successfully imported a text file with fixed width fields. However the import did not correctly interpret a date. In the text file the date field had no delimiter, was just...
3
by: sadiyakhan | last post by:
DATES £ CASH £ CREDIT INBOUND OUTBOUND TOTALS 01/02/2010 £20.98 £20.00 £20.00 £10.00 ------ 02/02/2010 £20.00 £20.00 £303.00 £30.00 ------ Hi, ...
2
by: Lina Arraiz | last post by:
First of all, my apologies for originally posting this question in the wrong section. I'm trying to export an Excel 2003 spreadsheet to Access 2007. Column D (START) has the start time and Column E...
2
by: HSXWillH | last post by:
I'm working in Access 2007 and coming to something that's always caused me problems. I have a form titled frmUsers. There are 3 subforms that list exercise dates and exercise types for each user. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.