Problems getting results where conditional includes Date/Time field.

[CHeighlund]

I'm working on a Delphi program that is designed to interface with an Access Database (Access 2000). I know almost nothing about how Access works.

I'm having a problem with my results, apparently based on a date/time field I'm referencing. Attempts to pass a count(*) SQL statement where the Date/Time field is referenced in the where clause(s) return values of 0. An attempt to do so in the sqlview mode of the database returned 0 as well, although a run without the Date/Time where clause returned a count of ~14000 records. The clause in question was 'where dt < 3/1/2004'. At the time I ran it, I was looking at the recordset in question, with my eyes clearly on at least five records with dt values between 2/23/2004 and 2/28/2004. I would have thought that these would have been picked up, and they clearly were not.

The only thing that I can think of is that the field dt has an attached timestamp; that is, hours, minutes, and AM/PM values in addition to the x/y/zzzz dates. My test criteria do not. How would I go about setting up the statements so that I'm catching those values?

 

[PaulBricker]

Well, if there was an attached time stamp it might cause some problem getting all the records you want, but if you are covering a few days with your criteria, you should be returning some records regardless of the timestamp.
In Access date/time values are normally enclosed in #'s
<#3/1/2004# even in the WHERE clause.

Try checking your syntax and see if that helps or post your sql for us to look at.

Paul

 

[lespaul]

besides adding the # for the date delimiter, you can also extract just the date portion of the database field by using the INT function:

WHERE INT(dt) < #3/1/2004#

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[CHeighlund]

I was not using the '#' markers. Putting those in place did solve the problem. Thank you to both of you.

And thanks also, lespaul, for the advice about the INT() option.