Convert String to Date in Query

[McArthurGDM]

I have a string value that comes out of a query in the form of



01012015
02012015
12012014

etc...

where the first 2 digits are the month, the next 2 are the day, and the next 4 are the year.

How do I convert these values to a valid date that can be queried on?

Thanks!

[June7]

With string manipulation and date conversion functions.

CDate(Left(x, 2) & "/" & Mid(x, 3, 2) & "/" & Right(x, 4))

Or

DateSerial(Right(x,4), Left(x,2), Mid(x,3,2))


Why does query generate a string?

[McArthurGDM]

I get a strange result when I do the calculation

SELECT SearchReportQuarterlyCYAvg1.Quarter, SearchReportQuarterlyCYAvg1.MetricID, SearchReportQuarterlyCYAvg1.OE, SearchReportQuarterlyCYAvg1.AvgOfValues, SearchReportQuarterlyCYAvg1.Years, SearchReportQuarterlyCYAvg1.CountOfValues, Format(CDate(Left([NewDate],2) & "/" & Mid([NewDate],3,2) & "/" & Right([NewDate],4)),"Short Date") AS DateConvertFROM SearchReportQuarterlyCYAvg1
WHERE (((Format(CDate(Left([NewDate],2) & "/" & Mid([NewDate],3,2) & "/" & Right([NewDate],4)),"Short Date"))<=#4/1/2015#));

This is my original table of values after I converted.

Quarter	MetricID	OE	AvgOfValues	Years	CountOfValues	DateConvert
Q1	A1		2462559.66666667	2015	3	3/1/2015
Q2	A1		2407390	2015	2	6/1/2015
Q3	A1			2015	0	9/1/2015
Q4	A1			2015	0	12/1/2015

Then I run the criteria and this is the result

Quarter	MetricID	OE	AvgOfValues	Years	CountOfValues	DateConvert
Q1	A1		2462559.66666667	2015	3	3/1/2015
Q4	A1			2015	0	12/1/2015

[pbaldy]

Don't use the Format() function, it changes the result to a string.

[IrogSinta]

Here's another way to convert your string to a date?
CDate(Format([NewDate],"00/00/0000"))

Ron

[June7]

Yep, that works, too.

Still don't know why you have that oddball string to begin with.

[McArthurGDM]

Fair question, June. There probably was a better way to do what I was doing, but I had been working on it for almost 10 hours straight yesterday and just did was seemed doable rather than logical.

Thanks for the help.


You were right, Pbaldy, about it converting it back to a string. Shouldn't that not be the case? I thought the whole point of the format function was to convert it to a string type?

[IrogSinta]

You cannot do a comparison between String type and Date type in your WHERE clause. Not sure why you want to convert it to a String anyway. I agree with June, why not fix this at the source?

Ron

[McArthurGDM]

I still get the same result when I take away the FORMAT function.

I don't understand why this isn't working, given that it has been converted into a date. In fact, no matter what method I use to convert it to a date, I still get the same oddball results.

EDIT:

Actually, if I put in a Date in the criteria like #3/1/2015#. It works.

When I reference a form for the date, that's when I get the wacky results.

Why is that?

[pbaldy]

Can you post the db here?

[McArthurGDM]

Wish I could.

Database contains company-sensitive information.

The issue lies in the fact that I am referencing a report control for the date.

When I input a date and put the hashtags around it, it works just fine. I think it's not actually assuming that the control on the form is a date, even though I designated the control as such.

I tried putting hashtags around the report reference, that is #Reports!SearchReport!DateControl#

But that dosen't work and says "Not a valid date"

[pbaldy]

No, you don't want those (only for a literal date). Try

WHERE CDate(Left([NewDate],2) & "/" & Mid([NewDate],3,2) & "/" & Right([NewDate],4))<=Reports!SearchReport!DateControl

[McArthurGDM]

I think that's pretty much what I already have that's not working.

I did some tests and I discovered that the only date value that's giving me weird results is the 12/1/2015 date

Here's the code I used to create the new dates:

NewDate: IIf([Months].[Quarter]="Q1","0301" & [Reports]![SearchReport]![SearchReportYear],IIf([Months].[Quarter]="Q2","0601" & [Reports]![SearchReport]![SearchReportYear],IIf([Months].[Quarter]="Q3","0901" & [Reports]![SearchReport]![SearchReportYear],IIf([Months].[Quarter]="Q4","1201" & [Reports]![SearchReport]![SearchReportYear]))))

And the query as I stated:

SELECT SearchReportQuarterlyCYAvg1.Quarter, SearchReportQuarterlyCYAvg1.MetricID, SearchReportQuarterlyCYAvg1.OE, SearchReportQuarterlyCYAvg1.AvgOfValues, SearchReportQuarterlyCYAvg1.Years, SearchReportQuarterlyCYAvg1.CountOfValues, SearchReportQuarterlyCYAvg1.NewDate, CDate(Format([NewDate],"00\/00\/0000")) AS DateCovert
FROM SearchReportQuarterlyCYAvg1
WHERE (((CDate(Format([NewDate],"00\/00\/0000")))<=[Reports]![SearchReport]![ReportMonthYear]));

[pbaldy]

I suppose you can try wrapping the report reference in the CDate() function as well, to make sure it's interpreted correctly as a date. The problem with 12/1 is indicative of a string comparison.

[McArthurGDM]

It worked.

Thank you!

I had to wrap the Form reference in the CDate function.

For whatever reason, it wasn't reading it as an actual date. I will have to remember that for the future.

Why is it that the problems that drive us nuts often have the simplest solutions?