423,846 Members | 2,048 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,846 IT Pros & Developers. It's quick & easy.

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

P: 18
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.TransferSpread…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.
1 Week Ago #1

✓ answered by twinnyfo

jdusn1978,

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

Expand|Select|Wrap|Line Numbers
  1. 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.

Share this Question
Share on Google+
6 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,703
jdusn1978,

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

Expand|Select|Wrap|Line Numbers
  1. 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.
1 Week Ago #2

P: 18
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.
1 Week Ago #3

NeoPa
Expert Mod 15k+
P: 31,121
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.
1 Week Ago #4

P: 18
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!
1 Week Ago #5

twinnyfo
Expert Mod 2.5K+
P: 2,703
Glad I could hepp you on this problem! Let us know if you have additional questions.
1 Week Ago #6

NeoPa
Expert Mod 15k+
P: 31,121
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.
1 Week Ago #7

Post your reply

Sign in to post your reply or Sign up for a free account.