SQL Statement Not Working Correctly

Results 1 to 2 of 2

Thread: SQL Statement Not Working Correctly

  1. #1
    Join Date
    Dec 1969

    Default SQL Statement Not Working Correctly

    I having been trying to create a asp page that will run a query based upon a ID, and two dates (start date and end date), but when I try getting a weeks amount of data, I end up with 3 months. <BR><BR>The statement is as follows: <BR><BR>"SELECT * FROM Current3 WHERE CDATE(RTUPoll) BETWEEN &#039;"& Date1 & "&#039; AND &#039;" & CurrentDate & "&#039; AND ID = &#039;" & WID & "&#039; " <BR><BR>Current3 is a Recordset in an Access Database, Date1 and CurrentDate are variables I am pulling in, ID is from the database, WID is a form variables I am pulling in and RTUPoll is a recordset from the database. <BR><BR>Does anyone have any clue why this is now working???

  2. #2
    Join Date
    Dec 1969

    Default Not a valid Access query

    You MUST put #...# around dates in Access queries, not &#039;...&#039;.<BR><BR>No idea why this didn&#039;t cause SQL to barf on your feet with a nasty error. <BR><BR>OH! Yes, I do see it!<BR><BR>Because you used a function in there: CDATE(RTUPoll) !!<BR><BR>That meant that Access felt free to *further* convert that value, as it thought it needed to.<BR><BR>Since you are asking Access to compare that expression vs. two *STRINGS* (&#039;...&#039; is *always* a string in Access!), it then did the equivalent of CSTR(CDATE(RTUPoll)) and did *string* comparisons!<BR><BR>I betcha if we analyzed those strings, we could see why you got 3 months of data!<BR><BR>WHY are you doing CDATE in the first place??? If that field is not *already* a DateTime field, why isn&#039;t it??? <BR><BR>Incidentally, did you know you can use the keyword/function DATE() in Access to get the current date?<BR><BR>So...to simplify and correct...<BR><BR>"SELECT * FROM Current3 WHERE RTUPoll BETWEEN #"& Date1 & "# AND DATE()AND ID = &#039;" & WID & "&#039; " <BR><BR>For that matter, if Date1 is 3 weeks prior to today, you can do *that* in Access, as well:<BR><BR>"SELECT * FROM Current3 WHERE RTUPoll BETWEEN DateDiff(&#039;d&#039;,-21,Date()) AND Date() AND ID = &#039;" & WID & "&#039; " <BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts