Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Queries _ Automatically Run A Query The 1st Of Every Month

Posted by: slavescu May 21 2020, 06:35 AM

Hello

I need some guidance or any help in writing code to run a specific query on a specific date of the month, any help would be appreciated
I have been playing with this snippet but without success
' Return the day, month & year from the date 12/31/2015
Dim dy As Integer
Dim mth As Integer
Dim yr As Integer
dy = DatePart( "d", #05/21/2020# )
mth = DatePart( "m", #05/21/2020# )
yr = DatePart( "yyyy", #05/21/2020# )
' Now, dy = 21, mth = 05 and yr = 2020.

If dy=1 then
MsgBox""
End If

Posted by: nuclear_nick May 21 2020, 06:51 AM

I do it in a roundabout way...

1)Create small database that just runs the query you need it to run. (Attaching all needed tables, etc..)
2)Create 'Autoexec' macro that executes the query.
3)Set up a task in Windows Task Scheduler that opens the database once a month, preferable on a computer that is going to be on when the task needs run.

That help? For more specific help, try more specific questions... https://www.UtterAccess.com/forum/askgoodquestion.html

Posted by: slavescu May 21 2020, 07:11 AM

Thank you Nick, i would like to run the code as i open the database instead , attached to a main form for example and avoid the Task Scheduler

Posted by: nuclear_nick May 21 2020, 07:21 AM

Ahh.. but then you'd have to make sure someone opened the database on the first of every month. And what happens if the query runs twice or more on the first of the month, as people keep opening it to make sure it runs?

It depends on what the query is intended to do, as well.

See... there are several factors to consider, which is why it helps to be as specific as you can.

Posted by: slavescu May 21 2020, 07:28 AM

solved, thank you all for the guidance

Posted by: slavescu May 21 2020, 07:37 AM

Silly me , it was simpler than i thought , (at least i hope so)

If Day(Date) = 1 Then
'run code here
Msgbox "First of the month "
Else
'run code here

End If

Posted by: Jeff B. May 21 2020, 07:39 AM

I'm with <nick> -- why? What does the query do?

More info, please

(I'm asking because there is often more than one way to accomplish something in Access. It would help us help you to know what you are trying to accomplish 'running a query on the first of each month' is a "how", not a "what"...)

Posted by: nuclear_nick May 21 2020, 07:41 AM

And what happens when two users log in at the same time? Are they tring to manipulate the same table? Or is it local?

You could be opening up another can of worms unintentionally. Again, lots of factors. (I, luckily, have a computer running constantly, so these tasks run at 3 in the morning, when no one is around... hopefully... smile.gif )

To each... their own.

Posted by: projecttoday May 21 2020, 07:59 AM

Create a table to store the update date and check that before updating. Also, lock the entire table during the update.

Posted by: slavescu May 21 2020, 08:13 AM

Thanks projectoday, i just did that but also created a field that's checked whenever someone updates it first so it won't run the code for subsequent users .

Posted by: GroverParkGeorge May 21 2020, 09:45 AM

You now have TWO fields to do the same thing, though, don't you? One will work.

If there is a date in the date field, it was run for that date, e.g. 2020/06/01

If the check box is checked, the update was run on that same date, e.g. 2020/06/01.

In other words, the check box is redundant.

Append one new record to the "UpdatesRun" table each time the update runs. Put the date in the date field for that new record. Then you only need to check for the max date in that date to know when the update was last run. The checkbox provides nothing beyond that fact.

Posted by: GroverParkGeorge May 21 2020, 09:46 AM

What I'm suggesting is that you do NOT change the updated date. I'm suggesting you add one new record for each time the update runs. That also provides history as well as the current status.

Posted by: slavescu May 21 2020, 01:05 PM

True, i gather it works better this way, ty

Posted by: slavescu May 23 2020, 04:45 PM

I am posting a function posted by Richard Rost from computer learning, and i'll post it here since it can be adapted in this case and build upon as i did and maybe used by someone else that is looking for the same guidance , so credit to Richard Rost

Public Function LastDayOfMonth(D As Date) As Date

Dim LDOM As Date
Dim NM As Date

NM = DateAdd("m", 1, D)
LDOM = DateSerial(Year(NM), Month(NM), "1")
LDOM = DateAdd("d", -1, LDOM)

LastDayOfMonth = LDOM

End Function

'can be called from qry, timer event, load and so forth LDOM = LastDayOfMonth([Place Valid Date Here])' run code here

Posted by: John Vinson May 23 2020, 05:03 PM

Or more simply (if obscurely), just a calculated field in a Query:

LDOM: DateSerial(Year(Date()), Month(Date()) + 1, 0)

And yes, it works in December, in leap years, anytime. DateSerial is a clever little function.