date

[SJJ via AccessMonster.com]

hi

is there any way to pull up date of monday and wednesday of every week.

for example;
06/03/2006 is monday
08/06/2006 is wednesday and so on.

i want these dates to appear on a form or query. if monday pass then it
should update to the next monday. same with wednesday. how do i have to setup
a table and query for it to work?
can u help please

thanks u very much

 

[Bob Barrows [MVP]]

hi

is there any way to pull up date of monday and wednesday of every
week.

for example;
06/03/2006 is monday
08/06/2006 is wednesday and so on.

i want these dates to appear on a form or query. if monday pass then
it should update to the next monday. same with wednesday. how do i
have to setup a table and query for it to work?
can u help please

Use the weekday function:
.... WHERE weekday([datefield], 1) IN (2,4)

For performance reasons, if you have an index on datefield and you wish for
it to be used, use an inner join to a Calendar table:

CalDate CalWeekDay
2006-03-06 2
2006-03-07 3
2006-03-08 4
....

select ... from datatable d inner join Calendar c
on d.datefield = c.CalDate
WHERE CalWeekDay IN (2,4)

You can use a VBA subroutine to create and populate this table fairly
quickly.
A calendar table will have many more uses for you besides just this query.
For example, you can include fields indicating holidays, working days, etc.

 

[SJJ via AccessMonster.com]

i am sorry the solution is not working may due my lag of understanding.

i used the weekday function and i just got -1 from it.....
this is what i typed weekday (date(),1)IN(2,4)

could u please explain in more detail coz this is new to me.
so please if u can

thanks u very much.

 

[Bob Barrows [MVP]]

i am sorry the solution is not working may due my lag of
understanding.

i used the weekday function and i just got -1 from it.....
this is what i typed weekday (date(),1)IN(2,4)

Your date column's name should be there, not the date() function.

 

[Bob Barrows [MVP]]

i am sorry the solution is not working may due my lag of
understanding.

i used the weekday function and i just got -1 from it.....
this is what i typed weekday (date(),1)IN(2,4)

could u please explain in more detail coz this is new to me.
so please if u can

thanks u very much.

or are you attempting to generate a list of Mondays and Wednesdays?

 

[John Vinson]

is there any way to pull up date of monday and wednesday of every week.

for example;
06/03/2006 is monday
08/06/2006 is wednesday and so on.

i want these dates to appear on a form or query. if monday pass then it
should update to the next monday. same with wednesday. how do i have to setup
a table and query for it to work?
can u help please

Date() returns the current date.

DateAdd("d", 1 - Weekday(Date(), 2), Date())

returns the most recent Monday (today's date if today is Monday).

Change the 2 to a 4 to get the most recent Wednesday.


John W. Vinson[MVP]

 

[SJJ via AccessMonster.com]

are you attempting to generate a list of Mondays and Wednesdays?

yes i am attempting to generate a list of dates of mondays and wednesdays
is it possible

thanks a lot

 

[John Vinson]

yes i am attempting to generate a list of dates of mondays and wednesdays
is it possible

How big a list?

Both the Monday and Wedneday this week?
All of them this year?
All of them for the next century?


John W. Vinson[MVP]

 

[Bob Barrows [MVP]]

are you attempting to generate a list of Mondays and Wednesdays?

yes i am attempting to generate a list of dates of mondays and
wednesdays is it possible

Yes, it is possible. You have several options, the easiest of which is to
create a calendar table as in my earlier reply.
You could also create a VBA procedure to generate this list, perhaps using
arguments to specify the start and end dates for your list.

The choice about which method to use depends on the use to which you wish to
put this list of dates, so perhaps you should provide a little more detail.

 

[SJJ via AccessMonster.com]

i have a condition which is the date must be >date()+7 and < Dateadd("m",2,
date())

so this rage must be maintained throughout.

lets accume todays date is 01/03/2006
so the user can book a date from 09/03/2006 .....01/05/2006. the user can
book any wednesdays in those range

tomoro the date is 02/03/2006
now the range is 10/03/2006..02/05/06 and so on

i need some sort of solution to pick up the mondays and wednesdays dates
falls in those ranges..so i can put these dates in a drop down menu. it has
to be update automatically.

is there any way to do that?

thanks a lot

 

[Bob Barrows [MVP]]

i have a condition which is the date must be >date()+7 and <
Dateadd("m",2, date())

so this rage must be maintained throughout.

lets accume todays date is 01/03/2006
so the user can book a date from 09/03/2006 .....01/05/2006. the user
can book any wednesdays in those range

tomoro the date is 02/03/2006
now the range is 10/03/2006..02/05/06 and so on

i need some sort of solution to pick up the mondays and wednesdays
dates falls in those ranges..so i can put these dates in a drop down
menu. it has to be update automatically.

I still maintain that creating a Calendar table is your best and easiest
solution. By "Calendar table" I mean a table containg two columns: CalDate
and DayOfWeek. It would be child's play to create a query that returns all
the Mondays and Wednesdays within a specific range from such a table:

Select CalDate FROM Calendar
WHERE DayOfWeek IN (2,4) AND
CalDate > date()+7 AND
CalDate < Dateadd("m",2, date()

Do you need help with creating a VBA procedure that would populate this
table for you?

Bob Barrows

 

[SJJ via AccessMonster.com]

yes please. i dont no how to create a calender table

 

[Bob Barrows [MVP]]

yes please. i dont no how to create a calender table

Ok, it's been a while since I've done any DAO stuff, so this should be
interesting ....

To start ... well, manually create the table in Design View (you know how to
do that, right? <grin>).

Two fields:
CalDate - date/time - primary key
DayOfWeek Integer

Save it as "Calendar".
Later on you can add other fields if you wish to track other calendar-type
information: holidays, fiscal periods, etc.

Manually enter the first record. Say: 2006-03-01 with 4 for the DayOfWeek
(Sun=1, Mon=2, etc)

Then create a new module (click into the Modules tab and use the New button)
and paste in this code*:

Option Explicit
Public Sub AddDataToCalendarTable()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim d1 As Date
Dim d As Date
Dim sSQL As String

Set db = CurrentDb
sSQL = "select max(caldate) from calendar"
Set qdf = db.CreateQueryDef("", sSQL)
Set rs = qdf.OpenRecordset
d1 = rs(0) + 1
rs.Close
Set rs = Nothing
sSQL = "insert into calendar (CalDate,DayOfWeek)" & _
"values (pDate,pDay)"
qdf.sql = sSQL
For d = d1 To d1 + 3650
qdf(0) = d
qdf(1) = Weekday(d, vbSunday)
qdf.Execute
Next d
Set qdf = Nothing
Set db = Nothing
End Sub
*this code is tested and worked fine for me

With the cursor in the procedure, press F5 to run it. It will insert 10
years of dates into your table. Then you can use the simple query I posted
last time to generate your list of Mondays and Wednesdays.

Bob Barrows

 

[SJJ via AccessMonster.com]

thank you very much ...it works superb