Date Filter not working

[scotttom]

OK this should be easy.....

SELECT vProjection.Date, vProjection.Project, vProjection.Term
FROM vProjection
WHERE (((vProjection.Date)<#5/30/2008#) AND ((vProjection.Term) Is Null))
ORDER BY vProjection.Date DESC;

I'm getting results with vProjection.Date > 5/30/2008.

vProjection is a linked SQL2005 view in access and Date is a smalldatetime.

WTF?!?!?!!

Thanks in advance for showing me the errors in my ways.

Scott

 

[PHV]

What is the data type type of vProjection.Date in the access's linked table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[scotttom]

Thanks for the reply.

It is a date/time in access and smalldatetime in SQL.

Best,

Scott

 

[Golom]

How much beyond 5/30/2008?

Access and SQL Server use slightly different base dates for datetime fields.

For Access, Day 0 is Dec 30, 1899.
For SQL Server, Day 0 is Jan 1, 1900 for a SmallDateTime.

I would have expected that would have been compensated for, but maybe not.

Try this as a passthrough query

SELECT vProjection.Date, vProjection.Project, vProjection.Term
FROM vProjection
WHERE (((vProjection.Date) < '5/30/2008') 
  AND  ((vProjection.Term) Is Null))
ORDER BY vProjection.Date DESC;

 

[scotttom]

Well beyond. Sorting does not work so I can't say for certain, but oddly enough the pass through works.

I'm going to be using this as a query for a recordset so will it behave like the passthrough or the access query. Probably the Access query because it is using jet correct?

Is there a solution?

Thanks for your help.

Scott

 

[Golom]

A pass-through doesn't use JET.

The whole point of a pass-through is that the query is sent to the ODBC server and processed as a native SQL Server query. Check out this link for a bit more info.

You should be able to use the pass-through as you do a regular query.

 

[scotttom]

Right, but..... (there is always a but) I am constructing this query at runtime based on variables from another recordset so recreating the passthrough would be a bit cumbersome in this case. Can I alter the view to somehow return only the Date? or are there any other work arounds?

Thank you again for hanging with me here.

Scott

 

[Golom]

As long as the ODBC connection that you want to use doesn't change, you should be able to create the pass-through query, selecting the ODBC connection and do all the other setup stuff. Save it as (for example) "myPassThrough" and then

SQL = "Select A, B, C From Some SQLTable"
CurrentDb.QueryDefs("myPassThrough").SQL = SQL

and then use the now modified query.