Home > Net >  Filter DateTime in MS Acess 2010 Database
Filter DateTime in MS Acess 2010 Database

Time:04-15

I have 5070 rows in a Table. But in that many entries are dump entries. I simply want to ignore them. In dump entries I have 1900-01-01 00:00:00 this data in many rows, I want to ignore all the rows which is having above data.

My query looks like this Select * from Table where AttendanceDate > #1900-01-01 00:00:00#

I tried using CDate(1900-01-01 00:00:00), "#1900-01-01 00:00:00#", <> #1900-01-01 00:00:00# as well, but nothing helps.

I have gone throuh around 15-20 SO Questions and tried their marked answers but didn't work.

EDIT

Sample Image

I have data like this. I want to filter data that has InTime > 1900-01-01 00:00:00.

The table has only 650 valid entries from 5070 entries. I want to remove all the other extradump entries.

Any help would be appreciated!

Thank you

CodePudding user response:

As suggested in the comments you have to use the date literals in the format #MM/dd/yyyy HH:mm:ss#.

So "SELECT * FROM tbl1 WHERE Datum > #1/1/1900 00:00:00#" should work in your case. Best practise is to use parameters as mentioned in the comments but the previous SQL statement should just work fine for you.

The data type of the field in question shoud be date/time.

PS: One could use the DateValue function to convert the text in date but this will cause trouble as one can never be sure if the conversion has been successful. IMHO it's best to have the correct data type from the very beginning

"SELECT * FROM tbl1 WHERE DateValue(Datum) > #1/1/1900 00:00:00#"

  • Related