how to formulate as MMM-YY and yyyymm base on yyyymmdd ?

[William Poh Ben]

Hi,

I have this problem facing at work I haven't been able to solve for a
long time. Hopefully someone is able to help me here.
Thanks a bunches !

How would I be able to use Field query or Update Query to populate
Field2 as "MMM-YY" and Field3 as "yyyymm" base on the given dates as
"yyyymmdd" in Field 1 ?

Field1 Field2 Field3
(yyyymmdd) (MMM-YY) (yyyymm)
20021111 Nov-02 200211
20021020 Oct-02 200210
20030226 Mar-03 200303
20030709 Jul-03 200307

 

[Cheryl Fischer]

For Field2, try:

MonthName(Val(Mid([Field1],5,2)),True) & "-" & Mid([Field1],3,2)

For Field3:

Left([Field1], 6)

 

[Dirk Goldgar]

Hi,

I have this problem facing at work I haven't been able to solve for a
long time. Hopefully someone is able to help me here.
Thanks a bunches !

How would I be able to use Field query or Update Query to populate
Field2 as "MMM-YY" and Field3 as "yyyymm" base on the given dates as
"yyyymmdd" in Field 1 ?

Field1 Field2 Field3
(yyyymmdd) (MMM-YY) (yyyymm)
20021111 Nov-02 200211
20021020 Oct-02 200210
20030226 Mar-03 200303

This one right above here is an error, no?

20030709 Jul-03 200307

It depends on what data type Field1 is. I would store dates in
Date/Time fields, but it looks as though Field1 is either a text field
(second best), or a number field (worst). I also wouldn't create extra
fields just to hold the date in a different format, except maybe in a
static data warehouse for sorting purposes. Instead, I'd generate the
desired formats as calculated fields in queries (or calculated controls)
whenever I needed them.

If Field1 is a text field, then you could define the calculated fields
using expressions like these:

Field2: Format(Format([Field1], "@@@@\-@@\-@@"), "mmm-yy")
Field2: Format(Format([Field1], "@@@@\-@@\-@@"), "yyyymm")

If Field1 is a number field, you could define the calculated fields like
this:

Field2: Format(Format([Field1], "0000\-00\-00"), "mmm-yy")
Field2: Format(Format([Field1], "0000\-00\-00"), "yyyymm")

 

[William Poh Ben]

Cheryl and Dirk, I want to thank you so much for solving my nightmares
!