Format Problem

T

tkosel

Two part question. First, I have the following code in a form. Testing it
in debug mode, this is what I get.

? format(datepart("yyyy", now()),"yy")
05

How come I am getting output of 05? Today is February 10, 2010. Shouldn't
I be getting 10?

Where this is leading to is below. (Second part of question, related
partially I think to problem outlined above.)

Me.JulianExpirationDate = Format(DateAdd("yyyy", 5, Now()), "dd/mm/yyyy")
? me.JulianExpirationDate
09/02/2015
Me.JulianExpirationDate = Format(Year(Me.JulianExpirationDate), "yy") &
Format_(Format(Me.JulianExpirationDate, "y"), "000")
? me.JulianExpirationDate
05245

I am expecting an output of 15041.


What am I doing wrong in both cases?
 
M

Marshall Barton

tkosel said:
Two part question. First, I have the following code in a form. Testing it
in debug mode, this is what I get.

? format(datepart("yyyy", now()),"yy")
05

How come I am getting output of 05? Today is February 10, 2010. Shouldn't
I be getting 10?

Because you are using date format code "yy" on the integer
2010, not a date. If you want just a two digit year as a
string, use Format(Date(), "yy") If you want it as a
number, use Year(Date()) Mod 100
Where this is leading to is below. (Second part of question, related
partially I think to problem outlined above.)

Me.JulianExpirationDate = Format(DateAdd("yyyy", 5, Now()), "dd/mm/yyyy")
? me.JulianExpirationDate
09/02/2015
Me.JulianExpirationDate = Format(Year(Me.JulianExpirationDate), "yy") &
Format_(Format(Me.JulianExpirationDate, "y"), "000")
? me.JulianExpirationDate
05245

You are also forgetting that Format always returns a string.

To get what you want as a string, just use:
Format(Date(), "yy") & Format(DatePart(Date(), "y"), "000")
 
D

Daryl S

Tkosel -

The Format function does not use "yy" as a format - maybe you want to use
this:

Format(Right(Str((DatePart("yyyy", Now()))), 2), "00")
 
F

fredg

Two part question. First, I have the following code in a form. Testing it
in debug mode, this is what I get.

? format(datepart("yyyy", now()),"yy")
05

How come I am getting output of 05? Today is February 10, 2010. Shouldn't
I be getting 10?

No! Access is correct and 05 is the correct answer *for the expression
you have written*.
Break it down into smaller portions.
DatePart("yyyy",Now()) = 2010 ... The current year

If you then convert the 2010 to a date (which is what your
Format(ADate,"yy") function expects, i.e. CDate(2010) = 7/2/1905
which is 2010 days after 12/30/1899 (which is where Access starts
counting dates from).

Therefore:
Format(2010,"yyyy") = 1905 )
and Format(2010,"yy") = 05
Try it.

To show the current year as a 2 digit year, all you need is
Format(Now(),"yy")

I don't have time now to look at the rest of your question. Sorry.
 
J

John W. Vinson

Two part question. First, I have the following code in a form. Testing it
in debug mode, this is what I get.

? format(datepart("yyyy", now()),"yy")
05

How come I am getting output of 05? Today is February 10, 2010. Shouldn't
I be getting 10?

See Fred's reply. You're trying too hard:

Format(Date(), "yy")

will give you "10".
Where this is leading to is below. (Second part of question, related
partially I think to problem outlined above.)

Me.JulianExpirationDate = Format(DateAdd("yyyy", 5, Now()), "dd/mm/yyyy")
? me.JulianExpirationDate
09/02/2015
Me.JulianExpirationDate = Format(Year(Me.JulianExpirationDate), "yy") &
Format_(Format(Me.JulianExpirationDate, "y"), "000")
? me.JulianExpirationDate
05245

I am expecting an output of 15041.


What am I doing wrong in both cases?

Formatting the date into a text string and then applying *date/time* functions
to that text string. For that matter, you're using the European d/m/y format;
Access interprets date strings as m/d/y (ignoring any regional settings on
your computer).

This was answered yesterday; the webpage might be mislaying your posts (so you
might not even see this). Lose the Format function -

Me.JulianExpirationDate = Format(DateAdd("yyyy", 5, Date()), "yy") &
Format(DatePart(DateAdd("yyyy", 5, Date())), "y", "000")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top