Dates are VERY different between Access and SQL Server.
The # symbol won't work in Sql server, so forget that.
The datetime field in SQL Server captures time as well as date information too, so with your queries watch out for this, as the query below will only match those dates which are '1/01/2004 00:00:00'
WHERE testdb.datefield = '1/01/2004'
Its easiest to use a date range using the function BEWTEEN or you can be clever and convert to integers using floor function as described in several help docs, but keeping it simple is usually the best way.
Reading all day, I figured out the 'date' vs #date# difference (and a bit more, too), but could not figure out why my test sql queries run from the VB data environment using the Access syntax were returning valid data sets from the sql server, while the vb code was not (I quess the VB data environment must be doing a sql query translation when it calls jet vs sql.)