I have an ODBC connection to a table. The field that I'm working with happens to be a text field, and I have to work around that fact (ie I can't change the source table). Our users put a date in that field in this format mm/dd/yyyy including the slashes.
Here is my SQL text to trim off the extra spaces then pull only those records that meet the format requirements above for a date (they sometime use this field as a text comment--such as "will be dated in January" so I'm filtering with the like conditon)
SELECT Trim(Table!Comment) AS trimComment FROM Marriage
WHERE (((Trim(
![Comment])) Like "##/##/####" ));
Here is my SQL (in another query) to convert the text to a date then prompt for beginning and ending dates.
SELECT CDate([trimComment]) AS ConvertedComment
FROM qryTrimComment
WHERE (((CDate([trimComment])) Between [type in beginning date] And [type in ending date]));
It doesn't error, but it doesn't work either. If I type in 9/1/2005 for the beginning date and 9/30/2005 for the ending date, I get September 2005 dates AND September 2006 dates.
I even tried adding in a formatdate with the shortdate paramater function to be sure I'm using dates, but that did not change the results.
Here is my SQL text to trim off the extra spaces then pull only those records that meet the format requirements above for a date (they sometime use this field as a text comment--such as "will be dated in January" so I'm filtering with the like conditon)
SELECT Trim(Table!Comment) AS trimComment FROM Marriage
WHERE (((Trim(
Here is my SQL (in another query) to convert the text to a date then prompt for beginning and ending dates.
SELECT CDate([trimComment]) AS ConvertedComment
FROM qryTrimComment
WHERE (((CDate([trimComment])) Between [type in beginning date] And [type in ending date]));
It doesn't error, but it doesn't work either. If I type in 9/1/2005 for the beginning date and 9/30/2005 for the ending date, I get September 2005 dates AND September 2006 dates.
I even tried adding in a formatdate with the shortdate paramater function to be sure I'm using dates, but that did not change the results.