ms access - sql query to select week,month, days -

please guys, need sql query select data access database using vb6. query provide current date , filed been compaired curr_date in database.

for example

select *    table   curr_date between firstoflastmonth , endoflasthmonth;  select *    table   curr_date between firstoflastweek , endoflasthweek;  select *    table   curr_date between firstofthismonth , endofthismonth;  select *    table   curr_date between firstofthsiweek , tilldate; 

the problem have figuring ot how specify 2 dates in comparison

is not easiest check wich month/week want in then?

from examples:

select *    table    curr_date between firstoflastmonth , endoflasthmonth;   

this become (with month function used)

select *    table    month(curr_date) = month(dateadd("m",-1,date())); 

this compares month of curr_date month of date (the current date) minus 1 month, last month. can done using datediff:

select *    table    datediff ( "m", curr_date, date()) = 1 , curr_date < date(); 

the last line added make sure dates 1 month later not selected.

same thing can applied other queries using date function found here

links used reference sheet provided richard calahan