Counting days using DateDiff

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to use the DateDiff function and have read as much as I can in
this newsgroup about that function and how to calculate the interval between
two dates. However, as a novice Access user, I’m totally confused and lost.

Here is what I have done:
I have two dates I’ve created in the design view of a table called: DDForm
2648
The first date has the Field name: ExpectedSeparationDate
The second date has the field name: DateChecklistPrepared
The Data Type for each is: Date/Time
The format for each is: dd/mm/yyyy
Both of these dates are on the form I created

Here is what I want to do:
Have a field on my form that calculates and displays the number of days
between the two dates shown above.

Here is what I’ve done to try to accomplish that:
In the table view for the table DDForm2648, I added a row and made the Field
Name: DateDiff
For the Data Type, I selected: number
Below in the field properties, under the General Tab, I clicked in the space
“Default value†and a botton with three dots appeared.
I clicked on the button with three dots and the Expression Builder box
appeared.
I clicked on the + to the left of Functions and then on the file folder icon
to the left of Built-In Functions.
I selected Date/Time.
I selected DateDiff.
I clicked Paste.
I changed the function in the window above to: DateDiff
(“dâ€,[ExpectedSeparationDate],[DateChecklistPrepared])
I clicked OK.
I clicked the save icon at the top on the menu bar.
I received the following error message: The database engine does not
recognize either the field ‘ExpectedSeparationDate’ in a validation
expression, or the default value in the table ‘DDForm2648’.

I’m a total novice and know nothing about programming. I didn’t think it
would be this difficult but I’d really appreciate it if someone can give me a
simple step by simple step on how to accomplish this.
Thanks!
 
SKaplan said:
I am trying to use the DateDiff function and have read as much as I can in
this newsgroup about that function and how to calculate the interval between
two dates. However, as a novice Access user, I'm totally confused and lost.

Here is what I have done:
I have two dates I've created in the design view of a table called: DDForm
2648
The first date has the Field name: ExpectedSeparationDate
The second date has the field name: DateChecklistPrepared
The Data Type for each is: Date/Time
The format for each is: dd/mm/yyyy
Both of these dates are on the form I created

Here is what I want to do:
Have a field on my form that calculates and displays the number of days
between the two dates shown above.

Here is what I've done to try to accomplish that:
In the table view for the table DDForm2648, I added a row and made the Field
Name: DateDiff
For the Data Type, I selected: number
Below in the field properties, under the General Tab, I clicked in the space
"Default value" and a botton with three dots appeared.
I clicked on the button with three dots and the Expression Builder box
appeared.
I clicked on the + to the left of Functions and then on the file folder icon
to the left of Built-In Functions.
I selected Date/Time.
I selected DateDiff.
I clicked Paste.
I changed the function in the window above to: DateDiff
("d",[ExpectedSeparationDate],[DateChecklistPrepared])
I clicked OK.
I clicked the save icon at the top on the menu bar.
I received the following error message: The database engine does not
recognize either the field 'ExpectedSeparationDate' in a validation
expression, or the default value in the table 'DDForm2648'.

I'm a total novice and know nothing about programming. I didn't think it
would be this difficult but I'd really appreciate it if someone can give m e a
simple step by simple step on how to accomplish this.
Thanks!
Click to expand...

I'm afraid you have somewhat lost your way! The DefaultValue for a field in
a table is NOT the place to calculate the date difference! The place to do
it is in the form where you display the data.

For the text box on the form where you want the number of days displayed,
set it's ControlSource property to:

= DateDiff("d",[ExpectedSeparationDate],[DateChecklistPrepared])
 
Thank you. It worked.

Brian said:
SKaplan said:
I am trying to use the DateDiff function and have read as much as I can in
this newsgroup about that function and how to calculate the interval between
two dates. However, as a novice Access user, I'm totally confused and lost.

Here is what I have done:
I have two dates I've created in the design view of a table called: DDForm
2648
The first date has the Field name: ExpectedSeparationDate
The second date has the field name: DateChecklistPrepared
The Data Type for each is: Date/Time
The format for each is: dd/mm/yyyy
Both of these dates are on the form I created

Here is what I want to do:
Have a field on my form that calculates and displays the number of days
between the two dates shown above.

Here is what I've done to try to accomplish that:
In the table view for the table DDForm2648, I added a row and made the Field
Name: DateDiff
For the Data Type, I selected: number
Below in the field properties, under the General Tab, I clicked in the space
"Default value" and a botton with three dots appeared.
I clicked on the button with three dots and the Expression Builder box
appeared.
I clicked on the + to the left of Functions and then on the file folder icon
to the left of Built-In Functions.
I selected Date/Time.
I selected DateDiff.
I clicked Paste.
I changed the function in the window above to: DateDiff
("d",[ExpectedSeparationDate],[DateChecklistPrepared])
I clicked OK.
I clicked the save icon at the top on the menu bar.
I received the following error message: The database engine does not
recognize either the field 'ExpectedSeparationDate' in a validation
expression, or the default value in the table 'DDForm2648'.

I'm a total novice and know nothing about programming. I didn't think it
would be this difficult but I'd really appreciate it if someone can give m e a
simple step by simple step on how to accomplish this.
Thanks!
Click to expand...

I'm afraid you have somewhat lost your way! The DefaultValue for a field in
a table is NOT the place to calculate the date difference! The place to do
it is in the form where you display the data.

For the text box on the form where you want the number of days displayed,
set it's ControlSource property to:

= DateDiff("d",[ExpectedSeparationDate],[DateChecklistPrepared])
Click to expand...
 
Back
Top