By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,140 Members | 1,301 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,140 IT Pros & Developers. It's quick & easy.

Date Functions in Access 2000

P: n/a
Hi Guys

I have recently been learning SQL from a book whose examples used the
Oracle engine. I have now made an Access database containing
information for English Football fixtures. I am using ASP pages and
ADO to access and query this database for information.

One piece of information I want to retrieve is a list of all the
football fixtures for the current week - that is, all the fixtures
from the current day until the next Sunday - the final day in this
week. For instance, if the query was run today, it would return
tonight's fixtures, as well as those for tomorrow (Friday), Saturday
and Sunday. If the query was run on Saturday, the 4th of October that
is, it would just return Saturday and Sunday's fixtures, which would
take it to the end of this current week.

I am using Microsoft Jet to parse the queries and was originally going
to use the NEXT_DAY Oracle function which took a date and a day and
would return the date of the next occurrence of the day specified, for
instance:

NEXT_DAY (#01/10/2003#, "Sunday") would return 05/10/2003, as this is
date of the next Sunday that occurs after the date supplied.

There doesn't seem to be an equivalent function in Access, so I was
wondering if anybody knew how I might be able to perform a similar
sort of query without the use of the NEXT_DAY function.

Cheers

Craig
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
You could use something like this:

Put it into a standard module.

Use vbSunday, vbMonday etc...

Regards

Peter Russell
Function nextday(Vdate, vDay)
'Gets the date of the next occurrence of the given day
' usage: mydate = nextday("01-oct-03",vbwednesday)
'Same day assumes this day next week

Dim b

If Not IsDate(Vdate) Or vDay > 7 Or vDay < 1 Then Exit Function

b = Weekday(Vdate) - vDay

If b < 0 Then
nextday = CDate(Vdate) - b
Else
nextday = CDate(Vdate) - b + 7
End If

End Function
Craig Bates previously wrote:
Hi Guys

I have recently been learning SQL from a book whose examples used the
Oracle engine. I have now made an Access database containing
information for English Football fixtures. I am using ASP pages and
ADO to access and query this database for information.

One piece of information I want to retrieve is a list of all the
football fixtures for the current week - that is, all the fixtures
from the current day until the next Sunday - the final day in this
week. For instance, if the query was run today, it would return
tonight's fixtures, as well as those for tomorrow (Friday), Saturday
and Sunday. If the query was run on Saturday, the 4th of October that
is, it would just return Saturday and Sunday's fixtures, which would
take it to the end of this current week.

I am using Microsoft Jet to parse the queries and was originally going
to use the NEXT_DAY Oracle function which took a date and a day and
would return the date of the next occurrence of the day specified, for
instance:

NEXT_DAY (#01/10/2003#, "Sunday") would return 05/10/2003, as this is
date of the next Sunday that occurs after the date supplied.

There doesn't seem to be an equivalent function in Access, so I was
wondering if anybody knew how I might be able to perform a similar
sort of query without the use of the NEXT_DAY function.

Cheers

Craig


Nov 12 '05 #2

P: n/a
On 2 Oct 2003 06:48:05 -0700, cr*********@hotmail.com (Craig Bates)
wrote:

DateAdd is a close cousin.
-Tom.

Hi Guys

I have recently been learning SQL from a book whose examples used the
Oracle engine. I have now made an Access database containing
information for English Football fixtures. I am using ASP pages and
ADO to access and query this database for information.

One piece of information I want to retrieve is a list of all the
football fixtures for the current week - that is, all the fixtures
from the current day until the next Sunday - the final day in this
week. For instance, if the query was run today, it would return
tonight's fixtures, as well as those for tomorrow (Friday), Saturday
and Sunday. If the query was run on Saturday, the 4th of October that
is, it would just return Saturday and Sunday's fixtures, which would
take it to the end of this current week.

I am using Microsoft Jet to parse the queries and was originally going
to use the NEXT_DAY Oracle function which took a date and a day and
would return the date of the next occurrence of the day specified, for
instance:

NEXT_DAY (#01/10/2003#, "Sunday") would return 05/10/2003, as this is
date of the next Sunday that occurs after the date supplied.

There doesn't seem to be an equivalent function in Access, so I was
wondering if anybody knew how I might be able to perform a similar
sort of query without the use of the NEXT_DAY function.

Cheers

Craig


Nov 12 '05 #3

P: n/a
Craig,
Look up the WeekDay() function in VBA help:

As query criteria use:

Between Date() And (Date()+7-Weekday(Date(),2))

Note that while Sunday is normally considered the 1st day of the week,
in your case it is the last day of the week, so the weekday function
must show Monday (2) as the first day of the week.

Hope this is helpful.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.
"Craig Bates" <cr*********@hotmail.com> wrote in message
news:a0**************************@posting.google.c om...
Hi Guys

I have recently been learning SQL from a book whose examples used the
Oracle engine. I have now made an Access database containing
information for English Football fixtures. I am using ASP pages and
ADO to access and query this database for information.

One piece of information I want to retrieve is a list of all the
football fixtures for the current week - that is, all the fixtures
from the current day until the next Sunday - the final day in this
week. For instance, if the query was run today, it would return
tonight's fixtures, as well as those for tomorrow (Friday), Saturday
and Sunday. If the query was run on Saturday, the 4th of October that
is, it would just return Saturday and Sunday's fixtures, which would
take it to the end of this current week.

I am using Microsoft Jet to parse the queries and was originally going
to use the NEXT_DAY Oracle function which took a date and a day and
would return the date of the next occurrence of the day specified, for
instance:

NEXT_DAY (#01/10/2003#, "Sunday") would return 05/10/2003, as this is
date of the next Sunday that occurs after the date supplied.

There doesn't seem to be an equivalent function in Access, so I was
wondering if anybody knew how I might be able to perform a similar
sort of query without the use of the NEXT_DAY function.

Cheers

Craig

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.