Access CDATE from Text

Please offer how I can use CDATE to convert a short text format field mm/yyyy to a date/format.

I have tried CDATE Left/Right to obtain place holders without success.

Thank you.
exp vgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
did you try

cdate([datefield]) ?
exp vgAuthor Commented:
Yes - but the intent is to purposely only get the month and year field. Your suggestion present the full month, day, year.

I have researched examples that have the left/right parameters  - but I am unable to get this to work.

Thank you.
exp vgAuthor Commented:
I basically need to convert mm/dd/yyyy to mm/yyyy and have the latter in date/time format so I can sort correctly in my crosstab query.

I have tried all options without success.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Dale FyeOwner, Developing Solutions LLCCommented:
In my response to your previous post, I recommended that you keep your original date field, and when you need to group by month and year, you simply use the Format( ) function to format the date as "yyyy/mm" or "mm/yyyy".

If you want to add a day into the mix, then you could do something like:

strMoYr  = "12/2015"
?CDate(strMoYear)

will return #12/1/2015#

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
exp vgAuthor Commented:
Let me clarify - I did do the format option first - but this was transformed into a short text format - that does not sort properly.

What I prefer is the  to use the original date, and use a CDATE to obtain just the month and year from the original date.

Basically  is there a way to obtain CDATE - Left/Right to get just the month and year from the original date?

Thank you.
Rey Obrero (Capricorn1)Commented:
if you want to get a correct sorting of the date values, use this format "yyyymm" or "yyyy-mm" or "yyyy/mm"
Rey Obrero (Capricorn1)Commented:
when you use the Left/Right to get the month and year, you are converting the result to a Text data type. You can get a correct sorting of the Text date by using the format I posted above
Robert ShermanOwnerCommented:
The bottom line is, if you want proper sorting, you need to leave the sorting field as a date format.  A date format, by definition, ALWAYS has a day AND a time component to it.   Perhaps what you are looking for is to format the values in the display or reporting stage.  Alternatively, add another field for the formatted version and sort on the DateTime field that you get using CDate.

EDIT: Just to be clear, DateTime fields will have all of the components.  When you use Cdate with some like "7/2015", you will get back a DateTime where the DAY is set to a default of 1, and the time (I believe) is set to midnight.  (or is it 12:01.. still, point is the same)
Rey Obrero (Capricorn1)Commented:
<if you want proper sorting, you need to leave the sorting field as a date format. >
Not necessarily.. as I've posted above, it can be in text format.
Robert ShermanOwnerCommented:
Yes, Rey is correct, but proper sorting as a text value will require changing the order of the components so that year comes before month, as in yyyymm or yyyy-mm, etc...   If the desired output is mm/yyyy, there will still be the need for a formatting of the data at some point for user presentation.  

EDIT: So, essentially what I was getting at was if the information is starting out as a DateTime field (which I believe it is), then converting it to text prior to sorting it doesn't gain us any advantage because there is still going to be an additional step to deal with the formatting for output...   the conversion to text becomes an extra unnecessary step in the middle.
Robert ShermanOwnerCommented:
A key piece of the question is the follow-up requirement that this is part of a Crosstab query.   This is one of the few areas of Access in which I have little experience, but I thought I'd point this out in case it was missed the first time it was mentioned, so that the solution can conform to that need.
exp vgAuthor Commented:
Robert - exactly - I need the final format to be with the month followed by the year.

I do see now that conversion to text is not necessary (since I did not think that sorting on it would be an issue). This is much more of a challenge than I thought it would be.

To backtrack to the beginning - I will keep the original date as short date - mm/dd/yyyy. What is the easiest way to just extract the mm/yyyy in this order, in date/time?

Thank you.
Robert ShermanOwnerCommented:
The Crosstab Query Wizard, I believe, will take Dates and handle grouping them into intervals, such as month/year...   Have you tried using that to see if you can create what you want from scratch now that you have an understanding of the rest of it?
exp vgAuthor Commented:
Yes, the wizard will group - but the display will only indicate months - and I need months and years. I would also prefer a mechanism to store the coding that enables me to create a crosstab query with month/year as column headers.
exp vgAuthor Commented:
Thank you everyone - it looks like I will either have to manually enter criteria - or format after the output.
exp vgAuthor Commented:
Thank you.
exp vgAuthor Commented:
I ended up manually entering in the column header in properties - it was the only feasible way to make this work

Thank you everyone for your assistance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.