Access - Date yyyymmdd to yyyymm

I have a date field in short text, with the input mask

yyyymmdd

I am trying to change this to

yyyymm

with

DOB: Format([Date of Birth],"yyyyymm")

Without success.

Please advise
exp vgAsked:
Who is Participating?
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.

aikimarkCommented:
Did you mean to use five "y" characters in your format string?  That may be the cause of your problem
DOB: Format([Date of Birth],"yyyyymm")

Open in new window


Please confirm that the field is a date field and not a string or Long Integer field
0
exp vgAuthor Commented:
This field is indicated as a Short Text field.

Thank you.
0
Kelvin SparksCommented:
Try Format(cDate(DateofBirth),"yyyymm")

Kelvin
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

IrogSintaCommented:
Why are you using Short Text for a date field?  You should use the Date/Time data type for your Date of Birth field and then you can format at it any way you wish later on.

Ron
0
exp vgAuthor Commented:
Hello Kelvin,

I tried

DateYYYYMM:Format(cDate(Date of Birth),"yyyymm")

without success, and also tried

DateYYYYMM:Format(cDate[Date of Birth],"yyyymm")

without success.

Please advise.
0
exp vgAuthor Commented:
I can also separate out [Date of Birth] with Left and Mid functions in one query, and then join these two fields in a second query, but it seems this can be accomplished in one query.
0
aikimarkCommented:
This will drop the days from your string
DOB: Left([Date of Birth],6)

Open in new window

0

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
IrogSintaCommented:
I'll ask again in case you missed my earlier post.  

Why are you using Short Text for a date field?  You should use the Date/Time data type for your Date of Birth field and then you can format at it any way you wish later on.  

If you do wish to keep this as a Text data type, then you cannot use the Format function with Date arguments.  Since you have it as a string, you need to use the LEFT function and take the first 6 characters like so:
Left([Date of Birth],6)

Ron
0
aikimarkCommented:
@exp vg

What the experts are saying is that this field should really be a datetime field, not a text field.
0
exp vgAuthor Commented:
The data come as short text.

Thank you.
0
aikimarkCommented:
When you import the data, the resulting field should be converted from text to datetime
0
exp vgAuthor Commented:
Unfortunately this does not happen - it stays as short text.

Thank you.
0
aikimarkCommented:
this does not happen
YOU should do this when you import the data.
0
exp vgAuthor Commented:
Thank you everyone. I really appreciate it.
0
Dale FyeCommented:
exp vg,

Can you please explain why you feel the need to continuously use this text field for storing variously formatted values that started out as dates.  This is about your 10th question relating to how to display or work with "dates" but displayed as some other format.  Numerous experts have suggested that you simply use a date field for most of your needs, entering a complete date, and then use the Format( ) function in a query or in the control source of a textbox to display that value as something other than a full date.  

When I need a value that displays as yyyymm or mmm yy, I generally create a query that generates the date for the first day of the month, but displays this value as mmm yy in a combo box.  This would look something like:

SELECT DateAdd(tbl_Numbers.intNumber, "m", #1/1/15#)
, FORMAT(DateAdd(tbl_Numbers.intNumber, "m", #1/1/15#), "mmm yy")
ORDER BY DateAdd(tbl_Numbers.intNumber, "m", #1/1/15#)

Note: this assumes you have a table (tbl_Numbers) with a single field (intNumber) and as many values as you need, I generally start mine at zero.

This way, I store the value #5/1/15# in my table, but display "May 15" in my form.  When I want to include this field in a query, and sort by the date, I simply sort by the date field (hidden) and display the formatted date as either "mmm yy" or "yyyy mm".
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.