X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Change Table Values, Access 2016    
post Nov 18 2017, 01:38 PM

Posts: 7
Joined: 16-June 16

Is it possible to change a table value based on the current date? Specifically I'd like to change the value of a short text field in a table based on a date field in the same record being 30 days before the current date. Can this be done with a macro or code?
Go to the top of the page
post Nov 18 2017, 01:46 PM

Posts: 1,864
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL

Possible yes, but may not be appropriate.
If you are working with dates- better to keep as a date and not text.
If you want to compare some date with 30 days prior to current date, then use a function or query.

Perhaps if we knew a little more of why you want this in plain English it would help.
Go to the top of the page
John Vinson
post Nov 18 2017, 02:44 PM

UtterAccess VIP
Posts: 4,273
Joined: 6-January 07
From: Parma, Idaho, US

Tables should store constant data, not something that will change every day. You can calculate the desired date very easily in a Query or in the Control Source of a form or report textbox with the expression

Day30: DateAdd("d", -30, Date())

This uses the builtin functions Date() to read today's date from your computer clock, and DateAdd to subtract 30 days (the "d", check the Help for DateAdd for other choices) from today.

What is the context? How will you be using this field?
Go to the top of the page
post Nov 18 2017, 07:56 PM

UtterAccess VIP
Posts: 10,249
Joined: 10-February 04
From: South Charleston, WV

Be sure to include some examples in your explanation.
Go to the top of the page
post Nov 20 2017, 03:24 PM

Posts: 8
Joined: 30-October 11

Thanks for the replies. It pointed me in the right direction.
My table contains multiple records that have the same value in a date field. On a form I have a combo box that gets it’s values from the date field of a query that runs off the table.. I only want to see the unique values from the last 30 days in the combo box. By setting the Unique Value field to “Yes” in the query builder of the combo box and using the DATEADD function in the query itself I was able to accomplish my goal. Here is the DATEADD formula I used. >=DateAdd("d",-30,Now())

Thanks for all your help
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    15th November 2018 - 07:56 AM