473,320 Members | 1,868 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,320 software developers and data experts.

Set a limit to dateDiff

22
I created a query with calculated field months:
Expand|Select|Wrap|Line Numbers
  1. DateDiff("m", 
  2.     [dateacquired], 
  3.     [Forms]![form2]![txtendingdate])+1
I also have a field lifeTime, what i want to do is to set a limit to months fields so that it will not be greater than lifeTime
Jun 21 '18 #1

✓ answered by Nauticalgent

Try using this as the fields control source:

Expand|Select|Wrap|Line Numbers
  1. =IIf(
  2.     DateDiff("m", 
  3.         [dateacquired], 
  4.         [Forms]![form2]![txtendingdate]) + 1 <= [lifetime], 
  5.     DateDiff("m", 
  6.         [dateacquired], 
  7.         [Forms]![form2]![txtendingdate]) + 1, 
  8.     [lifetime])
Sloppy as hell, but it should work...

6 1171
Nauticalgent
100 64KB
Try using this as the fields control source:

Expand|Select|Wrap|Line Numbers
  1. =IIf(
  2.     DateDiff("m", 
  3.         [dateacquired], 
  4.         [Forms]![form2]![txtendingdate]) + 1 <= [lifetime], 
  5.     DateDiff("m", 
  6.         [dateacquired], 
  7.         [Forms]![form2]![txtendingdate]) + 1, 
  8.     [lifetime])
Sloppy as hell, but it should work...
Jun 21 '18 #2
Mafi
22
Wow! It works perfectly thanks. What can i do to return negetive months to zero. Eg when i view previews years that is b4 dateacquired it gave me negetive months
Jun 21 '18 #3
Nauticalgent
100 64KB
Glad it worked. To set negative numbers to zero, you could nest the IIF statement. I have shown you a quick example, why not give it a try and if you cannot make it work, post back here with what you have tried so we can show you where you may be getting it wrong.

Not that I mind helping out, but I like to be taught to fish rather than having my fish given to me.

Deal?
Jun 21 '18 #4
Nauticalgent
100 64KB
This may give you some ideas:

http://www.simply-access.com/Nested_IIF_Statement.html
Jun 21 '18 #5
Mafi
22
I have gotten it. Thanks alot
Jun 21 '18 #6
Nauticalgent
100 64KB
Good deal. Care to share your solution so that others may learn from your blood, sweat and tears?
Jun 21 '18 #7

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

Similar topics

4
by: CJM | last post by:
I have an ASP page that lists files and folders in a directory. I'm using a cookie to record the last time this page was visited, and I intend to show links that are created/modified from that date...
8
by: inamori | last post by:
I face that problems 07/01/2003 06/30/2006 ---------> it should be 3 01/01/2003 02/28/2005 --------->could i get 2 years and 2 months 01/01/2003 03/01/2005 ...
6
by: Lofty | last post by:
Hi all. I have to write an app that interacts with mySQL (I really must have done some evil, evil stuff in a previous life to be landed with this!) I need to work out the difference in days...
1
by: intl04 | last post by:
I'm trying to set up a query that will include a new field ('Days until completion') whose value is derived from the DateDiff function. I think I have the syntax correct but am not sure. Days...
4
by: Paolo | last post by:
I am having some problem with a Year Function. I have form on which I have 4 field which indicate dates and an additional form which sums those dates: These are the fields: YEARS...
1
by: PMBragg | last post by:
ORINGINAL Post >Thank you in advance. I'm trying to pull all inventory items from December >of the previous year back to 4 years for my accountant. I know this can be >done, but I'm drawing a...
7
by: Adrian | last post by:
I hit on this problem converting a VB.NET insurance application to C#. Age next birthday calculated from date of birth is often needed in insurance premium calculations. Originally done using...
5
by: sr | last post by:
Anyone know of a better way to simulate a datediff for C#, i.e., without referencing the VB.NET runtime? Only added the functionality that was needed for me so it is not the full implementation...
6
by: kevinjwilson | last post by:
I am trying to get the date difference between two dates but I don't want the function to include weekends in the calculation. Does anyone have an idea on how to make this work?
2
by: muddasirmunir | last post by:
i am using vb 6 , i had place two datepicker in form now i want to calcuate differcen of month in two date for this i used the function datediff i had try it withh many syntax but getting error...
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
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)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.