Need help using Date Diff ( for the 1st time!)

[ian123]

I have 3 columns "date in" "Date Out" & "Days kept". How do i get access to
put the answer in days kept. I have tried looking over the web, it shows the
syntax i should be using but i can't seem to get it to work. Maybe I am
entering the syntax in the wrong place - design view/field properties/default
value

 

[Al Campagna]

ian123,
A control's Default value gets updated immediately upon opening a New
record.
Since DateIn and DateOut are null at that time, the Default value can
not be calculated.
Remove that Default Value

DaysKept should be an unbound calculated text control, with a
ControlSource of...
= DateDiff("d", [Date In], [Date Out])
will calculate the number of days between those two date values. (once they
have been entered)

No need to save that calculated value. Since you have captured Date In
and Date Out, you can always recalculate Days Kept "on the fly" in any
subsequent query, form, or report.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Al Campagna]

ian123,
A control's Default value gets updated immediately upon opening a New
record.
Since DateIn and DateOut are null at that time, the Default value can
not be calculated.
Remove that Default Value

DaysKept should be an unbound calculated text control, with a
ControlSource of...
= DateDiff("d", [Date In], [Date Out])
will calculate the number of days between those two date values. (once they
have been entered)

No need to save that calculated value. Since you have captured Date In
and Date Out, you can always recalculate Days Kept "on the fly" in any
subsequent query, form, or report.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[John W. Vinson]

I have 3 columns "date in" "Date Out" & "Days kept". How do i get access to
put the answer in days kept. I have tried looking over the web, it shows the
syntax i should be using but i can't seem to get it to work. Maybe I am
entering the syntax in the wrong place - design view/field properties/default
value

How do you get the difference between Date In and Date Out into Days Kept?

You Don't.

In fact Days Kept should *SIMPLY NOT EXIST* in your table.

Storing derived data such as this in your table accomplishes three things: it
wastes disk space; it wastes time (almost any calculation will be MUCH faster
than a disk fetch); and most importantly, it risks data corruption. If one of
the underlying fields is subsequently edited, you will have data in your table
WHICH IS WRONG, and no automatic way to detect that fact.

Just redo the calculation whenever you need it, either as a calculated field
in a Query or in the control source of a Form or a Report textbox.

 

[John W. Vinson]

I have 3 columns "date in" "Date Out" & "Days kept". How do i get access to
put the answer in days kept. I have tried looking over the web, it shows the
syntax i should be using but i can't seem to get it to work. Maybe I am
entering the syntax in the wrong place - design view/field properties/default
value

How do you get the difference between Date In and Date Out into Days Kept?

You Don't.

In fact Days Kept should *SIMPLY NOT EXIST* in your table.

Storing derived data such as this in your table accomplishes three things: it
wastes disk space; it wastes time (almost any calculation will be MUCH faster
than a disk fetch); and most importantly, it risks data corruption. If one of
the underlying fields is subsequently edited, you will have data in your table
WHICH IS WRONG, and no automatic way to detect that fact.

Just redo the calculation whenever you need it, either as a calculated field
in a Query or in the control source of a Form or a Report textbox.