Days Between date1 and current date

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

Guest

This seems basic... my Access 2002 tables has date1 field as well as other
field with vital information.

I want a new field to display the difference (Days) between date1 and the
current date without having to change the date every day

I have made a query using the different table fields and included "date1"
but not sure if I add a blank field in the table with a new named "Daysince"
so that in the query, the "Daysince" field gets automatically filled in or if
there is something else to do that I don't grasp about this.

I have tried datediff("d",now(),[date1]) and some other variations similar
but nothing shows or I get syntax error.

I hope I have offered enough information. Thank you for you assistance
Luke
 
This should work:
DateDiff("d",[Date1], Date())
This assumes there are no Null values in the Date1 field.
 
Luke:

this must be done in a query or in a calculated field on a form. fields in
tables are not supposed to store "information" which is processed data, they
are supposed to store "data" - the least divisible property of something.

create a new query and drag all the fields from your table onto the grid and
then create a calculated field by entering a formula similar to this in the
next available column.

Days Since: Date()-[YourDateFieldNameHere]

The resulting value will be in number of days.

If using a form you could add a text box and make this same formula its
control source =Date()-[YourDateFieldNameHere]. This assumes the
[YourDateFieldNameHere] field is a field found in the data source for the
form (it could be a table or query, it depends on how you build it)

Seth
 
Duane and Seth, thank you for responding so promply yesterday. appologies
for taking so long to get back here.
I tried both your examples and have read just about every string regarding
this. There must be something that I am not aware of.
Therefore I started over and made the simplest table starting from a fresh
new db with only one table without changing any of the defaults that Access
makes when building a table in design view as follows:

tbl: CHOCOMADE
fields: Date1; Milk; Dark; Vanilla; Daysince

I entered some dates (no nulls) into Date1 and some data into Milk; Dark;
Vanilla; fields and left the field Daysince empty.

I made ChocoQuery, drag each field to the query in design view as Seth
pointed out, then entered in Daysince criteria: Date()-[Date1] but nothing
shows.

I tried Duanes example doing the same procedure as above using
DateDiff("d",[Date1], Date()) in Daysince field and still nothing.

Note if I remove the criteria expression from the Datesince in the
ChocoQuery I can then see the data...

I hope I have articulated this well enough... as I said this seems to easy
to be so difficult I'll be watching all day for replies and correspondance.
Thanks again, Do please help
Luke


Duane Hookom said:
This should work:
DateDiff("d",[Date1], Date())
This assumes there are no Null values in the Date1 field.

--
Duane Hookom
MS Access MVP


Luke said:
This seems basic... my Access 2002 tables has date1 field as well as other
field with vital information.

I want a new field to display the difference (Days) between date1 and the
current date without having to change the date every day

I have made a query using the different table fields and included "date1"
but not sure if I add a blank field in the table with a new named
"Daysince"
so that in the query, the "Daysince" field gets automatically filled in or
if
there is something else to do that I don't grasp about this.

I have tried datediff("d",now(),[date1]) and some other variations similar
but nothing shows or I get syntax error.

I hope I have offered enough information. Thank you for you assistance
Luke
Click to expand...
Click to expand...
 
Why did you create a field named DaySince? This should just be a calculated
value in a query. We have all suggested not storing a value that can or
should be calculated.

Also, field names like "Milk; Dark; Vanilla" suggest an un-normalized table
structure but that is a different matter.
--
Duane Hookom
MS Access MVP


Luke said:
Duane and Seth, thank you for responding so promply yesterday. appologies
for taking so long to get back here.
I tried both your examples and have read just about every string regarding
this. There must be something that I am not aware of.
Therefore I started over and made the simplest table starting from a fresh
new db with only one table without changing any of the defaults that
Access
makes when building a table in design view as follows:

tbl: CHOCOMADE
fields: Date1; Milk; Dark; Vanilla; Daysince

I entered some dates (no nulls) into Date1 and some data into Milk; Dark;
Vanilla; fields and left the field Daysince empty.

I made ChocoQuery, drag each field to the query in design view as Seth
pointed out, then entered in Daysince criteria: Date()-[Date1] but nothing
shows.

I tried Duanes example doing the same procedure as above using
DateDiff("d",[Date1], Date()) in Daysince field and still nothing.

Note if I remove the criteria expression from the Datesince in the
ChocoQuery I can then see the data...

I hope I have articulated this well enough... as I said this seems to easy
to be so difficult I'll be watching all day for replies and
correspondance.
Thanks again, Do please help
Luke


Duane Hookom said:
This should work:
DateDiff("d",[Date1], Date())
This assumes there are no Null values in the Date1 field.

--
Duane Hookom
MS Access MVP


Luke said:
This seems basic... my Access 2002 tables has date1 field as well as
other
field with vital information.

I want a new field to display the difference (Days) between date1 and
the
current date without having to change the date every day

I have made a query using the different table fields and included
"date1"
but not sure if I add a blank field in the table with a new named
"Daysince"
so that in the query, the "Daysince" field gets automatically filled in
or
if
there is something else to do that I don't grasp about this.

I have tried datediff("d",now(),[date1]) and some other variations
similar
but nothing shows or I get syntax error.

I hope I have offered enough information. Thank you for you assistance
Luke
Click to expand...
Click to expand...
Click to expand...
 
Back
Top