Format Date in Unbound Textbox

[spacepro]

Hi All,

I need to change the format of a date in a unbound textbox. I want to change it from dd/mm/yy to dd.mm.yy, simply changing the / to .

I used a input mask and set the format to dd.mm.yy but still returns the date has dd/mm/yy. Basically I am using the value from a calendar to open a excel file that is named after the date. For example today's file would be 18.01.05.xls.

If this can't be done can I strip the date into 3 unbound textboxes and then concatenate the values using code.

Any suggestions.

Many Thanks

Andy

 

[Pat Hartman]

I would never use "." in a file name. It is simply too confusing. Also, if you want to name files with a date, it is far better to use yyyymmdd format. That way you can order the list and files would fall in to date order. With dd.mm.yy format, the files would fall in to day order. So they would be
1-Jan-02
1-Jan-03
1-Jan-04
10-Jan-02
10-Jan-03
10-Jan-04
11-Jan-02
11-Jan-03
11-Jan-04
12-Jan-02
....
2-Jan-02
..
3-Jan-03

Rather than
2002-Jan-1
2002-Jan-2
2002-Jan-3
2002-Jan-4
2002-Jan-5
....

I used month name above so as to not confuse the month/day.

 

[spacepro]

Pat,

Thanks for the advice, however we have no control of the creation of the excel files as they are system generated from a third party contractor. Obviously the files could be renamed, but when the file is generated to the server , I want the user simply to click a button to set an OLE field to view the spreadsheet within Access.

So that we don't give the user access priviledges to the actual excel files, except for viewing from Access. Is there a way to change the format to my suggestion even though this goes again your logical thought process.

Thanks

Andy

 

[WayneRyan]

Spacepro,

Look at the Replace function. You can use it before you append the ".xls"
to the filename.

Me.FileName = Replace(Me.FileName, "/", ".") & ".xls"

Wayne

 

[spacepro]

Hi Wayne,

Hope you are keeping well. Thanks for the solution.
Worked a treat.

Take Care
Andy