Between dates in a text field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Uschi - 15 Aug 2006 05:35 GMT

I have a table where the date field is text. I need to make a query to find
dates Between 01/01/2006 and 06/30/2006.
Using the criteria BETWEEN "01/01/2006" AND "06/30/2006" will bring up every
record between 01/ and 06/ but it will not limit the year to 2006.
Can anyone help?

Reply to this Message

pietlinden@hotmail.com - 15 Aug 2006 06:03 GMT

Why are you storing dates as text? that's what date fields are for.
to answer your question, you might want to use CDATE() to convert and
then do the comparison. I would use a first query to convert the dates
to real dates (if you can't convert the field type) and then query
that.

SELECT CDate([OriginalStart]) AS StartDate, CDate([OriginalEnd]) AS
EndDate
FROM MyTable...

Otherwise, if you use the QBE, it's going to interpret your dates as
text, because you're storing them that way. Might work in Oracle as
text, but not in Access.

Reply to this Message

Uschi - 15 Aug 2006 06:42 GMT

Many thanks for quick reply.
Yes, I know that dates should not be stored as text. Unfortunately, this
database was created 4 years ago and I inherited it. I believe the format was
to have the field look like mm/dd/yyyy.
I have made copies of the DB and changed the date field to DATE/TIME. The
conversion totally messed up the dates. Ex. 1_/1_/2006.
I don't understand how to convert the field using CDATE(). Can you walk me
through it?

>Why are you storing dates as text? that's what date fields are for.
>to answer your question, you might want to use CDATE() to convert and
[quoted text clipped - 9 lines]
>text, because you're storing them that way. Might work in Oracle as
>text, but not in Access.

Reply to this Message

pietlinden@hotmail.com - 15 Aug 2006 07:25 GMT

did you miss the SQL I posted?

SELECT tblDates.TextDate, CDate([TextDate]) AS FixDate
FROM tblDates;

CDate converts a text date to a real date (a number).
CDate([TextDate]) AS ReallyADateField

Then if you sort by ReallyADateField, you'll get proper date sort
order, e.g., by year, then month, then day.

You don't even have to remove the spaces. I tried it with the example
you posted and it worked fine. Before doing anything, I'd make a copy
of this database. Nothing worse than making an irretrievable mistake!
(Been there, done that! AND on other people's databases... NOT fun.)

So make a copy, then change the data type from text to Date/Time. If
you get no errors, you're fine. If you want to test it first, create a
query based on your table, and use CDATE. Check the results. If you
get valid conversions, you can just change the type right in the table.

Reply to this Message

Uschi - 15 Aug 2006 11:39 GMT

OK, I made a copy of the DB and I typed your code. It now looks like this:

SELECT ShareCertificates.CertNo, ShareCertificates.SpNo, ShareCertificates.
DateIssued, ShareCertificates.[Membership Name]
FROM ShareCertificates;
SELECT tblDates.TextDate, CDate([TextDate]) AS FixDate FROM tblDates;

I get the error message "Characters found after end of SQL statement"

What am I doing wrong?

Suggestions, please?

>did you miss the SQL I posted?
>
[quoted text clipped - 16 lines]
>query based on your table, and use CDATE. Check the results. If you
>get valid conversions, you can just change the type right in the table.

Reply to this Message

pietlinden@hotmail.com - 16 Aug 2006 01:35 GMT

Semi-colons are statement terminators in SQL. (like a period in
English). Remove the semi-colon after FROM ShareCertificates

Reply to this Message

Uschi - 16 Aug 2006 03:13 GMT

Thank you so much for hanging in there for me....

Here is a copy/paste of the query:

SELECT ShareCertificates.SpNo, ShareCertificates.CertNo, ShareCertificates.
DateIssued, ShareCertificates.[Membership Name]
FROM ShareCertificates
SELECT tblDates.TextDate, CDate([TextDate]) AS FixDate FROM tblDates;

I now get Syntax Error in FROM clause message.

What am I doing wrong?

>Semi-colons are statement terminators in SQL. (like a period in
>English). Remove the semi-colon after FROM ShareCertificates

Reply to this Message