|
 |
access thread: finding month in range of dates.
Message #1 by "Mitchell Adams" <MADAMS@p...> on Tue, 18 Sep 2001 09:22:11 -0400
|
|
I've got two columns in my table, a Start Date and an End Date.
What I need to do is submit a month as a parameter and see if it falls
between the start and end dates.
How would I use BETWEEN in this instance?
Example:
SELECT * FROM table WHERE [month] BETWEEN Column1 AND Column2;
I don't know the actual dates otherwise I'd go ahead and use #01/01/2001#
to search.
Any Ideas?
Thanks
Mitch
Mitchell L. Adams
Webmaster
Message #2 by brian.skelton@b... on Tue, 18 Sep 2001 14:59:06
|
|
Hi Mitch
You just need to ensure that you're comparing like with like. In this case
I'd use month numbers:
SELECT *
FROM table
WHERE format([month],"m") BETWEEN format(Column1,"m") AND format
(Column2,"m")
It should work...
-BDS
> I've got two columns in my table, a Start Date and an End Date.
>
> What I need to do is submit a month as a parameter and see if it falls
> between the start and end dates.
>
> How would I use BETWEEN in this instance?
>
> Example:
> SELECT * FROM table WHERE [month] BETWEEN Column1 AND Column2;
>
> I don't know the actual dates otherwise I'd go ahead and use
#01/01/2001#
> to search.
>
> Any Ideas?
>
> Thanks
>
> Mitch
>
>
>
> Mitchell L. Adams
> Webmaster
Message #3 by "Mitchell Adams" <MADAMS@p...> on Tue, 18 Sep 2001 15:59:40 -0400
|
|
Brian,
Thanks for the help. It's still not going though.
Here's the statement:
PARAMETERS [Month] Text ( 255 );
SELECT *
FROM Events
WHERE Format([Month], "mm")
BETWEEN format(StartDate, "mm") AND Format(EndDate, "mm");
When I do a search for say "06", it fails to bring up the correct data.
Any ideas? Any online resources you could suggest. I've looked over
microsoft.com....
Thanks again
Mitch
Mitchell L. Adams
Webmaster
xxx-xxx-xxxx
Palm Beach County Convention and Visitors Bureau
http://www.palmbeachfl.com
Keyword: CVB
>>> brian.skelton@b... 09/18/01 02:59PM >>>
Hi Mitch
You just need to ensure that you're comparing like with like. In this
case
I'd use month numbers:
SELECT *
FROM table
WHERE format([month],"m") BETWEEN format(Column1,"m") AND format
(Column2,"m")
It should work...
-BDS
> I've got two columns in my table, a Start Date and an End Date.
>
> What I need to do is submit a month as a parameter and see if it
falls
> between the start and end dates.
>
> How would I use BETWEEN in this instance?
>
> Example:
> SELECT * FROM table WHERE [month] BETWEEN Column1 AND Column2;
>
> I don't know the actual dates otherwise I'd go ahead and use
#01/01/2001#
> to search.
>
> Any Ideas?
>
> Thanks
>
> Mitch
>
>
>
> Mitchell L. Adams
> Webmaster
Message #4 by "Pardee, Roy E" <roy.e.pardee@l...> on Tue, 18 Sep 2001 13:23:18 -0700
|
|
Format() returns a variant of subtype string, which may be making for
strange comparisons. Try either converting this to an integer (e.g.,
CInt(Format())) or using the Month() function instead--that returns a
variant of subtype integer & should handle the BETWEEN comparison more
gracefully. So your query might be
PARAMETERS [Month] SMALLINT ;
SELECT *
FROM Events
WHERE [Month]
BETWEEN Month(StartDate) AND Month(EndDate);
HTH,
-Roy
-----Original Message-----
From: Mitchell Adams [mailto:MADAMS@p...]
Sent: Tuesday, September 18, 2001 1:00 PM
To: Access
Subject: [access] Re: finding month in range of dates.
Brian,
Thanks for the help. It's still not going though.
Here's the statement:
PARAMETERS [Month] Text ( 255 );
SELECT *
FROM Events
WHERE Format([Month], "mm")
BETWEEN format(StartDate, "mm") AND Format(EndDate, "mm");
When I do a search for say "06", it fails to bring up the correct data.
Any ideas? Any online resources you could suggest. I've looked over
microsoft.com....
Thanks again
Mitch
Mitchell L. Adams
Webmaster
xxx-xxx-xxxx
Palm Beach County Convention and Visitors Bureau
http://www.palmbeachfl.com
Keyword: CVB
>>> brian.skelton@b... 09/18/01 02:59PM >>>
Hi Mitch
You just need to ensure that you're comparing like with like. In this case
I'd use month numbers:
SELECT *
FROM table
WHERE format([month],"m") BETWEEN format(Column1,"m") AND format
(Column2,"m")
It should work...
-BDS
> I've got two columns in my table, a Start Date and an End Date.
>
> What I need to do is submit a month as a parameter and see if it falls
> between the start and end dates.
>
> How would I use BETWEEN in this instance?
>
> Example:
> SELECT * FROM table WHERE [month] BETWEEN Column1 AND Column2;
>
> I don't know the actual dates otherwise I'd go ahead and use
#01/01/2001#
> to search.
>
> Any Ideas?
>
> Thanks
>
> Mitch
>
>
>
> Mitchell L. Adams
> Webmaster
|
|
|
View Cart
