DateDiff

[Guest]

Can't figure out what I am doing wrong, have looked at other posts and
tutorials.

Want to show some 'days' before events. Such as Recurrency Training. Have
a field with the expiry date (RecurrencyDue), and a field that shows current
day TodayIs '=Date()'. The current date shows up fine, but when I input a
future date in

Trying to use =DateDiff("d",[RecurrencyDue],[TodayIs]) in the
'RecurrencyDaysLeft' but not calculating.

Thanks,

 

[Steve Schapel]

Gary,

I think the probelm arises because TodayIs is itself a calculated control.

Try...
=DateDiff("d",[RecurrencyDue],Date())
or...
=Date()-[RecurrencyDue]

 

[Guest]

Hi Gary

Try this

=DateDiff("d",[RecurrencyDue],Date())

If the RecurrencyDue date is in the future you will get a minus number as
the dates count down towards today so a conditional format could be a good
idea for user ease.


Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.

 

[Guest]

Should the cell be formated for text?

Gary,

I think the probelm arises because TodayIs is itself a calculated control.

Try...
=DateDiff("d",[RecurrencyDue],Date())
or...
=Date()-[RecurrencyDue]

--
Steve Schapel, Microsoft Access MVP

Can't figure out what I am doing wrong, have looked at other posts and
tutorials.

Want to show some 'days' before events. Such as Recurrency Training. Have
a field with the expiry date (RecurrencyDue), and a field that shows current
day TodayIs '=Date()'. The current date shows up fine, but when I input a
future date in

Trying to use =DateDiff("d",[RecurrencyDue],[TodayIs]) in the
'RecurrencyDaysLeft' but not calculating.

Thanks,

 

[Guest]

No. Use Number. Then you can use it in other ways such as a messge box
opens when there are only so many days to go.


--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.

Should the cell be formated for text?

Gary,

I think the probelm arises because TodayIs is itself a calculated control.

Try...
=DateDiff("d",[RecurrencyDue],Date())
or...
=Date()-[RecurrencyDue]

--
Steve Schapel, Microsoft Access MVP

Can't figure out what I am doing wrong, have looked at other posts and
tutorials.

Want to show some 'days' before events. Such as Recurrency Training. Have
a field with the expiry date (RecurrencyDue), and a field that shows current
day TodayIs '=Date()'. The current date shows up fine, but when I input a
future date in

Trying to use =DateDiff("d",[RecurrencyDue],[TodayIs]) in the
'RecurrencyDaysLeft' but not calculating.

Thanks,

 

[Guest]

Wayne and Steve; still something wrong; have tred both. Have also tried with
the cell formated as 'number'. About 10 years ago I was fairly comfortable
with FileMakerPro, and this is my first stab at Access. Lots to learn......
Does anyone ever actually send a file out for help? That is quite common on
a CAD forum that I work with.

Gary,

I think the probelm arises because TodayIs is itself a calculated control.

Try...
=DateDiff("d",[RecurrencyDue],Date())
or...
=Date()-[RecurrencyDue]

--
Steve Schapel, Microsoft Access MVP

Can't figure out what I am doing wrong, have looked at other posts and
tutorials.

Want to show some 'days' before events. Such as Recurrency Training. Have
a field with the expiry date (RecurrencyDue), and a field that shows current
day TodayIs '=Date()'. The current date shows up fine, but when I input a
future date in

Trying to use =DateDiff("d",[RecurrencyDue],[TodayIs]) in the
'RecurrencyDaysLeft' but not calculating.

Thanks,

 

[Guest]

It should work IF

RecurrencyDue is a field formated as date/time and contains data.

You have not changed the "name" of the field - this is different from the
control source. Open the properties box and check the "Other" column and
ensure that the field name is RecurrencyDue.

Let me know if you have done these and it's still not working

--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.

Wayne and Steve; still something wrong; have tred both. Have also tried with
the cell formated as 'number'. About 10 years ago I was fairly comfortable
with FileMakerPro, and this is my first stab at Access. Lots to learn......
Does anyone ever actually send a file out for help? That is quite common on
a CAD forum that I work with.

Gary,

I think the probelm arises because TodayIs is itself a calculated control.

Try...
=DateDiff("d",[RecurrencyDue],Date())
or...
=Date()-[RecurrencyDue]

--
Steve Schapel, Microsoft Access MVP

Can't figure out what I am doing wrong, have looked at other posts and
tutorials.

Want to show some 'days' before events. Such as Recurrency Training. Have
a field with the expiry date (RecurrencyDue), and a field that shows current
day TodayIs '=Date()'. The current date shows up fine, but when I input a
future date in

