ASP/SQL/Access DB - compare dates

[pom69]

I'm a novice to ASP programming. I have an Microsoft Access DB that has a date field (e.g. 8/9/2005) for each record. I want to write an SQL statement that only returns records for the past 7 days. I am accustom to written statements for DB2 access, such as:

select * from mytable where date(mydate) > date(current timestamp - 7 days)

[or something similar]

Can anyone help me here? Thank you.

[Bullschmidt]

Hi, and welcome to the board!

1) Example as you requested:

BeginDate = DateAdd("D", -7, Date())
EndDate = Date()
strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & BeginDate & "#) AND (TheDateField <= #" & EndDate & "#)"

2) Example setting a date range based on posted fields:

strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & Request.Form("MinDate") & "#) AND (TheDateField <= #" & Request.Form("MaxDate") & "#)"

3) Example setting a date range to be the current and previous months:

strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & DateSerial(Year(Date()), Month(Date()) + 1, 0) & "#) AND (TheDateField <= #" & DateSerial(Year(Date()), Month(Date()) - 1, 1) & "#)"