Selecting a date range in ASP

[kevinj]

I am working with an Access 2000 db and ASP. I'm trying to select records that fall within an inputted date range. I believe my problem is that the DatePaid field in my db includes the time as well as the date and so my select statement is not picking up records that fall on the inputted BeginningDate or EndDate. My select statement is as follows:

Set RS = DB.execute("SELECT * FROM tblmember WHERE DatePaid > =#"&BeginningDate&"# AND DatePaid <= #"&EndDate&"# ORDER BY LastName ASC")

I thought about setting BeginningDate to the day before the actual inputted date but didn't know how to do that.
Any help would be greatly appreciated.

[WebJoe]

I think

...WHERE DatePaid > =Date()-1 AND...

could help you

[duckhunter]

Try replacing your #'s with single quotes:

strSQL = "SELECT * FROM tblmember WHERE DatePaid >= '" & BeginningDate & "' AND DatePaid <= '" & EndDate & "' ORDER BY LastName ASC"

Set RS = DB.execute(strSQL)

[mattglet]

don't you have to use # when using dates in Access?

-Matt

[WebJoe]

I think mattglett is right: If you have a variant or string that should be compared to a date you have to use # in M$Access (which ist similar to the TO_DATE functio in Oracle).

Sorry 'bout my answer before, I didn't read your question right.
Does

NewDate = Format$((DateAdd("d", 1, Date(BeginningDate)), "mm/dd/yyyy")
...WHERE DatePaid > =#" & NewDate & "# AND...

help? Here the form value (which BeginningDate is, I assumed) gets convertet to a date-type, one day gets added and the whole thing convertet back to a string (for the SQL command)

I haven't tried this in ASP but it's the approach I would take in VB

[mattglet]

just throwing out ideas that i think of:

you might need some parentheses in your sql statement too...

Set RS = DB.execute("SELECT * FROM tblmember WHERE (DatePaid > =#"&BeginningDate&"#) AND (DatePaid <= #"&EndDate&"#) ORDER BY LastName ASC")

it's always good to keep your order of operation obvious (both to you, and the compiler)

and honestly, the time doesn't matter when comparing dates. you just need to make sure both your strings are in valid date format, so the comparison will definitely be of the same datatype.

-Matt

[abulhallaj]

If you get an error on DATE, it's maybe of your regional setting

[abulhallaj]

You can solve it with this code

'Set the date time format to your own if you are getting a CDATE error

Session.LCID = 1033

[aspdaddy]

Yes you need the # signs in ASP/Access,
IMO its best to format the input dates as
dd-mon-yyyy because of various problems interpreting them.

Some date info for ASP/Access -
http*//www.aspfaq.com/show.asp?id=2040