Year & Date issue. Show day of year as three numbers.

[HeadGasket]

I looked throught the Help on Access and I might not be able to do this but maybe one of you wizards out there knows a trick or two?

I'm going to try using the year, day of year, hour & minute (24 hour clock) as a report number. It's set up in a field on a table. Right now I have....

Default Value =Format(Now(),"yyyhhnn") 'which works but not exactly how I would like

yy = Last two digits of the year
y = Number of the day of the year (1 to 366) 'can this show three digits all the time?
hh = Hour in two digits (00 to 23)
nn = Minute in two digits (00 to 59)

For instance, right now for Jan. 10th, 2012, 1306 hours the result would be 12101304 which, for all intents and purposes works, but I would prefer the "day of the year" to always be represented by three digits and not just when it hits day 100 of the year.



I would prefer to see 120101304

Thanks in advance,

-David

[orange]

Why? If you want unique numbers why not use an autonumber--just curious.
You can always display/print Now() for the timestamp.

This should do what you want though
ReportNum = format(Now,"yy") & format(datepart("y",Now),"000")& format (Now,"hhnn")

[jzwp11]

Since the value you are trying to come up with is essentially a calculated value derived from the date/time, you would not store it in the table but calculate and display it on the fly when you need it. You would store the date/time.

I would do something like this

=right(year(mydatefield),2) & format(datediff("d",dateserial(year(mydatefield)-1, 12,31), mydatefield),"000") & format(datepart("h",mydatefield),"00") & format(datepart("n",mydatefield),"00")

[HeadGasket]

Thanks, orange.

Don't have a good explanation as to why I'm doing this and I admit it's a bit odd. I just can't seem to commit to a number to start out for Autonumber and starting at 00001 wouldn't go over with management (for this type of application). Goofy. I know.

The bigger thing is that I'm a total noob and haven't figured out all the in's and out's of Access. Most of it is still a big mystery and the Help info within the program is pretty cool.....if you already know practically everything and need a quick reminder.

Thanks for the help, though. I appreciate it.

-David

[jzwp11]

By the way, if you are using this in a multi-user environment, you could conceivable have 2 people enter records within the same minute and you would end up with a non-unique value. You might consider including the seconds as a term in your expression.