Selecting > today's date 2

[JrClown]

I'm trying to display a query from my table only showing date greater than today

[/b]this does not work[/b]
sql = "Select * from results where thrudate > sysdate QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[RoadRacer]

We meet again JR! =)

Try this:

sql = "SELECT * FROM results WHERE thrudate > #" & Date() & "#"

That works in Access, but if you're using SQL, change the # to the ever famous '

Hope this helps. Ed (RoadRacer) Holguin

&quot;I Hate Computers!&quot;

 

[JrClown]

hello RoadRacer, I was hoping Ito hear from such an expert as yourself bro. I will try that first thing in the morning. Thanks in advance.
I tried it with the # signs but I remember to have place them inside ' ticks. QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[JrClown]

It worked perfect bro, tyvvm again. QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[JrClown]

I spoke too soon RoadRacer,
Now I'm having a problem with this syntax

sql = sql & &quot; where department = ' &quot; & reqdeptsearch & &quot;' & thrudate > #&quot; & Date() & &quot;#&quot; order by department&quot;

Would you mind showing me the proper syntax please? QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[JrClown]

here the select statement I get an error on:

reqdeptsearch = Request(&quot;deptsearch&quot
sql = &quot;Select * from vacrequest&quot;
sql = sql & &quot; where department = ' &quot; & reqdeptsearch & &quot;' & thrudate > #&quot; & Date() & &quot;#&quot; order by department&quot;
QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[RobV]

It's quite useless to order the results by department if you're only requesting the records which match a particular department.

Maybe you should use

sql = sql & " where thrudate > Date()"

(for Access which understands the date function)

or

sql = sql & " where thrudate > GetDate()"

(for SQL\Server which understands the getdate function)

Hope this helps. Yours,

Rob.

 

[JrClown]

Thanks RobV,
I'm now having a problem with inserting thrudate > #&quot; & Date() & &quot;#&quot; into my statement.
What you said made perfect sense, why should I sort by the selected department... Thanks


What I'd like to do now is select from the department BUT only show those dates greater that today:

can I do two WHERE's within my statement? or how does it work?

sql = sql & &quot; where department = ' &quot; & reqdeptsearch & &quot;' order by thrudate&quot;

Thanks for your help. QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[RoadRacer]

Oof! Sorry, I wasn't able to get back to you sooner, just got a free moment in my morning. =)

I got a bit lost reading through the threads, I think that Pastrami sammich I had is affecting me. =D

Seriously, if I understood what you're trying to do, this should do the trick ...

sql = "SELECT * " &_
      "FROM vacrequest " &_
      "WHERE department = '" & reqdeptsearch & "' " &_
      "AND thrudate > #" & Date() & "# " &_
      "ORDER BY thrudate"

Let me know how it works ... Ed (RoadRacer) Holguin

&quot;I Hate Computers!&quot;

 

[JrClown]

Thanks Ed, you made my day again bro. It was not working at first then I noticed that my Department search field was not left justified so the syntax was displaying nothing.

I kept searching form Accounting instead of
Accounting

I typed a [/b]space[/b] below:
sql = &quot;SELECT * &quot; &_
&quot;FROM vacrequest &quot; &_
&quot;WHERE department = '[/b]space[/b] &quot; & reqdeptsearch & &quot;' &quot; &_
&quot;AND thrudate > #&quot; & Date() & &quot;# &quot; &_
&quot;ORDER BY thrudate&quot;

And fixed my dumbness LOL QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[RoadRacer]

WoW! I actually messed you up there then...hehe

I had seen that space and wondered why it was there, didn't think it was necessary, so I removed it ... BAD ROADRACER!

Glad it worked bro ... take care.
Ed (RoadRacer) Holguin

&quot;I Hate Computers!&quot;

 

[RobV]

You can also use the RTrim and LTrim functions of Sql Server to remove the extraneus spaces:

sql = "SELECT * " &_
      "FROM vacrequest " &_
      "WHERE LTrim(RTrim(department)) = '" _
                      & reqdeptsearch & "' " &_
      "AND thrudate > #" & Date() & "# " &_
      "ORDER BY thrudate"

Yours,

Rob.