FORCING LEADING ZEROS using Date/Time format ---- MS ACCESS 2000/XP

[Keith Hutchison]

Hello,

I'm having a problem forcing leading zeros using the date/time format in
access.
I want to show this: 01/06/2004 <---- mm/dd/yyyy (A fixed length)

The problem is when I setup a format mask it changes to this: 1/6/2004


This is a problem because my .asp page uses the string function mid like
this: monthholder = mid(database_date, 1, 2)
if done correctly the variable monthholder should have this: 01

instead it comes back with : 1/ (because access doesn't keep the
leading zero)


GRRRRR........


I tried to setup a format mask with the Date/Time like this 99/99/9999
I also tried 00/00/0000 I also tried ##/##/####
Nothing is working. Everytime I enter a date into the field and click away
it deletes the darn zeros!!!


I've searched my heart out on all search engines and came up with nobody
experiencing the same problem. I know I could just change it to a text
format but I thought there was a performance advantage with using the
Date/Time format...

Can anybody help me?
Thanks for any/all responses.

Kind regards,
Keith

 

[Allen Browne]

I think you are talking about the Format property here (not an Input Mask)?

If so, open the Windows Control Panel | Regional Settings, and see how Short
Date is defined there. The format property and the Format() function in
Access still interacts with the Windows settings and fudge things around
based on what is in Control Panel.

 

[Keith Hutchison]

I took care of it by making it a ****ing text field. I ought to strangle
the programmer(s) that made the month and day fields only 1 character (when
0-9)

For anyone else having the same problem here is what I did:

the column is a "text" field

Input mask set to: 00/00/00

so 01/09/04 actually comes out 01/09/04 !!!!!!!!!!!!!!!!!!!!

Before it came out with some 10/3/1928 crazy shit. (WTF??? 1928???? )
Whatever....

These three lines of code break apart the date into month, day and year
separately. It also typecasts it into an integer that you can work with
because the field is currently a text field...

monthholder = Cint(mid(dateholder, 1, 2))

dayholder = Cint(mid(dateholder, 4, 2))

yearholder = Cint(mid(dateholder, 7, 2))


Hope this helps somebody.


Thanks for you help Allen.

 

[Dirk Goldgar]

Hello,

I'm having a problem forcing leading zeros using the date/time format
in access.
I want to show this: 01/06/2004 <---- mm/dd/yyyy (A fixed length)

The problem is when I setup a format mask it changes to this:
1/6/2004


This is a problem because my .asp page uses the string function mid
like this: monthholder = mid(database_date, 1, 2)
if done correctly the variable monthholder should have this: 01

instead it comes back with : 1/ (because access doesn't keep the
leading zero)


GRRRRR........


I tried to setup a format mask with the Date/Time like this
99/99/9999 I also tried 00/00/0000 I also tried ##/##/####
Nothing is working. Everytime I enter a date into the field and
click away it deletes the darn zeros!!!


I've searched my heart out on all search engines and came up with
nobody experiencing the same problem. I know I could just change it
to a text format but I thought there was a performance advantage with
using the Date/Time format...

Can anybody help me?
Thanks for any/all responses.

Seems to me your best bet would be either to format the date as you want
before extracting the substring:

monthholder = Mid(Format(database_date, "mm/dd/yyyy"), 1, 2)

or to use the Month() function to get the month number, then format that
to 2 digits:

monthholder = Format(Month(database_date), "00")