Date between date1 and current date

[Guest]

I posted to the thread I made yesterday with same subject but not sure if you
guys look back. 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 thread 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

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

--
Duane Hookom
MS Access MVP

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

 

[Darren]

If DaySince is empty then you will get nothing, since you are placing a
criteria on that field. Nothing will match.

You want to put the Expression in the "Field" box NOT the Criteria box.

Like This

NewDaysSince: Date()-[Date1]

It will return a calculated field.

I posted to the thread I made yesterday with same subject but not sure if you
guys look back. 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 thread 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

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

--
Duane Hookom
MS Access MVP

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

 

[Guest]

By the way I am using Access 2002... Thanks for your help

I posted to the thread I made yesterday with same subject but not sure if you
guys look back. 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 thread 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

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

--
Duane Hookom
MS Access MVP

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

 

[John Vinson]

I tried both your examples and have read just about every thread 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.

The field daysince SHOULD NOT EXIST in your Table.

If you had data in it today, it would be *wrong* tomorrow. It's
derived data; don't store it - instead, calculate it on demand.

To do so, create a Query based on CHOCOMADE (yummy!) selecting the
Date1, Milk, Dark and Vanilla fields; in a vacant Field cell type

Daysince: DateDiff("d", [Date1], Date())

or just

Daysince: Date() - [Date1]

You can sort by Daysince, put criteria on it, anything except edit it.
There's no benefit to having it in your Table.

John W. Vinson[MVP]

 

[Guest]

Thank you Darren & John
THANK YOU SO MUCH YOU GUYS THAT WORKS!!!
I think those little details like that should be included in Access help.
If it is it is not readily available in the example area... I'm a Chocolate
maker... need details at my fingertips!!
You're the best!!! aside from chocolate... I think John Vincent Helped me a
couple years back when I was trying to get pictures of chocolates to show up
in my printable catalogue... even that worked out great
See you guys next time around.

I posted to the thread I made yesterday with same subject but not sure if you
guys look back. 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 thread 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

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

--
Duane Hookom
MS Access MVP

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

 

[Guest]

Sorry John Imeant to Say John Vinson, and I called you Vincent... Darn thanks
you get for helping me.... Thanks again...just think chocolate and it'll make
it better
Luke

Thank you Darren & John
THANK YOU SO MUCH YOU GUYS THAT WORKS!!!
I think those little details like that should be included in Access help.
If it is it is not readily available in the example area... I'm a Chocolate
maker... need details at my fingertips!!
You're the best!!! aside from chocolate... I think John Vincent Helped me a
couple years back when I was trying to get pictures of chocolates to show up
in my printable catalogue... even that worked out great
See you guys next time around.

I posted to the thread I made yesterday with same subject but not sure if you
guys look back. 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 thread 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

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

--
Duane Hookom
MS Access MVP


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

 

[John Vinson]

Sorry John Imeant to Say John Vinson, and I called you Vincent... Darn thanks
you get for helping me.... Thanks again...just think chocolate and it'll make
it better

<chuckle> I'm very much used to that misspelling; Vincent is a much
more common way to spell the name.

Now maybe I should get all petulant and demand some chocolate in
payment... <g>

Glad to have been able to help.

John W. Vinson[MVP]