Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

"Between" Dates Criteria Problem 1

Status
Not open for further replies.

weigoldk

MIS
Jan 12, 2001
249
US
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.

 


Hi,

Naturally a TEXT field that someone has entered a text string that looks like a date in mm/dd/yyyy format, is nearly USELESS since it will not collate correctly.

Convert the STRING to a DATE...
Code:
WHERE (((CDate([trimComment])) Between CDate([type in beginning date]) And CDate([type in ending date])));


Skip,

[glasses] [red][/red]
[tongue]
 
Thanks,

I've modified it to:

SELECT FormatDateTime(CDate([trimComment]),2) AS ConvertedComment
FROM qryTrimOnlyDateComment
WHERE (((FormatDateTime(CDate([trimComment]),2)) Between cdate([type in beginning date]) And cdate([type in ending date])));


But I still get Sept 2005 dates when I type in 9/1/2006 as my beginning and 9/30/2006 as my ending.

 
Another way:
PARAMETERS [type in beginning date] Datetime, [type in ending date] DateTime;
SELECT FormatDateTime(CDate([trimComment]),2) AS ConvertedComment
FROM qryTrimOnlyDateComment
WHERE CDate([trimComment]) Between [type in beginning date] And [type in ending date];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Thank you PHV, it works. Can you now do me a huge favor and tell me why?

What does the PARAMETERS command do?

Thanks again and Happy Halloween.

 
What does the PARAMETERS command do?
Assign a data type to your parameters.
 
OK--I get it.
That is the coolest thing I've seen today.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top