Trying to use =DateDiff("d",[RecurrencyDue],[TodayIs]) in the
'RecurrencyDaysLeft' but not calculating.

Thanks,

 

[Guest]

Still not working, Wayne. Expect it is some very basic rule (naming?) that
I'm missing. The Label name is 'RecurrencyDue_Label'. Under 'Format',
caption is 'Recurrency'. The data tab for the box shows 'control source' as
'Recurrency'. 'Other' tab shows name of date/time box as as 'RecurrencyDue'.
Have tried changing both the formulae and the names between ......Due, and
without. Have data (date) in box and formatted dd-mmm-yy


Gary

It should work IF

RecurrencyDue is a field formated as date/time and contains data.

You have not changed the "name" of the field - this is different from the
control source. Open the properties box and check the "Other" column and
ensure that the field name is RecurrencyDue.

Let me know if you have done these and it's still not working

--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.

Wayne and Steve; still something wrong; have tred both. Have also tried with
the cell formated as 'number'. About 10 years ago I was fairly comfortable
with FileMakerPro, and this is my first stab at Access. Lots to learn......
Does anyone ever actually send a file out for help? That is quite common on
a CAD forum that I work with.

Gary,

I think the probelm arises because TodayIs is itself a calculated control.

Try...
=DateDiff("d",[RecurrencyDue],Date())
or...
=Date()-[RecurrencyDue]

--
Steve Schapel, Microsoft Access MVP

Gary wrote:
Can't figure out what I am doing wrong, have looked at other posts and
tutorials.

Want to show some 'days' before events. Such as Recurrency Training. Have
a field with the expiry date (RecurrencyDue), and a field that shows current
day TodayIs '=Date()'. The current date shows up fine, but when I input a
future date in

Trying to use =DateDiff("d",[RecurrencyDue],[TodayIs]) in the
'RecurrencyDaysLeft' but not calculating.

Thanks,

 

[John Vinson]

Still not working, Wayne. Expect it is some very basic rule (naming?) that
I'm missing. The Label name is 'RecurrencyDue_Label'. Under 'Format',
caption is 'Recurrency'. The data tab for the box shows 'control source' as
'Recurrency'. 'Other' tab shows name of date/time box as as 'RecurrencyDue'.
Have tried changing both the formulae and the names between ......Due, and
without. Have data (date) in box and formatted dd-mmm-yy

A Label is *just that* - a text only explanatory label. It doesn't
contain data. I suspect you're picking the Label associated with a
textbox, rather than the textbox itself.

John W. Vinson[MVP]

 

[Steve Schapel]

Gary,

Apart form "not working", you haven't actually said what the problem is.
Could you be more specific?

Do I have this right so far...

You have a textbox. The Control Source property is Recurrency. The
Name property shows RecurrencyDue.

You have another textbox. Its Name is ??. And the Control Source is
the expression we have been discussing.

The calculation expression should refer to the value of the field, not
the value of the textbox. (Sorry if this is confusing - forms do not
have fields). So try this instead...
=Date()-[Recurrency]

 

[Guest]

Think it best to step through the procedures with a new start sample:
1-new database, open in table design
2-enter FieldName 'PassExp', DataType 'Date/Time'
3-enter FieldName 'DaysLeft', DataType 'Number'
4-enter FieldName 'Today', DataType 'Date/Time'
~~~~save as 'Practice' and close~~~~
1-Change to FormDesign and insert all fields
2-select 'DaysLeft' box, and in 'Properties/Data/Default Value
"=Date()-[PassExp]"
3-select 'Today' box, and in 'Properties/Data/Default Value "=Date()"
4-Save and close FormDesign. Open in 'Form', enter date in "PassExp'
results in no calculation in "DaysLeft". The "Today" box shows current date.
Changing formulation to 'DateDiff' using the 'Today' info doesn't do
anything either.

So, I'm guessing I'm putting something in the wrong place.

Gary

Gary,

Apart form "not working", you haven't actually said what the problem is.
Could you be more specific?

Do I have this right so far...

You have a textbox. The Control Source property is Recurrency. The
Name property shows RecurrencyDue.

You have another textbox. Its Name is ??. And the Control Source is
the expression we have been discussing.

The calculation expression should refer to the value of the field, not
the value of the textbox. (Sorry if this is confusing - forms do not
have fields). So try this instead...
=Date()-[Recurrency]

--
Steve Schapel, Microsoft Access MVP

