Selecting a week

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

Hello,

I have a table with a date field. I want to query one week
at a time. Right now I'm using a "Between...And" function
where the user has to enter two dates. Is there any way to
allow them to just enter the first date of the week - Or
even more ideal, any date within the week - and have the
results show records from the whole week?

Thanks,

Marc
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Depends on what you consider a week: Mon-Fri or Sun-Sat or something
else. If Sun-Sat you could have a WHERE clause like this:

PARAMETERS Forms!FormName!DateField DateTime;
SELECT ...
FROM ...
WHERE DateField Between (Forms!FormName!DateField -
WeekDay(Forms!FormName!DateField) + 1)
AND Forms!FormName!DateField +
(7-(WeekDay(Forms!FormName!DateField) Mod 7))


This criteria will give you the Sun & Sat dates that bracket the date
in Forms!FormName!DateField.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQBGMPIechKqOuFEgEQIDPQCfdHuS5f4NT/Oi78LFQ8uM/8Lg9G8Amwdk
etn2JnohIlIQ4Vp4Z67+y1f0
=P9R6
-----END PGP SIGNATURE-----
 
Hello,

I have a table with a date field. I want to query one week
at a time. Right now I'm using a "Between...And" function
where the user has to enter two dates. Is there any way to
allow them to just enter the first date of the week - Or
even more ideal, any date within the week - and have the
results show records from the whole week?

Thanks,

Marc
Click to expand...

Try:

BETWEEN DateAdd("d", 1 - Weekday(Date()), Date()) AND DateAdd("d", 8 -
Weekday(Date()), Date())

Weekday(Date()) will return a number from 1 (Sunday) through 7
(Saturday); DateAdd will subtract or add enough days to give you a
range from last Sunday to next Sunday on any date.
 
-----Original Message-----


Try:

BETWEEN DateAdd("d", 1 - Weekday(Date()), Date()) AND DateAdd("d", 8 -
Weekday(Date()), Date())

Weekday(Date()) will return a number from 1 (Sunday) through 7
(Saturday); DateAdd will subtract or add enough days to give you a
range from last Sunday to next Sunday on any date.


.
Thanks John,
Click to expand...

Marc
 
Thanks,

Marc
-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Depends on what you consider a week: Mon-Fri or Sun-Sat or something
else. If Sun-Sat you could have a WHERE clause like this:

PARAMETERS Forms!FormName!DateField DateTime;
SELECT ...
FROM ...
WHERE DateField Between (Forms!FormName!DateField -
WeekDay(Forms!FormName!DateField) + 1)
AND Forms!FormName!DateField +
(7-(WeekDay(Forms!FormName!DateField) Mod 7))


This criteria will give you the Sun & Sat dates that bracket the date
in Forms!FormName!DateField.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQBGMPIechKqOuFEgEQIDPQCfdHuS5f4NT/Oi78LFQ8uM/8Lg9G 8Amwdk
etn2JnohIlIQ4Vp4Z67+y1f0
=P9R6
-----END PGP SIGNATURE-----



.
Click to expand...
 
Back
Top