Hi,
hope someone could help.
On a form, I have three date fields Date1, Date2 and Date3 and format of all three fields is shortdate. I want field Date3 to be auto-poluate by this formula (Date1 - Date2) +1 after user enter Date1 and Date2. I built this VAB code on the AfterEvent of Date2 field. -
Private Sub Date2_AfterUpdate()
-
If Not IsNull(Me![Date2]) And IsDate(Me![Date2]) And Not IsNull(Me![Date1]) And IsDate(Me![Date1]) Then
-
Me.Date3 = (([Date2]-[Date1])+1)
-
End If
-
End Sub
-
I got the result of a date but not the correct date as 12/29/1899. any idea why?
thanks
777bonzai
6 1268
First and foremost, you should avoid storing calculated values. You can just run the calculation in real time when needed using an expression or a view.
Also, I don't know what you're expecting from your calculation because you haven't told us what the correct result is supposed to be.
But here's the million dollar question, why do you expect the subtraction of 2 dates to result in another date?
If you subtract Christmas Day from New Year's Day, why do you expect a date to come out of that? And what date do you expect to come out? Logically speaking, I expect a time interval to come out, not a date. If I subtract 2 dates, I expect the number of days that separate the two dates as a result.
Rabbit. Thanks for your response. Basically, for example, if user enter Date1 as 01/05/2011 and Date2 as 01/02/2011, Date3 should be auto populated as a date of 01/04/2011
thanks
777bonzai.
And why's that? The result makes no sense. If I subtract 01/05/2011 and 01/02/2011, the answer I expect is 3 days. Not a date.
You need to explain in more detail how you're making that leap of logic. What happens if you subtract 07/30/2011 and 01/02/2011?
Right now, you're doing math that only makes sense in your head, you need to explain your version of math.
zmbd 5,501
Expert Mod 4TB
777bonzai,
+ Are the controls [Date1] and [Date2] "unbound", if not, then I concur with Rabbit's comment about storing calculated values. There are only a few, rare, exceptions to this best practice.
+ Why 1/4/2011 instead of 1/3/2011? Say you had day1=1/1/2015 and day2=12/30/2015 would you want the return of 12/29/2015?
+ You also have no check for Day1>Day2 so would you desire that the in the case of day1=12/30/2015 and day2=1/1/2015 return some value (say 01/01/2015 or 01/02/2015, or 12/31/2014) or instead return #01/01/1900#?
NeoPa 32,556
Expert Mod 16PB Rabbit:
And why's that? The result makes no sense. If I subtract 01/05/2011 and 01/02/2011, the answer I expect is 3 days. Not a date.
I agree absolutely. Unless and until you can explain why you think it makes sense to try to express a date interval as a date, the question is impossible to answer. Sometimes people guess at what the question might mean if the person asking it had expressed it properly, but that's a really bad idea, even when they're right.
If you can rephrase the question for us, such that it makes sense, then you have the ear of three very experienced Access developers here waiting to help you.
I was able to figure it out. Thanks everyone!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ross Lewis |
last post by:
Is it possible to autofill a specific field in and Internet Explorer form.
I would like to install a program on a client's computer that will
automatically fill in a password field with their mac...
|
by: Tony |
last post by:
Hi,
I have an ID text field that is composed of two alphabetical letters
and the rest are numbers. This field is also a primary key. In this
same table, I want to separate this field into two...
|
by: helpless |
last post by:
Access 2003 - Is there any way to copy/drag data from one field to multiple fields in the same column?
|
by: Simon Gare |
last post by:
Hi,
have form on asp page with an email address field, when the user enters
their email address I want it to populate another field automatically on the
same page, some kind of OnBlur event....
|
by: MindBender77 |
last post by:
Hello Again All,
I have a memo field that is backslash delimited. I am attempting to break up this field into individual fields.
Ex (memo field). \John L. Doe\\StreetName RD\\City, Pa 11111
...
|
by: learnaccess3 |
last post by:
I am using a simple query (i.e.) select query taking soem fields , & binding with the controlsource property of the textbox. But I am unabel to edit the same same .Erroe Message when try to enter in...
|
by: deanvilar |
last post by:
Gurus,
how to query Select Distinct field + showing other fields?
e.g.:
SELECT DISTINCT assetMovementTable.assetNo, assetMovementTable.moveCode, assetMovementTable.compCode,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |