calculating dates in records

[Squibbly]

how do i calculate dates from a current date to the next record date, is
this acheivable in using macro or vba?

 

[Jeff Boyce]

We aren't there ... we can't see what you mean by "to the next record date".
You can use the DateDiff() and the DateAdd() functions to do "date-math".
The function Date() returns today's date.

 

[Squibbly]

sorry about that
i got a macro that opens up a form from the next selected date i.e. now its
1/8/2005 uk format the next record to be opened by the macro is 13/08/2005,
now what i want to do is put how many days to the next record on a form's
field, so when i open up the form, it will display how many days left to the
next record
is that possible?
thanks for your help

 

[Jeff Boyce]

If your form has a control on it with a date value (if I understand how it
is being opened), you could add another control that calculates the
difference between today's date and that date in the control. See my
previous post for the functions...

Regards

Jeff Boyce
<Access MVP>

 

[Squibbly]

but what i want ot do is calculate from a next record date control to the
one on a new form

 

[Jeff Boyce]

I don't think I understand what you are trying to do yet. And I don't have
a clear image of what your data structure looks like.

If you have a date in a row/record, why can't you calculate a "next" date in
a query?

Jeff Boyce
<Access MVP>

 

[Squibbly]

no the "next" date is in the next record, not in the same record

 

[Jeff Boyce]

I believe I understand that you want to USE the "next" date in a new record.
What I was suggesting is calculating the next date (that you will need to
put into the new record) by using a query against the record/row from which
you want to calculate it.

In my mind, I'm imagining a query against the most recent row (requires a
DateTime field to determine "most recent") that returns the relevant date
plus any date-math needed, and uses this calculation in setting up the new
record's relevant date.

You are using forms for doing this, right? Access tables store data, forms
are for entering/displaying data.

Regards

Jeff Boyce
<Access MVP>

 

[Squibbly]

yea i am using a form to display how many days are left until the next
record appears

 

[Jeff Boyce]

Now I'm confused again, then.

If you have a form that holds a current record (with a date), and you want
to display "how many days are left until the next record", you can do that
using an unbound control with the record source as a calculation. Use the
DateAdd() function to help determine a date 'n' days from the date in the
record/form.

Or am I still missing your point?

Jeff Boyce
<Access MVP>

 

[Squibbly]

ok i will try that
thanks

Now I'm confused again, then.

If you have a form that holds a current record (with a date), and you want
to display "how many days are left until the next record", you can do that
using an unbound control with the record source as a calculation. Use the
DateAdd() function to help determine a date 'n' days from the date in the
record/form.

Or am I still missing your point?

Jeff Boyce
<Access MVP>