Another date question, please advise!

[TracyM via AccessMonster.com]

Hi

I have had a good look through various posts re date counting and there is
alot of similar cases but can't find one to match mine.

I have a table which captures projects coming into the department.

It includes fields that capture, date arrived, date required, and date
complete.
I have set up an unbound field on a form which counts how many days from
current date to date required, this works fine, coding is below.

Control source of unbound box reads, =DateDiff("d",Date(),[Date Required])

But I want to be able to capture this information on a field within the table,
so we can report on whether the project has gone over etc, and also I want to
know if I can do some doing that will stop the count, when the 'date
complete' is finally filled in, as at the moment my counter just carries on
counting even when project is finished.

I think maybe theres some code I could use to say if date complete null, or
something, but I am not very good with code so looking for help with this
please, it would be much appreciated.

Thanks
Tracy

 

[Rick Brandt]

Hi

I have had a good look through various posts re date counting and there is
alot of similar cases but can't find one to match mine.

I have a table which captures projects coming into the department.

It includes fields that capture, date arrived, date required, and date
complete.
I have set up an unbound field on a form which counts how many days from
current date to date required, this works fine, coding is below.

Control source of unbound box reads, =DateDiff("d",Date(),[Date Required])

But I want to be able to capture this information on a field within the table,
so we can report on whether the project has gone over etc,

This is the wrong approach. Derived data should not be stored. Use the same
expression as on your form in a query and use the query for your report. There
is no need for that data to be in your table then.

and also I want to
know if I can do some doing that will stop the count, when the 'date
complete' is finally filled in, as at the moment my counter just carries on
counting even when project is finished.

Just use a slightly different expresssion...

=DateDiff("d",Nz([Date Complete],Date()),[Date Required])

That expression will give the difference between Date Required and Date Complete
unless Date Complete is null. In that case it will substitute the current date
for the Complete Date.

 

[TracyM via AccessMonster.com]

Thanks Rick

I will give it a go and see how I get on

Thanks again
Tracy

Hi

[quoted text clipped - 12 lines]

But I want to be able to capture this information on a field within the table,
so we can report on whether the project has gone over etc,

This is the wrong approach. Derived data should not be stored. Use the same
expression as on your form in a query and use the query for your report. There
is no need for that data to be in your table then.

and also I want to
know if I can do some doing that will stop the count, when the 'date
complete' is finally filled in, as at the moment my counter just carries on
counting even when project is finished.

Just use a slightly different expresssion...

=DateDiff("d",Nz([Date Complete],Date()),[Date Required])

That expression will give the difference between Date Required and Date Complete
unless Date Complete is null. In that case it will substitute the current date
for the Complete Date.

I think maybe theres some code I could use to say if date complete null, or
something, but I am not very good with code so looking for help with this
please, it would be much appreciated.