Same date each year (1 Viewer)

Slats58

New member
Local time
Today, 23:30
Joined
Sep 2, 2021
Messages
1
Hi Guys,

I’m a bit of a novice here with MS Access.

I have created a register for work that keeps track of the audits we do of security packets on the 3,800+ shelves and everything is working as it should except for archiving some data.

We need to audit all 3,800+ in one financial year. Problem is that my financial year is from 1st Oct YYYY to 30th Sept YYYY the next year. This date for the past financial year is obsoleted and we start the new year fresh again. I have fixed the problem for this year by including 30/09/2020 n to the date field in my update query, but I am going to have the same problem on 30/09/2021.

Is there any way I can tell the update query that runs continually and updates Table “Vault Audit Data” to “Shelf Audits Done” to ignore any data after 01/10/YYYY(Current Year)?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:30
Joined
Feb 19, 2013
Messages
16,553
perhaps use the date function?

cdate("1st Oct " & year(date()))
 

Minty

AWF VIP
Local time
Today, 12:30
Joined
Jul 26, 2013
Messages
10,354
At least two solutions,
1. store the current start and end of the financial year in a table and bring that into your query, display them on a form at start up, as a check, and you could also automatically update them once the DB is opened in the new Fin year.
2. Make two functions that return the start and end of the Fin year, and use those in your query.

I personally would use 1. as I think it is more efficient in a query, but you aren't dealing with millions of records, so speed may not be a concern.
 

moke123

AWF VIP
Local time
Today, 08:30
Joined
Jan 11, 2013
Messages
3,849
Could try something along the lines of Fiscal year.

Code:
Function GetFiscalYear(Dt As Variant, Optional StartMonth As Integer = 9) As Variant
If Not IsDate(Dt) Then Exit Function
If Month(Dt) >= StartMonth Then
   GetFiscalYear = (Year(Dt) + 1)
Else
   GetFiscalYear = Year(Dt)
End If
End Function
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:30
Joined
May 7, 2009
Messages
19,169
update YourTable set Field=... where [dateField] < DateSerial(Year(Date()), 10, 1)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:30
Joined
Oct 29, 2018
Messages
21,357
Is there any way I can tell the update query that runs continually and updates Table “Vault Audit Data” to “Shelf Audits Done” to ignore any data after 01/10/YYYY(Current Year)?
Hi. Welcome to AWF!

I'm not sure I understand your question. To get the current year, you can use the Year() function against the current date.
Code:
Year(Date())

So, to get October first of the current year, you can also use the DateSerial()Efunction.
Code:
DateSerial(Year(Date()), 10, 1)

Sent from phone...

Edit: Oops, too slow... Still waking up...
 

Users who are viewing this thread

Top Bottom