Re: SQL Compare Date Fields using Where
- From: "Bob Barrows" <reb01501@xxxxxxxxxxxxxxx>
- Date: Thu, 19 Feb 2009 08:03:10 -0500
Antonette wrote:
I still cannot get it to work.
I changed some things around trying to guess at why it does not like
it.
Here is my current code.
WorkDate = DateAdd("d",+ 21,Date())%>
CompDate = CStr(Month(WorkDate)) & "-" & CStr(Day(WorkDate)) & "-" &
CStr(Year(WorkDate))
<%rsSatAct.Open "SELECT * FROM DAEvents WHERE FnctnDate < # " &
CompDate & " # ORDER BY FnctnDate", cnnct, 3, 3
Error I receive is :
Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query
expression 'FnctnDate < # #'.
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/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:
Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.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"
.
- References:
- SQL Compare Date Fields using Where
- From: Antonette
- Re: SQL Compare Date Fields using Where
- From: Richard Mueller [MVP]
- Re: SQL Compare Date Fields using Where
- From: Antonette
- Re: SQL Compare Date Fields using Where
- From: Tom Lavedas
- Re: SQL Compare Date Fields using Where
- From: Antonette
- SQL Compare Date Fields using Where
- Prev by Date: how to paste a text file to word with vba
- Next by Date: Re: I would like to use Win32_LogonSession but it is confusing to me.
- Previous by thread: Re: SQL Compare Date Fields using Where
- Next by thread: Re: SQL Compare Date Fields using Where
- Index(es):
Relevant Pages
|
