UtterAccess.com
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
> Automatically Run A Query The 1st Of Every Month, Access 2016    
 
   
slavescu
post May 21 2020, 06:35 AM
Post#1



Posts: 20
Joined: 17-February 19



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
This post has been edited by slavescu: May 21 2020, 07:31 AM
Go to the top of the page
 
nuclear_nick
post May 21 2020, 06:51 AM
Post#2



Posts: 1,872
Joined: 5-February 06
From: Ohio, USA


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... How To Ask Good Questions

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
slavescu
post May 21 2020, 07:11 AM
Post#3



Posts: 20
Joined: 17-February 19



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
Go to the top of the page
 
nuclear_nick
post May 21 2020, 07:21 AM
Post#4



Posts: 1,872
Joined: 5-February 06
From: Ohio, USA


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.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
slavescu
post May 21 2020, 07:28 AM
Post#5



Posts: 20
Joined: 17-February 19



solved, thank you all for the guidance
This post has been edited by slavescu: May 21 2020, 08:15 AM
Go to the top of the page
 
slavescu
post May 21 2020, 07:37 AM
Post#6



Posts: 20
Joined: 17-February 19



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
Go to the top of the page
 
Jeff B.
post May 21 2020, 07:39 AM
Post#7


UtterAccess VIP
Posts: 10,488
Joined: 30-April 10
From: Pacific NorthWet


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"...)

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
nuclear_nick
post May 21 2020, 07:41 AM
Post#8



Posts: 1,872
Joined: 5-February 06
From: Ohio, USA


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.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
projecttoday
post May 21 2020, 07:59 AM
Post#9


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


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

--------------------
Robert Crouser
Go to the top of the page
 
slavescu
post May 21 2020, 08:13 AM
Post#10



Posts: 20
Joined: 17-February 19



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 .
Go to the top of the page
 
GroverParkGeorge
post May 21 2020, 09:45 AM
Post#11


UA Admin
Posts: 37,449
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post May 21 2020, 09:46 AM
Post#12


UA Admin
Posts: 37,449
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
slavescu
post May 21 2020, 01:05 PM
Post#13



Posts: 20
Joined: 17-February 19



True, i gather it works better this way, ty
Go to the top of the page
 
slavescu
post May 23 2020, 04:45 PM
Post#14



Posts: 20
Joined: 17-February 19



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
Go to the top of the page
 
John Vinson
post May 23 2020, 05:03 PM
Post#15


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


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.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    5th July 2020 - 01:16 AM