working with Dates

Dear All,

I am having a lot of problems working with dates:-

First of all I have a table called articles, and I have a date field, which is in text format, and I insert the dates for example 02/11/2003 in it. Now the user can enter articles that are bigger than todays date, however I want to display only those that are smaller than todays date. Is it possible to convert the text field I have for dates into numeric format? And if so, will the comparison work? At the moment it is not working since for the database, 11/02/2000 is bigger than 01/04/2003. Another thing, I wish to keep the Articles dates in text format for my purposes.

My second problem is when coming to compare dates:-

I have this sql statement
sql = sql & " AND newsArticles.newsDate BETWEEN " & "'" & FromDate & "'" & "AND '" & ToDate & "'"

but it is not working properly. This is the result when I write the sql with a response.write:-

select newsArticles.newsID, newsArticles.newsMainSecID, newsArticles.categoryID, newsArticles.newsDay, newsArticles.newsMonth, newsArticles.newsYear,newsArticles.newsDate, newsArticles.newsTitle, newsArticles.newsAuthor, newsArticles.newsSummary, newsArticles.newsText,newsCategory.categoryName, newsMainSec.newsMainSecName from (newsArticles INNER JOIN newsCategory ON newsArticles.categoryID = newsCategory.categoryID) INNER JOIN newsMainSec ON newsArticles.newsMainSecID = newsMainSec.newsMainSecID WHERE newsArticles.newsMainSecID = 1 AND newsArticles.newsDate BETWEEN '01/01/2000'AND '01/04/2002' ORDER BY newsArticles.newsDate Desc

and it is not returning any results when there should be. What am I doing wrong?

Your help is very much appreciated!

Why don't you just use a date field of the DATE type? Ms Access has a Date/Time field and (My)SQL has a date field of the YYYY-MM-DD format.
All you have to do (once you have a proper date field) is convert your date to the current format.

Just to state obvious you could use datediff or dateadd functions