Access - Date yyyymmdd to yyyymm

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

Select allOpen in new window

Please confirm that the field is a date field and not a string or Long Integer field

This field is indicated as a Short Text field.

Thank you.

Try Format(cDate(DateofBirth),"yyyymm")

Kelvin

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

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.

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.

@exp vg

What the experts are saying is that this field should really be a datetime field, not a text field.

The data come as short text.

Thank you.

When you import the data, the resulting field should be converted from text to datetime

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.

Thank you everyone. I really appreciate it.

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".