Select statement based on date field in db?

[kevinj]

I have a db that has a field called expiration_date. I need to select all records from the db whose expiration_date is within 1 month of the current date.

So far I have:

rs.open "select * from tblmembership where isFinished=yes AND expiration_date <= #" & expiring_date & "#",db,2

The expiring_date is what I'm not sure how to code.

Thanks for any help!
Kevin

[Krapulator]

Try:

select * from tblmembership where isFinished=yes AND expiration_date between dateadd(month, -1, getdate()) and getdate()

[kevinj]

Thanks Krapulator. I tried it and wasn't able to get it to work. I did get it to work with this.

rs.open "select * from tblmembership where expiration_date <= #" & month(date)+1 & "/1/" & year(date) & "# and expiration_date >=#" & date() & "#",db,2

Kevin

[Krapulator]

Sorry, I should have said that the code I was providing was for SQL-Server.

[duckhunter]

The text field value 'yes' will need to be in single quotes

select * from tblmembership where isFinished='yes' AND expiration_date between dateadd(month, -1, getdate()) and getdate()

Assuming isFinished is a varChar. If it's a Char, you might need RTRIM(isFinished) = 'yes'