DateDiff function

[Lauren]

Let me extend my previous question......

I have two date fields that will contain all different
dates, not only in the fields, but each record will have
different dates as well.
I want to do an update query using the DateDiff function
that will calculate the difference between the dates in
the two fields and place them in a number of days field
after each new record is entered.
How can I make this work?

Thanks again in advance!!

 

[Cheryl Fischer]

Lauren,

It is helpful to stay in the same thread to ask follow up questions ...

Presuming you are entering new records via a form, add your NumberOfDays
field to the form as a bound text box. You can make it visible or
invisible, depending on whether you want users to see the # of Days or not.

Then, in the form's BeforeInsert event, add the following code:

Me!NumberOfDays = DateDiff("d", Me!StartDate, Me!EndDate)


hth,

 

[Victor Delgadillo]

the function DateDiff(interval, [FirstDate], [SecondDate]) will return the
number of intervals between the first and the second date. If interval =
"d", the result will be in days. See the VBA help on DateDiff for all the
allowed interval values. [FirstDate] and [SecondDate] should be variables
containing date-type values or if they are strings, encase them in '#'
signs.
Also, don't save the resulting in a field. If this is a calculated field
(meaning both dates are part of the record), it's not necessary to save the
result inside the table. The formula is always available and there is no
need to update whenever there are changes in the data.
For the query, you may write the formula on the top box (instead of the
field), write a name for the column, add a colon, then the formula, more or
less like this:
DaysBetweenExamsateDiff("d",[FirstDate], [SecondDate])
The resulting column should be an integer if neither date is null.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_

 

[Gary Miller]

Lauren,

Did not see your previous question, but here is the DateDiff
that you need....

DateDiff("d",[FirstDateField],[SecondDateField])

or

DateDiff("d",Me![FirstDateField],Me![SecondDateField])

....depending on where you are calling it from.


Good database design says that you should not be 'storing'
this data as it is really a calculation that can be
calculated any time. Instead of actually placing it in the
table you can create a calculated field in a query, form or
report to display it. This would be the syntax for creating
a new field in a query...

Days: DateDiff("d",[FirstDateField],[SecondDateField])

Gary Miller
Sisters, OR

----- Original Message -----
From: "Lauren" <[email protected]>
Newsgroups: microsoft.public.access
Sent: Friday, October 31, 2003 11:41 AM
Subject: DateDiff function

 

[Cheryl Fischer]

I agree with your and Victor's comments regarding saving the calculated data
to a field. I should have mentioned that!!

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Lauren,

Did not see your previous question, but here is the DateDiff
that you need....

DateDiff("d",[FirstDateField],[SecondDateField])

or

DateDiff("d",Me![FirstDateField],Me![SecondDateField])

...depending on where you are calling it from.


Good database design says that you should not be 'storing'
this data as it is really a calculation that can be
calculated any time. Instead of actually placing it in the
table you can create a calculated field in a query, form or
report to display it. This would be the syntax for creating
a new field in a query...

Days: DateDiff("d",[FirstDateField],[SecondDateField])

Gary Miller
Sisters, OR

----- Original Message -----
From: "Lauren" <[email protected]>
Newsgroups: microsoft.public.access
Sent: Friday, October 31, 2003 11:41 AM
Subject: DateDiff function

Let me extend my previous question......

I have two date fields that will contain all different
dates, not only in the fields, but each record will have
different dates as well.
I want to do an update query using the DateDiff function
that will calculate the difference between the dates in
the two fields and place them in a number of days field
after each new record is entered.
How can I make this work?

Thanks again in advance!!

 

[Gary Miller]

Did you get the info you needed?

Gary