Choosing next available date

[umalila14 via AccessMonster.com]

I am working on a database, where one date will trigger another. The initial
date will be entered after the email cmd is clicked. The reminder date needs
to be triggered from the initial date from a list of dates I have entered in
a table called QuaterlyDates, which are on the 15th of each quarterly month
(Feb, May, Aug, Nov)

My question is how do I get access to automatically select the date closest
to the initial date. For example if the initial date is 3/16/08 then the
reminder date should automatically populate as 5/15/08 because it is the next
closest quarterly date. I have a table ready with all the dates, but don't
know how to tell access what to do.

Hope this makes sense.

 

[Evi]

If your QuarterlyDates table isn't sorted by the Date put it into a query
and refer to the query instead of Quarterly dates

=NZ(DLookup("[YourDateField]","QuarterlyDates","[YourDateField]>" &
Format([ThisDateField],"0"))

Replace YourDateField with the name of the field in QuarterlyDates
Replace ThisDatefield with the name of the date field in the table into
which you have just typed a date.
Evi

 

[Ken Sheridan]

You don't need to worry about the dates being sorted (in fact tables are sets
and as such have no intrinsic order). Simply look for the earliest (Min)
date after the initial date. In the AfterUpdate event procedure of the
InitialDate control on your data entry form assign a value to the
ReminderDate:

ReminderDate = DMin("QuarterlyDate", "QuarterlyDates", "QuarterlyDate > #" &
Format(InitialDate,"mm/dd/yyyy") & "#")

In fact you don't need to store the reminder date in the main at all as it
can be computed from the initial date. Moreover, to store the date in the
table is a bad idea as it introduces redundancy and the risk of inconsistent
data.

You can show the reminder date in a computed control in a form or report, or
a computed column in a query using the above expression, e.g. as the
ControlSource of a text box control in a form or report:

= DMin("QuarterlyDate", "QuarterlyDates", "QuarterlyDate > #" &
Format(InitialDate,"mm/dd/yyyy") & "#")

In a query you can put the following in the 'field' row of a blank column in
query design view:

ReminderDate: DMin("QuarterlyDate", "QuarterlyDates", "QuarterlyDate > #" &
Format(InitialDate,"mm/dd/yyyy") & "#")

The reason for formatting the initial date in the above expressions BTW is
that date literals must be in US date format or an otherwise internationally
unambiguous date format.

Alternatively in a query you can use a subquery to return the reminder date:

SELECT YourMaintable.*,
(SELECT MIN(QuarterlyDate)
FROM QuarterlyDates
WHERE QuarterlyDates.QuarterlyDate

YourMaintable.InitialDate)

AS ReminderDate
FROM YourMaintable;

Ken Sheridan
Stafford, England

 

[umalila14 via AccessMonster.com]

Thank you, but I'm confused. Where do I place this code? I opened the form
in design view, and in the initial notification date field I went to the
After update cmd and typed this:

Private Sub InitialNotifDt_AfterUpdate()
Me.R1Due=nz(DLookup("[ReminderDates]","tblQtryReminderDates","
[ReminderDates]>" & Format([InitialNotifDt],"0"))

Note:
R1Due is the field where the quarterly date should go.
ReminderDates is the field in the table where the Quarterly dates is in.
tbl QtryReminderDates is the table name for the Quarterly Reminders
InitialNotifDt is the initial notification date.


Where did I go wrong? I am very dumb at coding as you can clearly see.

If your QuarterlyDates table isn't sorted by the Date put it into a query
and refer to the query instead of Quarterly dates

=NZ(DLookup("[YourDateField]","QuarterlyDates","[YourDateField]>" &
Format([ThisDateField],"0"))

Replace YourDateField with the name of the field in QuarterlyDates
Replace ThisDatefield with the name of the date field in the table into
which you have just typed a date.
Evi

I am working on a database, where one date will trigger another. The initial
date will be entered after the email cmd is clicked. The reminder date needs

[quoted text clipped - 12 lines]

 

[umalila14 via AccessMonster.com]

Ken, THANK YOU, this worked!! I'm very excited!

You don't need to worry about the dates being sorted (in fact tables are sets
and as such have no intrinsic order). Simply look for the earliest (Min)
date after the initial date. In the AfterUpdate event procedure of the
InitialDate control on your data entry form assign a value to the
ReminderDate:

ReminderDate = DMin("QuarterlyDate", "QuarterlyDates", "QuarterlyDate > #" &
Format(InitialDate,"mm/dd/yyyy") & "#")

In fact you don't need to store the reminder date in the main at all as it
can be computed from the initial date. Moreover, to store the date in the
table is a bad idea as it introduces redundancy and the risk of inconsistent
data.

You can show the reminder date in a computed control in a form or report, or
a computed column in a query using the above expression, e.g. as the
ControlSource of a text box control in a form or report:

= DMin("QuarterlyDate", "QuarterlyDates", "QuarterlyDate > #" &
Format(InitialDate,"mm/dd/yyyy") & "#")

In a query you can put the following in the 'field' row of a blank column in
query design view:

ReminderDate: DMin("QuarterlyDate", "QuarterlyDates", "QuarterlyDate > #" &
Format(InitialDate,"mm/dd/yyyy") & "#")

The reason for formatting the initial date in the above expressions BTW is
that date literals must be in US date format or an otherwise internationally
unambiguous date format.

Alternatively in a query you can use a subquery to return the reminder date:

SELECT YourMaintable.*,
(SELECT MIN(QuarterlyDate)
FROM QuarterlyDates
WHERE QuarterlyDates.QuarterlyDate

YourMaintable.InitialDate)

AS ReminderDate
FROM YourMaintable;

Ken Sheridan
Stafford, England

I am working on a database, where one date will trigger another. The initial
date will be entered after the email cmd is clicked. The reminder date needs

[quoted text clipped - 9 lines]

Hope this makes sense.