Query: Convert text to date, display records older than 60 days from Date()

[jdusn1978]

Goal: Convert text to date in a query and show only dates that are older than 60 days from Date()

I’ve searched the site and nothing quite matches what I’m looking for, at least in a way I can comprehend. I can read VBA and SQL at the about the level of a very skilled orangutan.

I’m automating an Excel process using Access. I import Excel files (using DoCmd.TransferS pread…etc) and want to utilize a column named [Posting Date] that contains crappy formatted dates. In Excel, those dates are displayed as DD.MM.YYYY (example: 04.12.2018) but formatted as “General” in Excel. That general column imports as a text column into Access 2016, which my query won’t read as a date without telling it too.

I need a query that turns the text 04.12.2018 into a readable date, and returns records older than 60 days from Date(). I don’t care what display format the query converts the date to (unless you awesome folks tell me I should care), as long as in the end I am viewing records older than 60 days from today. After 60 days of not making a payment, you’re going on the naughty list.

Table Name = CCRejected
Total Columns = A through N
Key Columns = Col A “Customer Number” (number); Col D “Posting Date” (text); Col L “Reason” (text)

Desired outcome: Convert Col D “Posting Date” (text) to a (date) format that can then be further queried for records that are older than 60 days.
Why: If you haven’t paid your bill for over 60 days you can no longer provide you services.

Thanks in advance.

[twinnyfo]

jdusn1978,

To get a Date value in Acces, you would use the following Function:

CDate(Right([Posting Date], 4) & "-" & Mid([Posting Date], 4, 2) & "-" & Left([Posting Date], 2))

That generates the Posting Date "as a date". Notice that we are using String Functions to take apart the string value presented, putting it together into a universal date format ("yyyy-mm-dd"), and then converting it to a Date using the CDate() function.

Then, if you subtract that value from Date(), if that value is greater than 60, they get coal in their stockings!

Hope this hepps.

[jdusn1978]

Twinnyfo, thank you for replying. It's whiskey:30 here in Germany; I'll take a noob crack at this in the a.m. and report back. I very much appreciate all of you. Merry [your views]mas.

[NeoPa]

Well expressed question and humour too. I like it. I can also tell you that Twinny's suggestion should work fine for you.

Let us know if you struggle at all.

[jdusn1978]

twinnyfo - Works perfectly! Thank you, and I might have some more questions coming your way as I work through this project. You guys rock!

NeoPa - You've been at this for a long while... you helped me a few years ago on a project in the Netherlands. Thank you!

[twinnyfo]

Glad I could hepp you on this problem! Let us know if you have additional questions.

[NeoPa]

I can't pretend to remember the details, but certainly would have appreciated a question as fully explained. Also very glad to know I was able to help.

19th October 2006 was when I asked my first question here. I've never regretted a minute of it.