Still not working, Wayne. Expect it is some very basic rule (naming?) that
I'm missing. The Label name is 'RecurrencyDue_Label'. Under 'Format',
caption is 'Recurrency'. The data tab for the box shows 'control source' as
'Recurrency'. 'Other' tab shows name of date/time box as as 'RecurrencyDue'.
Have tried changing both the formulae and the names between ......Due, and
without. Have data (date) in box and formatted dd-mmm-yy

 

[John Vinson]

Think it best to step through the procedures with a new start sample:
1-new database, open in table design
2-enter FieldName 'PassExp', DataType 'Date/Time'
3-enter FieldName 'DaysLeft', DataType 'Number'
4-enter FieldName 'Today', DataType 'Date/Time'
~~~~save as 'Practice' and close~~~~
1-Change to FormDesign and insert all fields
2-select 'DaysLeft' box, and in 'Properties/Data/Default Value
"=Date()-[PassExp]"
3-select 'Today' box, and in 'Properties/Data/Default Value "=Date()"
4-Save and close FormDesign. Open in 'Form', enter date in "PassExp'
results in no calculation in "DaysLeft". The "Today" box shows current date.
Changing formulation to 'DateDiff' using the 'Today' info doesn't do
anything either.

So, I'm guessing I'm putting something in the wrong place.

Exactly.

You're putting a CALCULATION - DaysLeft - in a TABLE.

Tables should contain static data.

Calculations must be done in Queries, in the Control Source of a
textbox on a form or report, or in code.

The field DaysLeft *should not exist in your table*.

Unless you want it to permanently record the date upon which a record
was created, the field Today should not exist either.


If you want to SEE today's date and the number of days left on a Form,
simply store the field PassExp in a table (along with other non-date
related fields, presumably).

on a Form based on this table put a textbox (labeled Days Left) with a
control source

= DateDiff("d", Date(), [PassExp])

This number of days left will not be stored anywhere in your table, or
anywhere else - but it's not *NEEDED* to store it. You can calculate
it whenever it's needed, using the date that the calculation is being
done, whatever that date might be.

You may be assuming that information must be stored in a Table in
order to be of any use. That assumption is WRONG and is apparently the
basis of your confusion.

John W. Vinson[MVP]

 

[Guest]

Ok John, I couldn't see where to enter a calculation into a query; had been
looking. Just now typed in the =Date().... into the adjacent 'Field' in the
Query and produced what I was looking for.

Thanks, Gary

Think it best to step through the procedures with a new start sample:
1-new database, open in table design
2-enter FieldName 'PassExp', DataType 'Date/Time'
3-enter FieldName 'DaysLeft', DataType 'Number'
4-enter FieldName 'Today', DataType 'Date/Time'
~~~~save as 'Practice' and close~~~~
1-Change to FormDesign and insert all fields
2-select 'DaysLeft' box, and in 'Properties/Data/Default Value
"=Date()-[PassExp]"
3-select 'Today' box, and in 'Properties/Data/Default Value "=Date()"
4-Save and close FormDesign. Open in 'Form', enter date in "PassExp'
results in no calculation in "DaysLeft". The "Today" box shows current date.
Changing formulation to 'DateDiff' using the 'Today' info doesn't do
anything either.

So, I'm guessing I'm putting something in the wrong place.

Exactly.

You're putting a CALCULATION - DaysLeft - in a TABLE.

Tables should contain static data.

Calculations must be done in Queries, in the Control Source of a
textbox on a form or report, or in code.

The field DaysLeft *should not exist in your table*.

Unless you want it to permanently record the date upon which a record
was created, the field Today should not exist either.


If you want to SEE today's date and the number of days left on a Form,
simply store the field PassExp in a table (along with other non-date
related fields, presumably).

on a Form based on this table put a textbox (labeled Days Left) with a
control source

= DateDiff("d", Date(), [PassExp])

This number of days left will not be stored anywhere in your table, or
anywhere else - but it's not *NEEDED* to store it. You can calculate
it whenever it's needed, using the date that the calculation is being
done, whatever that date might be.

You may be assuming that information must be stored in a Table in
order to be of any use. That assumption is WRONG and is apparently the
basis of your confusion.

John W. Vinson[MVP]

 

[John Vinson]

Ok John, I couldn't see where to enter a calculation into a query; had been
looking. Just now typed in the =Date().... into the adjacent 'Field' in the
Query and produced what I was looking for.

You can actually use the syntax

FieldAlias: Expression

in a vacant Field cell in the query design grid, e.g.

Today: Date()

and

DaysLeft: DateDiff("n", Date(), [DateDue]


John W. Vinson[MVP]