select * where DATE_acc = the last 3 days back

[sal21]

I have in a column of access table a filed named Date_acc is formatted date.
how to select by * but only all records have in Date_acc the value of the last 3 day back refered today date...
Eaxple for today:

select * only if in Date_acc are present 13/05/2010,12/05/2010,11/05/2010

[HansV]

SELECT * FROM MyTable WHERE Date_acc > Date()-3

will return records from the last 3 days (including today).

[sal21]

TKS!
If i have understand Date_acc > Date()-30 set the filter to 12/04/2010, or not?

[HansV]

Well, for today (13 May 2010), the expression Date()-30 returns 13 April 2010, not 12 April 2010. If you always want to get records from the last month, whether the month has 28, 29, 30 or 31 days, you can use

... WHERE Date_acc > DateAdd("m",-1,Date())

[sal21]

Well, for today (13 May 2010), the expression Date()-30 returns 13 April 2010, not 12 April 2010. If you always want to get records from the last month, whether the month has 28, 29, 30 or 31 days, you can use

... WHERE Date_acc > DateAdd("m",-1,Date())

Hi Hans i have insert the value of date in a var....

Code: Select all

dim  DATA_ACCENSIONE  as date
   DATA_ACCENSIONE = DateAdd("m", -1, Date)
   
   Set RSD = DB.OpenRecordset("Select * from DBO WHERE [DATA_ACC] > '" & DATA_ACCENSIONE & "' AND TIPO='" & TEST_COUNT & "'", dbOpenDynaset, dbReadOnly)

have error, peraph insert the param DATA_ACCENSIONE in other composition???

Note:
The field DATA_ACC is formatted Date/Time and the database is a Sql Server Table

[HansV]

Try

Code: Select all

    Set RSD = DB.OpenRecordset("Select * from DBO WHERE [DATA_ACC] > #" & Format(DATA_ACCENSIONE, "mm/dd/yyyy") & _
        "# AND TIPO='" & TEST_COUNT & "'", dbOpenDynaset, dbReadOnly)

You're suddenly using DAO now? I thought you always used ADODB...

[sal21]

Try

Code: Select all

    Set RSD = DB.OpenRecordset("Select * from DBO WHERE [DATA_ACC] > #" & Format(DATA_ACCENSIONE, "mm/dd/yyyy") & _
        "# AND TIPO='" & TEST_COUNT & "'", dbOpenDynaset, dbReadOnly)

You're suddenly using DAO now? I thought you always used ADODB...

No... i use ADODB, i test the code in DAO:-)

In other case the sintiax is the same?

[HansV]

The SQL remains the same.

[sal21]

The SQL remains the same.

ok!
Tks.