problem with querying datetime

[GLM2010]

I have data in a access2007 file and having problems with pulling data when I trying to find records within a particular date.

The following works just fine

SELECT expenseType
FROM expenseTypes
WHERE
expenseTypeID = 7 AND
expenseSource = 3

but if I try to do

SELECT
SUM (expenseAmt) AS thisAmt
FROM expenses3
WHERE
expenseDate < #1/1/2006#

I don't get anything. No error. Nothing.
If I do

SELECT
SUM (expenseAmt) AS thisAmt
FROM expenses3
WHERE
expenseDate > #1/1/2006#

I get the same result as if I wrote

SELECT
SUM (expenseAmt) AS thisAmt
FROM expenses3
WHERE
expenseDate > #1/1/2007#


The only thing I could think of is that there is something wrong with the date.

It's date/time with a format of short date. I added a few rows and typed in some data thinking that this new data would have to be good. But that didn't help.

I must be missing something simple because I've wasted too much time over something so simple.

[TheSmileyCoder]

As far as I can tell:
Those queries should return 1 row with either a number, or a blank row.

I dont spot anything wrong with what you have shown us. Try to provide more information such as the table structure and field types, and where/how you are trying to use this query.

[topher23]

This isn't a direct answer to your question, but it should provide you a system for finding the root problem. Then, if you can't figure out the answer after going through the process, we might be able to help you better.

When I'm troubleshooting Aggregate queries, I always first take the Sum off and run it as a simple Select query. If the data in the Select query looks fine, there is something wrong with the way the query is being summed.

If the Select query has bad data, I'd then take off the criteria. If everything works right at that point, then it's time to look at why the criteria is filtering incorrectly.

[GLM2010]

I found an answer, or at least a workaround. I'm using CFMX and asked the same question on that board. They didn't have an answer for WHY it didn't work only that using cfqueryparam removes the problem

expenseTypeID=7 AND
expenseDate >= <cfqueryparam value="1/1/2006" cfsqltype="cf_s ql_timestamp"> AND
expenseDate <= <cfqueryparam value="12/31/2006" cfsqltype="cf_s ql_timestamp">

I don't know why this works but it does. Usually you use cfqueryparam to prevent SQL injection attacks.

[GLM2010]

Still, I would love to know why Access 2007 acts this way. The datatype is date/time and the format is shortdate. The only problem I've had has been working with the datetime.