yyyymmdd

[Clay]

I need to convert dates that are in the format of
yyyymmdd to just year and month. How can I do this?
Please be specific - as my VBA needs some work!

Thank You,
CLay

 

[Bruce M. Thompson]

I need to convert dates that are in the format of

yyyymmdd to just year and month. How can I do this?
Please be specific - as my VBA needs some work!

If these date values are text, then "Left([MyDateValue], 6)" would take care of
that.

If the date values are stored in date/time fields, then it would appear that
either the field's "Format" property has been set to "yyyymmdd" in the table
design or your regional time settings in Windows are set to that format, so
everywhere you want to display only the year and month, just set the "Format"
property to something like "yyyymm" or anything appropriate that doesn't include
the "d" (day) symbol. If you are hoping to exclude the *day* from the date value
in the table, you can't. Date/time fields are stored as numbers (the whole
number portion represents the date right down to the day and the decimal portion
represents the time value) and must include a valid date/time value.

 

[Clay]

Bruce,

The date values are text, but where would I go about
putting Left([Yrmnth]), 6)?

Clay

-----Original Message-----

If these date values are text, then "Left

([MyDateValue], 6)" would take care of

that.

If the date values are stored in date/time fields, then it would appear that
either the field's "Format" property has been set to "yyyymmdd" in the table
design or your regional time settings in Windows are set to that format, so
everywhere you want to display only the year and month, just set the "Format"
property to something like "yyyymm" or anything

appropriate that doesn't include

 

[Bruce M. Thompson]

The date values are text, but where would I go about

putting Left([Yrmnth]), 6)?

Well, that depends on what you need to do with it. If you are going to display
this value only, then you can add a column to a query or add a control to a
form/report in the follow manner (note that you won't be able to edit or add
using this approach):

Query (new column):
NewDate: Left([Yrmnth]), 6)

Calculated control on form - Set a texbox's "Control Source" property to:
=Left([Yrmnth]), 6)

Regarding other uses, you would need to be more specific on what you want to do.
I wouldn't modify the values in that field directly in case the original values
represent necessary data.