SQL works on local - not on server

  • Thread starter Thread starter Dommett
  • Start date Start date
D

Dommett

I have a database results form that uses the following SQL
statement:
SELECT * FROM Main WHERE ("Start Date" <= Date()
AND "Expiration Date" > Date()) ORDER BY "Company Name" ASC

This all works fine under my IIS server but when I publish
to a hosting site in Canada the statement doesn't work. I
only get results if I say "Start Date" > Date()! And the
results are never ASC. I kept wondering if there is
something happening with the way dates are stored in
Canada vs. US but wouldn't that be universal? FYI - I'm
using FrontPage 2000 and Access database.

Any help appreciated.
 
First, are you sure you pasted the SQL statement into
your message correctly? Like, do you actually mean
[Start Date] <= Date()
or
'Start Date' <= Date()

'Start Date' is clearly incorrect because it's a string
literal. You're comparing dates like '12/05/2002' to the
string 'Start Date'.

[Start Date] implies a database field, but what format?
If it doesn't use the database's native Date/Time field,
conmparisons won't work correctly. For example, the
string '12/5/2002' will test greater than the
string '11/30/2002'.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
|| Microsoft FrontPage Version 2002 Inside Out
|| Web Database Development Step by Step .NET Edition
|| Troubleshooting Microsoft FrontPage 2002
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------
 
Back
Top