Question

Locked

Simple...or what should be simple... Access 2007 question

By shawn ·
I have a field in a table that contains a number or days (examp 100) I need to add a field to a report or right back into the table that calculates the date 100 days ago. Why is this so hard for Access?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

It isn't.

by Maevinn In reply to Simple...or what should b ...

Now()-Field1 works fine. It won't happen in a table view, because that's recursive. You're asking it to tell you a date that's XX days ago, when XX hasn't been identified.

Setting it up in a report, use the formula above (preferably with some formatting) and it will be just fine. Or modify the data entry form to perform the calculation and update a field in the table, unless you need the value to always be XX from NOW(), rather than XX from the date the record was entered.

Collapse -

found something

by shawn In reply to It isn't.

=DateAdd("d",-[Age 1],[Date])

I used this to get the date to count back whatever the number of days were in the Field (Age 1). It works. Thing is, I can't count back from the current date, I have to count back from the date the data was collected and put in Age 1 (via pocket pc).

Collapse -

There is also DateSubstract...

by Tony Hopkinson In reply to found something

e.g
Update MyTable
Set [DateSomeDaysAgo] = DateSubtract("d",[Age1],[Date])
Where [DateSomeDaysAgo] is null

Collapse -

Functions

by Maevinn In reply to found something

Use the Now() function instead of the Date field. That will give you the current date.

Collapse -

Try date() function

by arkman In reply to Simple...or what should b ...

Try
=DateAdd("d",-[Age 1],date())

Collapse -

correction

by shawn In reply to Try date() function

I just read my earlier post, I should have said - I must use the date that came in from the pda, not the current date. So what I did works. I'd rather have it be in the table. How is that possible?

Collapse -

Update Query

by Maevinn In reply to correction

Create an update query that runs on close for the data entry form.

Related Discussions

Related Forums