Access - Date yyyymmdd to yyyymm

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


I am trying to change this to



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

Without success.

Please advise
exp vgAsked:
Who is Participating?

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

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.

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
exp vgAuthor Commented:
This field is indicated as a Short Text field.

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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.

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.
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.
This will drop the days from your string
DOB: Left([Date of Birth],6)

Open in new window

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
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)

@exp vg

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

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

Thank you.
this does not happen
YOU should do this when you import the data.
exp vgAuthor Commented:
Thank you everyone. I really appreciate it.
Dale FyeOwner, Developing Solutions LLCCommented:
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".
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.