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
select * where DATE_acc = the last 3 days back
-
- PlatinumLounger
- Posts: 4366
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78580
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: select * where DATE_acc = the last 3 days back
SELECT * FROM MyTable WHERE Date_acc > Date()-3
will return records from the last 3 days (including today).
will return records from the last 3 days (including today).
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4366
- Joined: 26 Apr 2010, 17:36
Re: select * where DATE_acc = the last 3 days back
TKS!
If i have understand Date_acc > Date()-30 set the filter to 12/04/2010, or not?
If i have understand Date_acc > Date()-30 set the filter to 12/04/2010, or not?
-
- Administrator
- Posts: 78580
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: select * where DATE_acc = the last 3 days back
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())
... WHERE Date_acc > DateAdd("m",-1,Date())
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4366
- Joined: 26 Apr 2010, 17:36
Re: select * where DATE_acc = the last 3 days back
Hi Hans i have insert the value of date in a var....HansV wrote: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())
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)
Note:
The field DATA_ACC is formatted Date/Time and the database is a Sql Server Table
-
- Administrator
- Posts: 78580
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: select * where DATE_acc = the last 3 days back
Try
You're suddenly using DAO now? I thought you always used ADODB...
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)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4366
- Joined: 26 Apr 2010, 17:36
Re: select * where DATE_acc = the last 3 days back
No... i use ADODB, i test the code in DAO:-)HansV wrote:Try
You're suddenly using DAO now? I thought you always used ADODB...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)
In other case the sintiax is the same?
-
- Administrator
- Posts: 78580
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 4366
- Joined: 26 Apr 2010, 17:36
Re: select * where DATE_acc = the last 3 days back
ok!HansV wrote:The SQL remains the same.
Tks.