Question Today's date in a calculated field (1 Viewer)

bk1010

Registered User.
Local time
Today, 20:00
Joined
Nov 16, 2009
Messages
16
Hi All,

Is there a way to use calculated field in a table with today's date?
For Example:
=IIF([OrderDate]<Today(),"Out of Date","Still in Date")

Thanks in advance
 

Micron

AWF VIP
Local time
Today, 16:00
Joined
Oct 20, 2018
Messages
3,476
I believe the answer is no - simple calculations like Now() or Date() only. However, it is considered bad design to store calculations. That's what forms, queries and reports are for - calculating on the fly.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:00
Joined
Feb 28, 2001
Messages
27,001
First, MAYBE it is possible. Maybe not. Micron says "NO" and I tend to not discount what he says 'cause he's pretty reliable.

But second, it is almost NEVER recommended. Computed fields generally tend to be pains in the toches.

You would do this better with a query based on the table and in THAT context, it is trivial because that is EXACTLY the kind of thing queries were designed to accommodate. AND you can use SELECT queries almost anywhere that you could have used a table.
 

bk1010

Registered User.
Local time
Today, 20:00
Joined
Nov 16, 2009
Messages
16
Both,

I can see the logic in not storing the data in a calculated field. I shall design a query instead.

Thanks for the advice.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:00
Joined
Feb 19, 2002
Messages
42,973
To simplify:
Today() is not a valid function. It would never work
Date() is a valid function and it might work.
 

Micron

AWF VIP
Local time
Today, 16:00
Joined
Oct 20, 2018
Messages
3,476
Where did today() come from? Somebody edited a post?
TDM - thanks for the comment. Sometimes you don't know until you test because the question is often about something you would never do and I'm playing on my phone so I can't test.
Been waiting 3 1/2 hours for the car to just get in to the shop.:(
EDIT
Never mind. Maybe I'll get a phone with a bigger screen next time.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:00
Joined
Oct 29, 2018
Messages
21,358
Unfortunately, Date() and Now() are not available for Calculated Fields, but DateSerial() is.
 

Micron

AWF VIP
Local time
Today, 16:00
Joined
Oct 20, 2018
Messages
3,476
Unfortunately, Date() and Now() are not available for Calculated Fields, but DateSerial() is.
They are if used alone - which is what I meant?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 13:00
Joined
Oct 29, 2018
Messages
21,358
They sre if used alone - which is what I meant?
Hi Micron. I just tried it, and I got an error.


 

Attachments

  • date.PNG
    date.PNG
    29.6 KB · Views: 3,340

Mark_

Longboard on the internet
Local time
Today, 13:00
Joined
Sep 12, 2017
Messages
2,111
bk1010,

One other aspect others have not touched on regarding calculated fields; they are calculated when the record is added/updated. They don't "Magically" change if the record is not touched.

For your calculation it would ONLY be valid for the date it is added/updated. The next day, it wouldn't show "Out of Date" unless you forced the record to be changed.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:00
Joined
Oct 29, 2018
Messages
21,358
bk1010,

One other aspect others have not touched on regarding calculated fields; they are calculated when the record is added/updated. They don't "Magically" change if the record is not touched.

For your calculation it would ONLY be valid for the date it is added/updated. The next day, it wouldn't show "Out of Date" unless you forced the record to be changed.
That's a very good point!
 

Micron

AWF VIP
Local time
Today, 16:00
Joined
Oct 20, 2018
Messages
3,476
Hi Micron. I just tried it, and I got an error
Thanks. I must have been thinkng about Default Value property. Put it down to me being bored while waiting (still) and not being able to test something I don't use.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:00
Joined
Oct 29, 2018
Messages
21,358
Thanks. I must have been thinkng about Default Value property. Put it down to me being bored while waiting (still) and not being able to test something I don't use.
Hi. No worries. Good luck with the repair(?).
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:00
Joined
Feb 19, 2002
Messages
42,973
Now I remember why I have never used any calculated fields in tables. Given when the calculation happens, it makes perfect sense that they would not support Date() or Now().

Just do the calculation in your query. It will always be up to date. (grin)
 

ReinM

New member
Local time
Today, 21:00
Joined
Nov 15, 2020
Messages
11
I want to add a timestamp-field in a table to register when a record is first filled out. I know that this is simple in a form, but in this case I want it in a table-field. As the method with the now()-function in a calculated field doesn't work, I would like a suggestion for how to do that. I tried now() as default value in the field, but that registers the moment the empty record is created, while my purpose is to register the moment a particular field in the record is filled in for the first time.
 

ReinM

New member
Local time
Today, 21:00
Joined
Nov 15, 2020
Messages
11
No, just now(). I tried your suggestion =now(), but the same problem occurs: it works in sofar that a date/timevalue appears, but alas it is the time that the new empty record is created, which happens automatically the moment a previous record is filled out. The new empty created record with the timestamp in it then stays waiting to be filled out, and that may very well happen much later.
That is why I tried in a calculated field "iif(field_x is not null,(now()),(null))". But that doesn't work because the now() function is not accepted in a calculated field.
 

Minty

AWF VIP
Local time
Today, 20:00
Joined
Jul 26, 2013
Messages
10,355
Access creates the new record automatically, with the default values in it.

I would question why you are creating an "empty" record as soon as the previous one is completed.
Why not wait until you need the new record?
 
Last edited:

cheekybuddha

AWF VIP
Local time
Today, 20:00
Joined
Jul 21, 2014
Messages
2,237
Then you will have to do it at the form level.

In the Form_BeforeUpdate event you can check whether all necessary data is present and correct and then add Now() to your Timestamp field if not already present
 

ReinM

New member
Local time
Today, 21:00
Joined
Nov 15, 2020
Messages
11
I would question why you are creating an "empty" record as soon as the previous one is completed.
Why not wait until you need the new record?
Access creates the new record automatically, with the default values in it.
 

Users who are viewing this thread

Top Bottom