Date Query

  • Thread starter Thread starter SJJ via AccessMonster.com
  • Start date Start date
S

SJJ via AccessMonster.com

hi

how can i make a query which will automatically run 2nd tuesday of every
month.

Can some one help me on this please.

thanks in advance
 
Dear SJJ:

I recommend creating an Access application that runs this query when it is
opened, then closes. Then just schedule a command line that runs Access on
this database. Of course, the computer has to be ON and booted for this to
work. You can link the necessary table from your existing database.

Tom Ellison
 
hi

how can i make a query which will automatically run 2nd tuesday of every
month.
Click to expand...

I'd use the Windows Scheduler to launch Access on that schedule; you
can include a command-line argument to run a selected macro, which in
turn can run the query:

msaccess.exe "X:\SomePath\yourfile.mdb" /x MyMacro

will execute the macro named MyMacro; this can have a RunQuery action
followed by a Quit.

John W. Vinson[MVP]
 
thank you for the response but this not i wanted.

i have to cheate a query which should run to show the date of 2nd tuesday of
every month. it can be also done by VBA.

can any 1 help me please..

thanks a lot.
 
thank you for the response but this not i wanted.

i have to cheate a query which should run to show the date of 2nd tuesday of
every month. it can be also done by VBA.
Click to expand...

Well, that is NOT what you asked. In your original message you said

how can i make a query which will automatically run 2nd tuesday of
every month.

so that's the question that we answered. You are now asking a
*different* question.

By "every month" you mean... what? the 2nd Tuesday of every month from
now through 9999 AD, the limit of Access date/time fields? All 2nd
Tuesdays this year? The 2nd Tuesday of any selected month?

To get the last of these, try

DateSerial([Enter year:], [Enter month number:],
16-Weekday(DateSerial([Enter year:], [Enter month number:], 1), 3))

in a query.

John W. Vinson[MVP]
 
thanks a lot john. it works :)

one more question please.

how do u run a query for every fortnight on a monday.

thanks in advance.

SJJ
 
thanks a lot john. it works :)

one more question please.

how do u run a query for every fortnight on a monday.
Click to expand...

Use the Windows Scheduler to open Access (set the schedule there). Run
the query from a Macro (named RQ let's say); in the command line in
the scheduler, open the macro with the X command line switch:

"C:\Program Files\Office\msaccess.exe" "K:\path\yourdata.mdb" /x RQ


John W. Vinson[MVP]
 
thanks a lot

Is there any other way to do this, something like the equation given above
for the 2nd tuesday of every month.
i need a formula so i can put into my query Or is it possible to modify the
given equation above?

thanks

SJJ
 
thanks a lot

Is there any other way to do this, something like the equation given above
for the 2nd tuesday of every month.
i need a formula so i can put into my query Or is it possible to modify the
given equation above?
Click to expand...

SJJ, I simply do not understand what it is that you are trying to
accomplish. "A formula so I can put into my query" meaning... what?
You want a criterion for the second Tuesday of the month, you want a
calculated field showing that date, you want... what?

John W. Vinson[MVP]
 
i need to create a query which must shows the date which start from 1st
monday and every fortnight.
i.e. consider this month, the date of 1st monday is 02/01/2006 so the next
date must be 16/01/06 and then 30/01/06 and so on. it must automatically
change every month. that is my question.

is there any way to do this in query. the formula given me perfectly worrks
for the 2nd tuesday.
thanks for that. but i dont no how to do this bit.

any suggestion please.

thanks a lot.

SJJ
 
i need to create a query which must shows the date which start from 1st
monday and every fortnight.
i.e. consider this month, the date of 1st monday is 02/01/2006 so the next
date must be 16/01/06 and then 30/01/06 and so on. it must automatically
change every month. that is my question.

is there any way to do this in query. the formula given me perfectly worrks
for the 2nd tuesday.
thanks for that. but i dont no how to do this bit.
Click to expand...

ok... so there can never be more than three such dates in a month,
correct? You essentially want the dates of the first Monday of
(what... any given month? the current month? next month? the next 500
years of months? you haven't explained that in this entire thread
though I've asked), the third Monday, and (if there is one) the fifth
Monday?

I think this could be done with some VBA, or possibly even a fairly
snarky expression for the third. The first Monday isn't too bad, if
you have a date in the month in question in the field Somedate:

DateSerial(Year([Somedate]), Month([Somedate]), 8 -
Weekday(DateSerial(Year([Somedate]), Month([Somedate]), 1), 2))

and the second fortnight, just use 22 instead of 8.

But you have not answered my basic question, which I guess I have not
ever clearly asked:

WHY?

What is it that you will do with this information when you get it? In
what form do you want it? How will it be used?

John W. Vinson[MVP]
 
ok

i have some hospital equipments, in that equipment list i have few of small
Wheelchairs, big Weelchairs and medium wheelchairs. the small wheel chairs
have to be serviced every 2nd tuesday of every month.
big wheel chair has to be serviced every 3rd tuesday of every month.

and the medium wheelchairs has to be serviced every fortnight from 1st monday
of this month.

i hope this gives u the explanation of what i wanted.

the given formula above gives the dates of 2nd and 3rd tuesday (of course i
modified it). it gets update 1st of every month so i dont have to chance it
every month. so now i need a formula or anything!! which will gives me the
1st of monday and then every fortnight from that date. it must update
automatically so i dont have to chance it every time.

thanks

SJJ
 
Do you mean you want the first monday of the year and then a way to calculate
every succeeding 14 days in the year.

So for this year you need something that will calculate
Jan 2, 16, 30
Feb 13, 27
Mar 13, 27
Apr 10, 24
May 8, 22
etc.

Or do you need
Jan 2, 16, 30
Feb 6, 20
Mar 6, 20
Apr 3, 17
May 1, 15, 29

And do you need that date calculated from some date stored as a last service
date and returning the next expected service date?
 
the given formula above gives the dates of 2nd and 3rd tuesday (of course i
modified it). it gets update 1st of every month so i dont have to chance it
every month. so now i need a formula or anything!! which will gives me the
1st of monday and then every fortnight from that date. it must update
automatically so i dont have to chance it every time.
Click to expand...

I would really suggest creating a Table and fill it with the needed
dates for the next three or four years, and then just use that table.
I can imagine some Tuesdays falling on a holiday and the service
needing to be done the day before or the day after, and the like. It
should take ten minutes' work with a calendar to fill out a
service-date table that you could then just use.

John W. Vinson[MVP]
 
yes, i want date as follows

Jan 2, 16, 30
Feb 13, 27
Mar 13, 27
Apr 10, 24
May 8, 22
etc.


SJJ
 
SJJ said:
yes, i want date as follows

Jan 2, 16, 30
Feb 13, 27
Mar 13, 27
Apr 10, 24
May 8, 22
etc.
Click to expand...
And do you need that date calculated from some date stored as a last service
date and returning the next expected service date?

yes, it would be perfect if u can tell me how to do it please.
 
If the LastServiceDate is already there and is correct, then all you need to
do is add 14 days.

If you need to get the next fortnight day per the example, then the
following may work for you. It is UNTESTED.

DateAdd("d",14 - (DatePart("ww",LastServiceDate7,2) mod 2)
*7,DateAdd("d",1-DatePart("w",LastServiceDate,2),LastServiceDate))
 
Back
Top