Date query doesn't filter 2

[hefly]

the following query does not filter my dates from field "dated". There are no other criteria set.

My query:

SELECT [tbl_2010 Transaction Details].Dated
FROM [tbl_2010 Transaction Details]
WHERE ((([tbl_2010 Transaction Details].Dated)>"#12/31/2010#"));

I expect the query to return only those dates greater than 12/31/2010. It returns all dates, including those less than 12/31/2010.


Any idea what is wrong? I am running Access 2010 with SP1

Hefly

 

[PHV]

Don't use quotes for date literal:

SELECT Dated
FROM [tbl_2010 Transaction Details]
WHERE Dated>#2010-12-31#

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

 

[hefly]

PHV:

Thank you very much! If I use the criteria you suggested in design view, Access adds the quotes, and I get no fitering.

I can go to SQL view and delete the quotes... and they do not appear when I run the query, but I still get no filtering.

Do you have any other suggestions?

Thank you.

Hefly

 

[JonFer]

If Access is adding quotes, your field is a text field and then you don't need the hash/pound signs. Look at your table design to verify.

 

[dhookom]

It sounds like the field is text and you want to query it as if it's a date data type. I would change the field to a date/time if you expect to store dates. Otherwise try:

SELECT [tbl_2010 Transaction Details].Dated
FROM [tbl_2010 Transaction Details]
WHERE DateValue(Dated) >#12/31/2010#;

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

And to avoid problem with null values:

SELECT Dated
FROM [tbl_2010 Transaction Details]
WHERE CVDate(Dated)>#2010-12-31#

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

 

[hefly]

Gentlemen: THank you! I have no clue why this "date" field was set to text, but it certainly was.

I had imported the data from an excel spreadsheet... and I obviously neglected to select the correct data type.

Thank you again.

Hefly

))