Vista - SQL Compare Date Fields using Where

I am ahving trouble finding the correct syntax for comparing date fields.

What I have is:

CompDate = DateAdd("d", +21,Date)

<%rsSatAct.Open "SELECT * FROM DAEvents WHERE Date < #'" & CompDate & "'#
ORDER BY Date", cnnct, 3, 3

I want to find all the students who are to be tested in the next 21 days.

Date is the Test Date in the Students Table

Thanks in Advance

Toni

On Feb 18, 3:00*pm, Antonette <Antone...@xxxxxx>
wrote:{snip}Is this in an ASP? The <% in the query line of code seems to imply
that it is. In that case, the code outside of the <% %> tags is not
in the same context as the query. I know very little about ASP, but
that seems like a real problem to me. Plus, the error message seems
to be saying that the variable CompDate is empty when the query string
is evaluated. That would seem to reinforce my suspicions.

If this is in an ASP, maybe you should seek out a more compatible
group as this one tends to cover standalone VBS applications and, to a
lesser extent, client side scripting in HTMLs or HTAs. I think the
ASP expertise is a little thin.

Tom Lavedas
***********
http://there.is.no.more/tglbatch/

Antonette wrote:You cannot debug syntax issues with sql statements without knowing what the
sql statement is. Your first step with these issues should ALWAYS be:
display the sql statement constructed by your code. To make this simpler,
good practices include:
1. assign the sql statement being constructed to a variable. Like this:
sql="SELECT * FROM DAEvents WHERE FnctnDate < # " & _
CompDate & " # ORDER BY FnctnDate"

2. display the resulting sql statment. In your case, you are using asp, so
use response.write:

Response.Write sql & "<BR>"

This enables you to, if the syntax error still escapes you, copy the sql
statement to the sql view of the Access Query Builder and test it. You may
get a better error message that will allow you to see the error more
clearly.

3. use the sql variable in your Open statement:
rsSatAct.Open sql,cnnct,3,3

Jet (Access) requires dates to be in one of two formats:
US format: m/d/yyyy (note the slashes rather than the dashes)
or (better)
ISO format: yyyy-mm-dd

The second is preferred because it is less ambiguous. I have never seen
anyone express a date as yyyy-dd-mm so there is no chance that a date
expressed as 2009-02-06 will ever be misinterpreted as June 2, 2009.

Lastly:
Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/...e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl




--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

In microsoft.public.scripting.vbscript message <uNMu3LekJHA.2460@xxxxxx
NGP06.phx.gbl>, Wed, 18 Feb 2009 10:07:08, "Richard Mueller [MVP]"
<rlmueller-nospam@xxxxxx> posted:It might be necessary (and would be if the dates were compared as
strings) for Month & Day to have a leading zero if less than ten.

--
(c) John Stockton, nr London, UK. ?@merlyn.demon.co.uk Turnpike v6.05.
Web <URL:http://www.merlyn.demon.co.uk/> - w. FAQish topics, links, acronyms
PAS EXE etc : <URL:http://www.merlyn.demon.co.uk/programs/> - see 00index.htm
Dates - miscdate.htm moredate.htm js-dates.htm pas-time.htm critdate.htm etc.