Jake,

Previous posters have shown where your code is incorrect, but there is
another issue you bring up as well regarding using only the date component
of the datetime value in the comparison.

Transact SQL has a set of date functions that should be used when
manipulating datetime values.  To use it, your code would be:

sql = "SELECT * FROM vacancies "
sql = sql & "WHERE DateDiff(dd, GetDate(), '" & Format(vacExpireDate,
"MM/DD/YYYY") & "') > 0"

Note the usage of the GetDate() function, which will return the current
datetime value from the server.  The DateDiff() function will return the
number of days between the two dates (the "dd" as the first param).

--
Jeff Wilson
President
The Boolean Group, Inc.
(xxx) xxx-xxxx
Glendale, CA




"Jake C" <jakeone@b...> wrote in message
news:64977@s..._language...
>
> I have a field, vacExpireDate, of varchar datatype whose values are of the
> format, dd/mm/yy (no time component). I wish to convert these values to a
> date for comparison purposes.
>
> I've tried:
> sql = "SELECT * FROM vacancies "
> sql = sql & "WHERE" & cdate(vacExpireDate) & " > " & "'" & now() & "'"
>
> and I've also tried:
> sql = "SELECT * FROM vacancies WHERE "
> sql = sql & "vacExpireDate =" & convert(char(20), vacExpireDate, 103)
> sql = sql & " > " & "'" & now() & "'"
>
> ... but the conversion either fails to work or it tries to use the time
> component to perform the comparison. What should I be doing instead?
>
> Thanks for any help.
>
> Jake
>
>