MS Access VBA auto updating mod date?

Question

In excel, If I want a cell to autoupdate the date I use a variant of the following code:

TODAY() + MOD(DATE(2021,1,1) - TODAY(),90) - 90

That string will auto update every 90 days, What I need to do is convert this to a VBA function, which will be part of the onload function in a given form. I have tried

DATE() + (2 / 4 / 2021 - DATE()) Mod 90 - 90

Which obviously doesn't work. Any ideas out there on how to make this happen? The result of this needs to be used in a SQL Where statement. Now I could just hardcode the dates, as this does update every quarter, and then run a complicated if statement, but it seems the wrong way to do it.

Edited: I was informed I wasn't clear enough and reread it, and I guess I Am not.

I need this to be a variable in VBA in order to compare the date the application is run to a date stored in 4 different tables.

The idea is that 90 days from a set date, the front end has a search feature that should pull information from the requested table(s) at the users request.

So what I am trying to do is create a public variable that auto updates every 90 days in order to always keep this up to date, and not have to recode dates every so often.

My alternative solution is to use 4 variables to set the months and then run a current date check against each of these by pulling the month from the current date, and then changing that information back to a date and use the new date...

ex of the suboptimal code:

if Month(Date) > 10 Then
   varYear = Year(Date)
   strDate = "10/1/" & CStr(varYear)
   useDate = DateValue(strDate)

Which feels like I am going the long way around instead of just being able to pull a 90 day mod on a certain date when compared to the current date, as the excel spreadsheet formula does.

Which obviously doesn't work. - it's not informative at all. Are you trying to create computed column? See: Combine fields using the Calculated data type and here: Compute the quarter of a date in a calculated field in an Access table — Maciej Los
– Maciej Los, Commented Jul 21, 2021 at 19:50
Yeah that's actually effectively what I have running mine looks like ```useDate = DateValue("10/1" & CStr(Year(Date))) a bit bulky but down to 8 lines of code, given each quarter needs a month check. It just would have been nice to get it down to one line of code. — Christopher J. Joubert
– Christopher J. Joubert, Commented Jul 21, 2021 at 20:43

Gustav · Accepted Answer · 2021-07-21 21:01:47Z

0

I guess(?) it could go like this:

DateAdd("q", DateDiff("q", DateSerial(2021, 1, 1), Date()), DateSerial(2021, 1, 1))

or:

DateAdd("q", 1 + DateDiff("q", DateSerial(2021, 1, 1), Date()), DateSerial(2021, 1, 1))

answered Jul 21, 2021 at 21:01

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MS Access VBA auto updating mod date?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related