Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-06-2019, 06:56 AM   #1
bk1010
Newly Registered User
 
Join Date: Nov 2009
Posts: 12
Thanks: 4
Thanked 0 Times in 0 Posts
bk1010 is on a distinguished road
Today's date in a calculated field

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

bk1010 is offline   Reply With Quote
Old 11-06-2019, 07:09 AM   #2
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,253
Thanks: 10
Thanked 232 Times in 220 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Today's date in a calculated field

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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
bk1010 (11-06-2019)
Old 11-06-2019, 07:14 AM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,775
Thanks: 93
Thanked 1,729 Times in 1,600 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Today's date in a calculated field

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
bk1010 (11-06-2019)
Old 11-06-2019, 07:19 AM   #4
bk1010
Newly Registered User
 
Join Date: Nov 2009
Posts: 12
Thanks: 4
Thanked 0 Times in 0 Posts
bk1010 is on a distinguished road
Re: Today's date in a calculated field

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.
bk1010 is offline   Reply With Quote
Old 11-06-2019, 08:19 AM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Today's date in a calculated field

To simplify:
Today() is not a valid function. It would never work
Date() is a valid function and it might work.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-06-2019, 08:30 AM   #6
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,253
Thanks: 10
Thanked 232 Times in 220 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Today's date in a calculated field

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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 11-06-2019, 08:37 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,656
Thanks: 58
Thanked 1,463 Times in 1,444 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Today's date in a calculated field

Unfortunately, Date() and Now() are not available for Calculated Fields, but DateSerial() is.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-06-2019, 08:51 AM   #8
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,253
Thanks: 10
Thanked 232 Times in 220 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Today's date in a calculated field

Quote:
Originally Posted by theDBguy View Post
Unfortunately, Date() and Now() are not available for Calculated Fields, but DateSerial() is.
They are if used alone - which is what I meant?
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)

Last edited by Micron; 11-06-2019 at 10:56 AM.
Micron is offline   Reply With Quote
Old 11-06-2019, 08:55 AM   #9
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,656
Thanks: 58
Thanked 1,463 Times in 1,444 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Today's date in a calculated field

Quote:
Originally Posted by Micron View Post
They sre if used alone - which is what I meant?
Hi Micron. I just tried it, and I got an error.


Attached Images
File Type: png date.PNG (29.6 KB, 56 views)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-06-2019, 09:26 AM   #10
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 400 Times in 393 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Today's date in a calculated field

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.
Mark_ is offline   Reply With Quote
Old 11-06-2019, 09:30 AM   #11
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,656
Thanks: 58
Thanked 1,463 Times in 1,444 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Today's date in a calculated field

Quote:
Originally Posted by Mark_ View Post
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!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-06-2019, 09:47 AM   #12
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,253
Thanks: 10
Thanked 232 Times in 220 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Today's date in a calculated field

Quote:
Originally Posted by theDBguy View Post
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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 11-06-2019, 09:53 AM   #13
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,656
Thanks: 58
Thanked 1,463 Times in 1,444 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Today's date in a calculated field

Quote:
Originally Posted by Micron View Post
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(?).
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-07-2019, 12:25 PM   #14
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Today's date in a calculated field

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)

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Writing today's date into a field TimTDP Modules & VBA 2 09-30-2013 06:43 AM
Macro to put today's date in a field megatronixs Macros 5 02-17-2013 06:23 AM
Go to record where date field = today Alan Forman Forms 21 10-06-2010 11:32 PM
date field find today travismp Queries 5 08-21-2007 10:07 PM
Want today's date in Field name rmarquez5 Queries 1 02-27-2003 08:07 PM




All times are GMT -8. The time now is 01:04 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World