Date filtering criteria

[walduxas]

Hi guys,

I have a little problem when I try to filter the file by date criteria. Here's what I did:
All the data is in text file, so I linked this text file to MS Access 2002, and splited the text file columns. So now I have made a table. The date in this file is like this "2007 05 08", so I used a select query [Query1] with additional column which replaced the spaces in this date by the dashes and now I have the date in the format "2007-05-08" (this is the correct date format on my computer machine). I also set that the format of the column would be "General Date". Untill now everything is OK and I'm able to see the correct date. I created additional select query [Query2] which used the data in the first query [Query1] and tried to filter the date column by entering value

<=#2007-05-08#

However after I pree the button 'Enter' or 'TAB', then MS Access automatically changes this criteria to

<="#2007-05-08#"

When I try to press the button "View" the system says that 'Data type mismatch in criteria expression'. And I'm not able to filter the data as I want to.

Does anybody know why this happens? Is there anything possible to do that I could filter this data by the date?

 

[namliam]

now I have the date in the format "2007-05-08"

...

<=#2007-05-08#

...

<="#2007-05-08#"

Does anybody know why this happens? Is there anything possible to do that I could filter this data by the date?

Yes I do know, and probably you do too... Your " date format " is "2007-05-08"... What do the "" mean??

This means its a TEXT column, NOT a Date column, thus you cannot use <= ## because what does ## Mean?? This means this is a DATE value!

Use DateSerial or CDate to convert your text into a REAL DATE for which you can use <=##
>> Check access help if you dont know either function,
>> FYI for optimal efficiency skip the replace to get - into the date and use Dateserial with a mix of Left, Right and Mid functions

Good luck !

 

[walduxas]

Yes I do know, and probably you do too... Your " date format " is "2007-05-08"... What do the "" mean??

This means its a TEXT column, NOT a Date column, thus you cannot use <= ## because what does ## Mean?? This means this is a DATE value!

Use DateSerial or CDate to convert your text into a REAL DATE for which you can use <=##
>> Check access help if you dont know either function,
>> FYI for optimal efficiency skip the replace to get - into the date and use Dateserial with a mix of Left, Right and Mid functions

Good luck !

Hi,

thanks for your advice, it helped. Now I don't see characters " in the criteria field. However still if I press the button "View" the system says that 'Data type mismatch in criteria expression'. Can it be because in some rows, the value of date is '#Error'?

 

[namliam]

yes probably so...

 

[walduxas]

OK, let's take this problem to another level
I managed to get rid of those rows where value was equal to '#Error'. However the program still said that 'Data type mismatch in criteria expression'. So in the criteria field I entered this expression:

Like #2009.05.31#

Now the system didn't show me any error messages and it generated the rows where the date was equal to 05/31/2009.
But the thing is that I need to filter only those rows where date is equal to or higher than the 05/31/2009. If I try to use logical signs like ">", "<", "=" etc., than I receive error message about wrong data type mentioned above. Is there any possible way to filter this data? How should I do that?

 

[namliam]

Like #2009.05.31#

This is nonsense...
= #2009.05.31#
is much better.

can you make a small copy of your DB and set it on the forum (attach to a post) where we can look at your DB ourselves? There must be something logicaly wrong.

 

[walduxas]

Hi,

I attached a little part of my database. See the query called "Final". When I try to enter the filter criteria in the fields "Begin" or "Mature", I receive that error message. You can experiment by yourselves

 

[namliam]

OK Here is the thing... Query on query is nice for the user, but Access dont work that way I had totaly forgotten about this "feature" but obviously sometimes access is a little to smart for its own good....

The query on query makes Access into 1 big query... thus executing the query in one go, applying the < ## on all rows including the errors.. Throwing the new error.
There is unfortunatly -that I know off- no way around this other than "compiling" the errors into a real (faked) date....

Something like so, to take out the spaces first, Query1:

SELECT Right(Trim([Field7]),10) AS [Begin]
FROM Bandomasa;

Query2:

SELECT Query1.Begin, IIf([begin] Like "#### ## ##",DateSerial(Left([Begin],4),Mid([Begin],6,2),Right([Begin],2)),#1/1/1900#) AS Expr1
FROM Query1
WHERE (((IIf([begin] Like "#### ## ##",DateSerial(Left([Begin],4),Mid([Begin],6,2),Right([Begin],2)),#1/1/1900#))<#2/4/2002# 
  And   (IIf([begin] Like "#### ## ##",DateSerial(Left([Begin],4),Mid([Begin],6,2),Right([Begin],2)),#1/1/1900#))<>#1/1/1900#));

Using the IIF you basicaly convert the errors into a real date of 1/1/1900.
Then in the where you exclude that date (<>#1/1/1900#)