Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pyrrhus_finch
Yak Posting Veteran
51 Posts |
 Posted - 2004-12-16 : 13:20:11
|
| Im sure this has been posted before but the search function on the site seem to be down today:I want to Select all records before the current month.What's the best way to do this in Access. I did this:Select * from CAMPERS WHERE(YEAR([DATE]) < year(now()))OR (Month([DATE])<= month(now())-1);but think there must be a simpler syntax.Thanks a bunch. Anyone know? Please... : ) |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-16 : 13:55:42
|
quote:
Select * from CAMPERS WHERE(YEAR([DATE]) < year(now()))OR (Month([DATE])<= month(now())-1);
that doesn't return correct results, FYI.use:WHERE Date < DateSerial(Year(Now()), Month(now()),1)- Jeff |
 |
|
|
pyrrhus_finch
Yak Posting Veteran
51 Posts |
Posted - 2004-12-16 : 14:23:35
|
| Thanks - much easier.I'd like to understand:Why wouldn't it return the correct result?selects records for previous years and one less than the current month?no? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-16 : 14:36:28
|
| did you try it and look at the results?consider: current year-month of 2003-06condition: (YEAR([DATE]) < year(now())) OR (Month([DATE])<= month(now())-1);will a record on 8/1/2003 be returned or not, based on your condition?- Jeff |
|
|
|
pyrrhus_finch
Yak Posting Veteran
51 Posts |
Posted - 2004-12-16 : 14:43:53
|
| ahh. thanks. my dataset is so small right now i didn't see that. thanks for pointing it out. |
|
|
|
pyrrhus_finch
Yak Posting Veteran
51 Posts |
Posted - 2004-12-16 : 15:06:24
|
| stumped again?so.. how would i select only the records for the previous month?i started with... but its absurd now that I think about it.select * from Campers where (Month([DATE])=month(now())-1)ugghh. |
|
|
|
pyrrhus_finch
Yak Posting Veteran
51 Posts |
Posted - 2004-12-16 : 15:25:03
|
| WHERE DateSerial(Year([DATE]), Month([DATE]),1) = DateSerial(Year(Now()),(Month(now()) -1),1)That seems to work but looks like it should be easier. |
|
|
|
|
|
|
Select all records before current month.