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

  • Thread starter Thread starter William Poh Ben
  • Start date Start date
W

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
 
For Field2, try:

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

For Field3:

Left([Field1], 6)
 
William Poh Ben said:
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
Click to expand...

This one right above here is an error, no?
20030709 Jul-03 200307
Click to expand...

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