Date Difference Help Needed

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")))
 
J

Jeff Boyce

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?
 
D

Dave

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...
 
J

Jeff Boyce

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...
 
D

Dave

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...
 
J

Jeff Boyce

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Difference from Now on sub-form 1
Date Difference help needed 3
Date Difference Code Help Needed 2
Help with Date Difference Expression 2
Help with small result type 2
DMax and DMin Code Help Needed 3
question? What is the difference between date() and date$() 1
Date Comparison 2

Top