Hi Jeff, in my subtable there is an Action Type, Action Date and a Due
Date
and Comments. To make it simple Actions 1 - 8. They are all for one
record
(PNGID) in my main table and the record may have anywhere from 1 to 8
actions
(throughout it's cycle) but these actions can be in any order and any
combination. If we use the Action Date, it is typically a day a letter
comes
in telling us something needs to happen and that letter will have a Due
Date
(the user types in both dates).
For two action types, the due date is not specified in the letter received
but needs to be calculated (either 30 or 90 days from the date of the
letter
(action date)) and I currently have this calculated on my subforms. All
the
other due dates are typed in and stored in the table. Because you never
know
what action may be coming up (it could be 1, 3, 7 or 2, 3, 4, 5) I need to
ask for the most current date (max) to know what action we are currently
pursuing. Because actions 1 & 2 have calculated due dates they aren't in
the
table so they come up with a blank due date in my query and report.
I'm trying to figure out a way to make these two calculated due dates show
up with all my other due dates so that the query will include them if they
are the most recent and report on that action. I thought perhaps I could
somehow (hidden box on the subforms?) feed them back into the table as due
dates even though everything I've read says don't store calculations.
I've
tried some different code but I'm not very good at code and can't get it
to
work. Or I thought perhaps there's another solution that I'm missing.
I'm just leaving on holidays and won't be back until Monday so, I won't be
able to post any more details until then but if there is some specifics
that
would help please let me know (SQL in query?, table design?)
Thanks again - I really appreciate it!
Jeff Boyce said:
Perhaps it's me that's confused...
If you DON'T have data in a [Due Date] field, how can you "take the most
recent data ... based on [your] Due Date field and these other two...?
Would you mind providing an example? Having a concrete example might
make
it clearer to me.
Thanks
Regards
Jeff Boyce
Microsoft Office/Access MVP
carrietom said:
Thanks again Jeff - I must be having a bit of a block on this because
I'm
still not understanding. I have 8 types of actions that all have a due
date.
6 of these are stored in the due date field and two are not (because
they're
calculated). How I need to report is on the most recent action which I
do
by
querying the most current due date. Since the 2 calculated dates
aren't
stored in the Due Date field, they do not show up where appropriate.
If I create these fields in a query and use the DateAdd function, is
there
a
way to make the query take the most recent data then based on my Due
Date
field and these other two fields? Thanks.
:
I was suggesting that you NOT store a calculated date in any table.
Open a query. Add the table that has the original stored date. Add
that
date field.
Add a new field, giving it whatever name you want ... and use the
DateAdd()
function to create your first calculated date.
Add another new field, with its own name ... and use the DateAdd()
function
to create your second calculated date.
Now use the query as a source for whatever report needs the calculated
dates.
Regards
Jeff Boyce
Microsoft Office/Access MVP
thanks Jeff, given that - what I'm actually trying to do is get two
calculated dates (Due Dates) to appear on my reports with the other
due
dates
(which are entered manually by the user. (so I would have one
column
of
all
the due dates) Is there a way, using a query that I can make these
two
calculated due dates (from the form) and all my other due dates feed
together? I thought it would be best to somehow get the two
calculated
due
dates to feed back into the due date field in my subtable (because
in
these
two instances they are always blank)?
:
If you have a given date (?Action_Date?), and you are applying a
fixed
rule
([Action_Date] plus one year to calculate a [Due_Date] (or any
other
date-math), you can use the DateAdd() function in a query to derive
the
calculated dates on the fly.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hi,
I have a database with a number of dates. These are in a
subtable
(Date_Details) and only the required dates for data entry are
stored
there
(almost). There is an Action_Date and a Due_Date. Off of the
due
date
are
one or more calculated dates (Send Reminders) that are located on
the
forms
but not stored in the table. Each of the action types has it's
own
subform
(due to functionality for the user). In two of my subforms the
Due_Date
is
actually calculated also (based off the Action Date).
Now my problem is that when I try to run my Due Date reports
(which
include
all actions) these two due dates do not appear because they are
not
stored
in
the table with the other due dates. I know that you aren't
supposed
to
store
calculations in the tables but is there some way that I can get
these
two
onto my reports in the due dates column without storing them in
the
Due
Date
field?
If not, would I be able to put some code on either the After
Update,
or
Lost
Focus event of the calculated field to store it in the Due Date
field
in
my
table? I made some attempts but I keep getting errors and wasn't
sure
if
that was because it isn't possible or I'm just getting the code
wrong.
Any suggestions are really appreciated, as always!
