DateDiff()

[AnhCVL via AccessMonster.com]

Hi all,

I have a form consist of 3 text fied (StartDate, RecDate, and Ddays) which
bound to a data table (Dtable) for loging purpose. I want to be able to
calculate the days different between the StartDate and the RecDate and
display it on the Ddays field ( which is normally locked, manyallu entry is
not permitted). I want the form to do the calculation upon the date is
entered on the second text box ( RecDate) and displays the different on the
3rd textbox (Ddays) automatically. If the RecDate is earlier than the
StartDate, the date different will be negative value number of days (such as -
10, or -8), if the RecDate is later than the StartDate, the outcome will be
possitive value( such as 7, or 10). I was able to do it in excel by just
substract the cell ( I.E $A2-$A1, where "cell A2" acts as RecDate and "cell
A1" acts as StartDate), but failed to do so in the access form. I've tried
the DateDIff() by place the code under RecDate as below:

Private Sub RecDate_LostFocus()
Dim TDdays, StartDate, RecDate As Date
Date_Due.SetFocus
If StartDate.Text = "" Then
MsgBox "Invalid Start Date detected", vbwarning, "Input Required"
StartDate.SetFocus
ElseIf RecDate.Text = "" Then
MsgBox "Invalid Received Date detected", vbwarning, "Input
Required"
RecDate.SetFocus
Else
Ddays.Text = DateDiff("d", RecDate, StartDate)
End If
End Sub


Any suggestion?

Thanks
Mark

 

[Allen Browne]

1. Remove the Ddays field from your table.

2. Create a query, and type this expression into the Field row:
Ddays: DateDiff("d", [StartDate], [RecDate])
Save the query. Close.

3. Open your form in design view, and change its RecordSource property to
the query instead of the table.

Your form now has a Ddays that is automatically read-only (since it's
calculated), and can't go wrong.

Anything that would be a formula in Excel is handled as a calculated query
field in Access. More info in:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

 

[Josh]

It seems to me that your variable names are the same as the textbox names.
For instance, you have a variable named StartDate and a textbox named
StartDate. This is probably the source of your error.

You need to follow VB naming conventions to make all object names
diffferent. In your case, your textbox names should start with "txt", ie
"txtStartDate". Date variables should start with "dat", ie "datStartDate".

 

[AnhCVL via AccessMonster.com]

Hi Allen & Josh:

Thanks for the replies.
from Allen's suggestion, I've created the calculation query to perform the
calculation using DateDiff() function and set the form's source to the query
but I want to save it on the record table as the insertion point move to
another field, that's why I place such code under the field's properties as
it lost fofuc, the record can be writen on the table. since it perform ny
query, sort of confused in the attempt to save the data onto the table. Also,
is it possible to force the field to contain negative data ( such as -10, -20
or so) as I specified on my initial message? the DateDiff() only give
positive integer value, but I want the days to be negative ( I.E -10) if the
RecDate is earlier than StartDate? it's kinda like this:

StartDate : constant
RecDate: can be any date before or after StartDate

Ddays = [RecDate] - [StartDate]

is my explaination clear and is it possible? Thank you!

Mark

It seems to me that your variable names are the same as the textbox names.
For instance, you have a variable named StartDate and a textbox named
StartDate. This is probably the source of your error.

You need to follow VB naming conventions to make all object names
diffferent. In your case, your textbox names should start with "txt", ie
"txtStartDate". Date variables should start with "dat", ie "datStartDate".

Hi all,

[quoted text clipped - 31 lines]

Thanks
Mark

 

[John W. Vinson]

DateDiff() only give positive integer value,

Huh? You're mistaken:

?DateDiff("d", #1/1/2009#, #1/1/2008#)
-366

 

[Allen Browne]

The article on Calculated Fields at:
http://allenbrowne.com/casu-14.html
explained that saving this into a table is not the right design. You can use
a query anywhere you an use the table, and the query can't go wrong.
Technically, storing dependent data is non-normalized.

It also explains how to use the AfterUpdate event of the text box if you
really want to go that way.

John Vinson has explained the issue with negatives.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen & Josh:

Thanks for the replies.
from Allen's suggestion, I've created the calculation query to perform the
calculation using DateDiff() function and set the form's source to the
query
but I want to save it on the record table as the insertion point move to
another field, that's why I place such code under the field's properties
as
it lost fofuc, the record can be writen on the table. since it perform ny
query, sort of confused in the attempt to save the data onto the table.
Also,
is it possible to force the field to contain negative data ( such
as -10, -20
or so) as I specified on my initial message? the DateDiff() only give
positive integer value, but I want the days to be negative ( I.E -10) if
the
RecDate is earlier than StartDate? it's kinda like this:

StartDate : constant
RecDate: can be any date before or after StartDate

Ddays = [RecDate] - [StartDate]

is my explaination clear and is it possible? Thank you!

Mark

It seems to me that your variable names are the same as the textbox names.
For instance, you have a variable named StartDate and a textbox named
StartDate. This is probably the source of your error.

You need to follow VB naming conventions to make all object names
diffferent. In your case, your textbox names should start with "txt", ie
"txtStartDate". Date variables should start with "dat", ie
"datStartDate".

Hi all,

[quoted text clipped - 31 lines]

Thanks
Mark

 

[AnhCVL via AccessMonster.com]

Thanks Guys

I've tried the suggestions from the website of Allen, I was able to get it
displayed but kinda running into issues in the attempts of saving the data on
the table. I place the code under "AfterUpdate" event instead of "LostFocus"
event using calcualting query. I will continue keep trying base on Allen's
suggestions. Thanks again!

Mark

The article on Calculated Fields at:
http://allenbrowne.com/casu-14.html
explained that saving this into a table is not the right design. You can use
a query anywhere you an use the table, and the query can't go wrong.
Technically, storing dependent data is non-normalized.

It also explains how to use the AfterUpdate event of the text box if you
really want to go that way.

John Vinson has explained the issue with negatives.

Hi Allen & Josh:

[quoted text clipped - 38 lines]