Ok, I'm going to correct a few of these posts because there are a large number of errors:
1) You only need # or single quotes around date values in SQL statements, so 1/1/2001 would require #'s or singlequote's around it, but a function that returns a date (like getdate() in SQL Server) would not require them
2) In the original post, the sample code has another error, in that it does not concatenate the values of the variables into the string:
Code:
SQL="SELECT * FROM table WHERE fieldValue>= [highlight]" & fifteenAgo & "[/highlight] AND fieldValue<=[highlight]" & fifteenFromNow
According to the previous statement, we now need to add #'s or single-quotes like so:
Code:
SQL="SELECT * FROM table WHERE fieldValue>= [highlight]#[/highlight]" & fifteenAgo & "[highlight]#[/highlight] AND fieldValue<=[highlight]#[/highlight]" & fifteenFromNow
[highlight] & "#"[/highlight]
3) I am assuming tblLogins is a table in your database and not a local object in your ASP code, in that case Sheco's code will not work. Your original example may, if Access supports the Now() function (which I believe it does). If you did want to compare the difference between the result of Now() in the ASP code and the data in your table, you would need #/'s because you would be concatenating the return value of Now() into your SQL string.
Code:
[b]Your Original Comparison[/b]
SELECT * FROM TABLE WHERE NOW() - tblLogins.Date<30
[i]This should work as long as you remember that the NOW() function is being called inside access. [/i]
[b]Sheco's[/b]
sSQL = "SELECT * FROM TABLE WHERE " & cStr(NOW() - tblLogins.Date) & " < 30"
[i]This will not work because you don't have access to a tblLogins object in your ASP code[/i]
[b]Using Now() From ASP in your SQL string[/b]
sSQL = "SELECT * FROM TABLE WHERE #" & NOW() & "# - tblLogins.Date < 30"
or
sSQL = "SELECT * FROM TABLE WHERE DateDiff('s',#" & NOW() & "# - tblLogins.Date) < 30"
I just noticed that damber mentioned some of these problems, hopefully the additional information I provided will be useful.
Basically think of it this way. You are building a string to send to MS Access (or whatever database). The syntax of a SQL statement is designed so that the database can easily determine the difference between differant arguments. For example: if you entered a date without anything around it, ie 1/1/2000, then how does the database know whether you are sending it a date or asking it to compute 1 divided by 1 divided by 2000? While it would be possible to make a funciton to get the right one most of the time, it is much more efficient and less prone to error for the system to simply require symbols surrounding the value in certain circumstances.
So raw string/text/character data requires single quotes around it. Raw dates require #'s or single-quotes around them.
One thing that often helps is to print the SQL string out to the screen. That way you will be able to see exactly what your going to send to the database. You can also paste this into Access or Query Analyzer to see how the database will handle it.
In any case, I've gone on long enough, hope some of this helps,
-T
