Need help: form to table

[James Barahoga]

Hi,

I am using Access 2003 and am a newbie.

Anyway, I did a simple form and had Access calculate three dates based on
one that I enter using the "DateAdd" expression.
The calculations are correct and the dates are correcly displayed on the
form. However, when I save and close the database, I realised that all the
fields that I've manually keyed in are correctly entered into the table. The
fields that are "calculated" in the form by "DateAdd" however, are not
entered into the table at all. This messes up everything when I do a query
for a report on the dates that are calculated. Can anyone help?

Also, can anyone advise on how to use a customised calendar which will
consider holidays and weekends when countng days with the DateAdd
expression?

Thanks,
James

 

[Rick Brandt]

Hi,

I am using Access 2003 and am a newbie.

Anyway, I did a simple form and had Access calculate three dates
based on one that I enter using the "DateAdd" expression.
The calculations are correct and the dates are correcly displayed on
the form. However, when I save and close the database, I realised
that all the fields that I've manually keyed in are correctly entered
into the table. The fields that are "calculated" in the form by
"DateAdd" however, are not entered into the table at all. This messes
up everything when I do a query for a report on the dates that are
calculated. Can anyone help?

Calculations should not be stored. Add those same calculations to your query.

 

[James Barahoga]

Hi,
Thanks for helping.
How do I add calculations to my query? Please advise.

Thanks,
James

 

[Homer J Simpson]

Hi,
Thanks for helping.
How do I add calculations to my query? Please advise.

Usually: alter the query to be an update query.

 

[Brian Bastl]

Homer,

your response was a joke, right??

Brian

 

[James Barahoga]

Hi,

Thanks for helping.
I've changed the query to an update query and this is what it looks like
now:

Field : CheckProgress
Table : STUDENTS
Update to : [Students]![CheckProgress]
Criteria ateAdd("y",-30,[ExamDate])

The query can run but I get a popup saying:

"You are about to update 0 record(s)"

Again, the table does not get updated...

Can you help?


Thanks,
James

 

[Douglas J. Steele]

Homer's reply was not what Rick had in mind.

You don't use an Update query: the whole point is NOT to store the results
of the calculation in the table. As fellow Access MVP John Vinson likes to
say "Storing calculated data generally accomplishes only three things: it
wastes disk space, it wastes time (a disk fetch is much slower than almost
any reasonable calculation), and it risks data validity, since once it's
stored in a table either the Total or one of the fields that goes into the
total may be changed, making the value WRONG."

Instead, add an additional field to your query that provides the results of
your calculation.

You do this by putting the formula in an empty cell on the Field line of the
query. Put a name in front of the formula (followed by a colon), or else
Access will name the field something useful like Expr1 <g>

CheckProgress: DateAdd("y",-30,[ExamDate])

Take the CheckProgress field out of your table. The Query will have a field
named CheckProgress which will be the value you want.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

Thanks for helping.
I've changed the query to an update query and this is what it looks like
now:

Field : CheckProgress
Table : STUDENTS
Update to : [Students]![CheckProgress]
Criteria ateAdd("y",-30,[ExamDate])

The query can run but I get a popup saying:

"You are about to update 0 record(s)"

Again, the table does not get updated...

Can you help?


Thanks,
James

Usually: alter the query to be an update query.

 

[Homer J Simpson]

Homer,

your response was a joke, right??

Brian

Nope. You can tell when I'm joking. Note the big sharp teeth.