Date format issue

[di.miller]

Hi All,

I have an Access database with a field called COMPLETEDATE that is of the value string that has the format of "1110315". Here is where it gets weird. I need to drop off the leading 1 to make the data look like "110315" and convert this to the type Date. I have figured out that I can drop the leading 1 in Access by using the code:

SELECT Right([HBELP_FINAL_Q.COMPLETEDATE],6) AS RealCompleteDate

This returns values like "110315" which I like but, I still have an issue to deal with. How do I modify this code to convert it to a Date type?

I tried using:

SELECT CDate(Right([HBELP_FINAL_Q.COMPLETEDATE],6)) AS RealCompleteDate

but the results became like 1/11/2022 and strange numbers like the year 2176!

Can someone please help me figure out how to modify my code to convert this to a date type with a format of yymmdd?

Any help is greatly appreciated, this is driving me crazy!
-Diana

[William McKinley]

I import the exact same date format into my database. I used this function, might not be the best way but it works:

Transaction Date: DateValue(Format(([YourField]+19000000),"@@@@\/@@\/@@"))

[di.miller]

Hi William,

I don't really understand your code. Do I place this code in my Select statement? What will the final output look like?

Thanks so much for your help,
Diana

[di.miller]

I found an answer on another forum and here is what worked for me:

CDate("20" & Format(Mid([HBELP_FINAL_Q.COMPLETEDATE],2),"00-00-00"))

Hope this can help others with similar problems.
-Diana