Date Functions in Access 2000

[Craig Bates]

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

[Peter Russell]

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

[Tom van Stiphout]

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

[Fredg]

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