Format conversion from number to YYYYMM

[piotr.kabath@gmail.com]

Hi I have date stored as number in yyyymm format, as I need to
calculate previous month\ prev 3 months etc, I need to convert this to
date format yyyymm.
I have tried but with no success, I would be thankful for help.

regards
Piotr

[Allen Browne]

Re: Format conversion from number to YYYYMM

So this is a Number field (not a Date/Time field), with 6 digits.

You will need to convert it to a date, and then back to a 6-digit number.
This kind of thing (where d represents the name of your field):

CLng(Format(Dat eAdd("m", -3, DateSerial([d]\100, [d] Mod 100, 1)),
"yyyymm"))

Be sure to filter out nulls: CLng() fails with null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<piotr.kabath@g mail.comwrote in message
news:57afa132-6a92-4211-b227-4edd8079961a@34 g2000hsz.google groups.com...

Hi I have date stored as number in yyyymm format, as I need to
calculate previous month\ prev 3 months etc, I need to convert this to
date format yyyymm.
I have tried but with no success, I would be thankful for help.
>
regards
Piotr

[lyle fairfield]

Re: Format conversion from number to YYYYMM

piotr.kabath@gm ail.com wrote in news:57afa132-6a92-4211-b227-4edd8079961a@
34g2000hsz.goog legroups.com:

Hi I have date stored as number in yyyymm format, as I need to
calculate previous month\ prev 3 months etc, I need to convert this to
date format yyyymm.
I have tried but with no success, I would be thankful for help.

Debug.Print CDate(Format(20 0802, "####-##"))

2008-02-01

--
lyle fairfield

In ancient times Ulysses had to outwit the sirens, who had the power to
charm by their song all sailors who heard them, so that the mariners were
compelled to cast themselves into the sea to their destruction.
Today, he would have to deal with (free) Aim-Mail.