Date Difference Help Needed

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I need the difference between the below code and the current date. The
number of days since it was Invoiced.
Example; this would give on one of my records the following, but not
differernce between that amount of days and the current date.
-317

=([FTimeBillingSub].[Form]![Text39],0) ,This gives the number of days
since it was Invoiced

Maybe my original formula for Text39 is off;

It is ;
=IIf(Nz(DSum("BillAmt","TTimeBilling","TimeID=Forms!TimeCards!TimeID"),0)=Nz(DSum("Payment","TPaymentSub","TimeID=Forms!TimeCards!TimeID"),0),0,DateDiff("d",Date(),DMax("BillDate","TTimeBilling","TimeID=Forms!TimeCards!TimeID")))
 
Dave

We're not there, we don't know what your form looks like, or what you're
entering in your form. From you description, it sounds like you are trying
to take a difference between a date ("the current date") and a number ("that
amount of days"). DateDiff() only calculates the difference between two
dates.

By the way, your code appears to be calculating a value of "0" in some
situations -- that is not a date (or rather, a 0 in a date fields results in
a date in the late 1800's! Is that what you are trying to do?
 
Yes, trhying to calculate the number of days (i.e. diggerence) between
current date and date of field Text39 which shows the number of days sine it
was invoiced.
hope this helps explain...

Jeff Boyce said:
Dave

We're not there, we don't know what your form looks like, or what you're
entering in your form. From you description, it sounds like you are
trying
to take a difference between a date ("the current date") and a number
("that
amount of days"). DateDiff() only calculates the difference between two
dates.

By the way, your code appears to be calculating a value of "0" in some
situations -- that is not a date (or rather, a 0 in a date fields results
in
a date in the late 1800's! Is that what you are trying to do?

--
Regards

Jeff Boyce
<Office/Access MVP>

Dave said:
I need the difference between the below code and the current date. The
number of days since it was Invoiced.
Example; this would give on one of my records the following, but not
differernce between that amount of days and the current date.
-317

=([FTimeBillingSub].[Form]![Text39],0) ,This gives the number of
days
since it was Invoiced

Maybe my original formula for Text39 is off;

It is ;
=IIf(Nz(DSum("BillAmt","TTimeBilling","TimeID=Forms!TimeCards!TimeID"),0)=Nz
(DSum("Payment","TPaymentSub","TimeID=Forms!TimeCards!TimeID"),0),0,DateDiff
("d",Date(),DMax("BillDate","TTimeBilling","TimeID=Forms!TimeCards!TimeID"))
)
Click to expand...
Click to expand...
 
Dave

Please re-read my response. If your text field holds "number of days", you
can't calculate a DateDiff() between a date (today) and that number (or
rather, you might be able to but it won't mean anything useful).

Here's an example -- "how many days is it between 1/31/2005 and 17?" That's
what it sounds like you are trying to do...

--
Regards

Jeff Boyce
<Office/Access MVP>

Dave said:
Yes, trhying to calculate the number of days (i.e. diggerence) between
current date and date of field Text39 which shows the number of days sine it
was invoiced.
hope this helps explain...

Jeff Boyce said:
Dave

We're not there, we don't know what your form looks like, or what you're
entering in your form. From you description, it sounds like you are
trying
to take a difference between a date ("the current date") and a number
("that
amount of days"). DateDiff() only calculates the difference between two
dates.

By the way, your code appears to be calculating a value of "0" in some
situations -- that is not a date (or rather, a 0 in a date fields results
in
a date in the late 1800's! Is that what you are trying to do?

--
Regards

Jeff Boyce
<Office/Access MVP>

Dave said:
I need the difference between the below code and the current date. The
number of days since it was Invoiced.
Example; this would give on one of my records the following, but not
differernce between that amount of days and the current date.
-317

=([FTimeBillingSub].[Form]![Text39],0) ,This gives the number of
days
since it was Invoiced

Maybe my original formula for Text39 is off;

It is ;
Click to expand...
=IIf(Nz(DSum("BillAmt","TTimeBilling","TimeID=Forms!TimeCards!TimeID"),0)=Nz
(DSum("Payment","TPaymentSub","TimeID=Forms!TimeCards!TimeID"),0),0,DateDiff
("d",Date(),DMax("BillDate","TTimeBilling","TimeID=Forms!TimeCards!TimeID"))
)
Click to expand...
Click to expand...
 
Ok, well then since Text29 shows a numeric value, then in Text471, what
would be the formula to see how many days difference between Text39 and the
current date, i.e. day in number format, or can this be done? Or just show
me how to show to get the current date in a numeric value for the current
day.!
Thanks,

Dave

Jeff Boyce said:
Dave

Please re-read my response. If your text field holds "number of days",
you
can't calculate a DateDiff() between a date (today) and that number (or
rather, you might be able to but it won't mean anything useful).

Here's an example -- "how many days is it between 1/31/2005 and 17?"
That's
what it sounds like you are trying to do...

--
Regards

Jeff Boyce
<Office/Access MVP>

Dave said:
Yes, trhying to calculate the number of days (i.e. diggerence) between
current date and date of field Text39 which shows the number of days sine it
was invoiced.
hope this helps explain...

message
Dave

We're not there, we don't know what your form looks like, or what
you're
entering in your form. From you description, it sounds like you are
trying
to take a difference between a date ("the current date") and a number
("that
amount of days"). DateDiff() only calculates the difference between
two
dates.

By the way, your code appears to be calculating a value of "0" in some
situations -- that is not a date (or rather, a 0 in a date fields results
in
a date in the late 1800's! Is that what you are trying to do?

--
Regards

Jeff Boyce
<Office/Access MVP>

I need the difference between the below code and the current date. The
number of days since it was Invoiced.
Example; this would give on one of my records the following, but not
differernce between that amount of days and the current date.
-317

=([FTimeBillingSub].[Form]![Text39],0) ,This gives the number of
days
since it was Invoiced

Maybe my original formula for Text39 is off;

It is ;

=IIf(Nz(DSum("BillAmt","TTimeBilling","TimeID=Forms!TimeCards!TimeID"),0)=Nz
(DSum("Payment","TPaymentSub","TimeID=Forms!TimeCards!TimeID"),0),0,DateDiff
("d",Date(),DMax("BillDate","TTimeBilling","TimeID=Forms!TimeCards!TimeID"))
)
Click to expand...
Click to expand...
Click to expand...
 
Dave

I'm not there, I can't see your Text29 or Text471 or Text39, and I don't
know what you are putting in them.

Have you looked at Access HELP on the DateDiff() function? It has the
syntax and an example and an explanation.

If you want to calculate the difference between two dates, they'll both have
to be dates, and using DateDiff() is the way to go. You can even use it
inside a query, as a "field".

Good luck

Jeff Boyce
<Office/Access MVP>

Dave said:
Ok, well then since Text29 shows a numeric value, then in Text471, what
would be the formula to see how many days difference between Text39 and the
current date, i.e. day in number format, or can this be done? Or just show
me how to show to get the current date in a numeric value for the current
day.!
Thanks,

Dave

Jeff Boyce said:
Dave

Please re-read my response. If your text field holds "number of days",
you
can't calculate a DateDiff() between a date (today) and that number (or
rather, you might be able to but it won't mean anything useful).

Here's an example -- "how many days is it between 1/31/2005 and 17?"
That's
what it sounds like you are trying to do...

--
Regards

Jeff Boyce
<Office/Access MVP>

Dave said:
Yes, trhying to calculate the number of days (i.e. diggerence) between
current date and date of field Text39 which shows the number of days
Click to expand...
sine
it
was invoiced.
hope this helps explain...

message
Dave

We're not there, we don't know what your form looks like, or what
you're
entering in your form. From you description, it sounds like you are
trying
to take a difference between a date ("the current date") and a number
("that
amount of days"). DateDiff() only calculates the difference between
two
dates.

By the way, your code appears to be calculating a value of "0" in some
situations -- that is not a date (or rather, a 0 in a date fields results
in
a date in the late 1800's! Is that what you are trying to do?

--
Regards

Jeff Boyce
<Office/Access MVP>

I need the difference between the below code and the current date. The
number of days since it was Invoiced.
Example; this would give on one of my records the following, but not
differernce between that amount of days and the current date.
-317

=([FTimeBillingSub].[Form]![Text39],0) ,This gives the number of
days
since it was Invoiced

Maybe my original formula for Text39 is off;

It is ;
Click to expand...
=IIf(Nz(DSum("BillAmt","TTimeBilling","TimeID=Forms!TimeCards!TimeID"),0)=Nz (DSum("Payment","TPaymentSub","TimeID=Forms!TimeCards!TimeID"),0),0,DateDiff ("d",Date(),DMax("BillDate","TTimeBilling","TimeID=Forms!TimeCards!TimeID"))
)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top