Cannot Filter Date after changing the format forn date/time stamp

[kanoor]

Hi,

I'm converting a data dump date field from Date/Time stamp in a query

06.10.2010 02:42:00 using Left/Mid/Right to and concatenating to show 06/10/2010. Though I tried using Cdate to make it a date function and on properties changed it to short date, I can't get it to recognise it as a date and thus not able to filter it by date. When I put a date in the query to filter it is not doing anything and just hangs. The db has around million rows with about 10 fields.

Will greatly appreciate any help on this.

Thanks

 

[HiTechCoach]

To convert to a date you can use the Rpalce () liek this:

? CDate(Replace("06.10.2010 02:42:00", ".", "/"))
6/10/2010 2:42:00 AM

If you just want the date value without the time then try this:

? DateValue(Replace("06.10.2010 02:42:00", ".", "/"))
6/10/2010

 

[kanoor]

Thanks a lot. This has definitely cut down the number of step in building the query. I have rebuilt the query with the expression and is definitely quicker to browse from one end to the other end in the table view. But still when I put a select date in there its not retrieving the data for select date. Its just waiting and not even showing the query progress bar. Is it because of the volume of data. But as I said when I browse in the table to the last record its breezing past to the 1.4 Millionth record.

Many Thanks