Between dates in a text field

[Uschi via AccessMonster.com]

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?

 

[pietlinden]

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.

 

[Uschi via AccessMonster.com]

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
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.

 

[pietlinden]

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.

 

[Uschi via AccessMonster.com]

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?

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.

 

[pietlinden]

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

 

[Uschi via AccessMonster.com]

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?