p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

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




  Return to Index