Naming files with Date built into the name

[phweston]

I am in the process of exporting a text file, and I need to name it using the current date and some text as the file name.

The nameing convention goes something like:

YYYYMMDDhhmmss_sometext

When I set this up using Month and Day functions, I would like to see the single digit months, days, hours, minutes, and seconds to show up as 2 digits.

For example, if I use Month(now), this will return a "9", instead of "09". I tried using the Format function, but that doesn't work. Any suggestions.


Here is part of the code that I am using:


SaveDate = Now
YearDate = Year(SaveDate)
MonthDate = Format(Month(SaveDate), mm)
DayDate = Format(Day(SaveDate), dd)
HourDate = Hour(SaveDate)
MinuteDate = Minute(SaveDate)
SecondDate = Second(SaveDate)

exportfile = YearDate _
& Format(MonthDate, mm) _
& Format(DayDate, dd) _
& Format(HourDate, hh) _
& Format(MinuteDate, mm) _
& Format(SecondDate, ss) _
& "_holdings"

DoCmd.TransferText acExportDelim, "Testexport Export Specification", "tblCharfinalData", "s:\federated\_technology\" & exportfile & ".txt", no


The file name came out as "200393112254_holdings"

 

[jrbarnett]

Are you sure you are using the format function correctly?

Format (Now, "mm&quot returns 08

Format (Now, "hh&quot returns 16 (I am in the uk - GMT timezone if this seems out)

To get a leading zero if there isn't one:

Format (Format (Now, "m&quot, "00&quot

but this is a bit of a waste of time as you can put two M's in the first set of quotes.
By the way - you don't need to extract all the individual elements of the date - you can use it all from the first one. The following should fix it:

exportfile = Format (Year(SaveDate), "yyyy&quot _
& Format(SaveDate, "mm&quot _
& Format(SaveDate, "dd&quot _
& Format(SaveDate, "hh&quot _
& Format(SaveDate, "mm&quot _
& Format(SaveDate, "ss&quot _
& "_holdings"

John

 

[bdmclennan]

Afternoon,

Minutes should be formated as nn. Try this

format(now(),"YYYYMMDDHHNNSS&quot & "_HOLDING"

it will return:
20030903172429_HOLDING

Cheers

bruce

 

[phweston]

Thanks for all your help!

I was on the right track. I didn't enclose the "YYYYMMDDhhnnss" in quotes.

It works great now!

Again, thanks

 

[markphsd]

why not just view the properties for the file... that always has the date it was created.

just a thought.

Mark P.

Bleh

 

[LittleSmudge]

why not just view the properties for the file... that always has the date it was created.

Yes Mark - but if you need to store numerous versions of a file in the same folder ( Lots of "_Holding" files - as per earlier example ) then you need to differentiate them in their NAME.


BTW.
The great advantage of YYYYMMDD in that particular order is that Windoz Explorer then automatically list them in a chronological order ( Whereas DDMMYY jumbles them up ).






G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[markphsd]

oh, well i just use the right click option and view details of the explorer window. i can then sort by date created, modified, file name.. oh.. and size.

you can also write a file viewer in vba. i have one for clients..

when i save files, if they had a version number, i save it with the version number at the end of the name. But i can see the relevance of dates in the name if it makes it easier.

Mark P.

Bleh