Date formatting

[Guest]

I am appending data from a linked excel worksheet into an Access table. The
date field in Excel is formatted as 00/00/0000. When I append the data into
Access it appends as 0/00/0000. Is there anyway to format Access to accept
00/00/0000. I need this format do to some formulas that look at the left 2
characters to perform calculations. These calculations do not work for single
digit months. For example, 1/21/2007. The formula pulls 1/, instead of 01.
Please help, I do not want to have to re-write all the formulas.
Thanks,
Rob

 

[Douglas J. Steele]

I would recommend using the Month, Day and Year functions, rather than
relying on the position in the date.

What happens when someone whose Short Date format is other than mm/dd/yyyy
uses your application?

 

[Rick Brandt]

I am appending data from a linked excel worksheet into an Access
table. The date field in Excel is formatted as 00/00/0000. When I
append the data into Access it appends as 0/00/0000. Is there anyway
to format Access to accept 00/00/0000. I need this format do to some
formulas that look at the left 2 characters to perform calculations.
These calculations do not work for single digit months. For example,
1/21/2007. The formula pulls 1/, instead of 01. Please help, I do not
want to have to re-write all the formulas.
Thanks,
Rob

If you are putting this into a field with a Data Type of DateTime then it
doesn't matter as formatting does not affect how dates are stored, only how they
are displayed. Your formula method is a flawed design for working with dates.
Either change the formula or store your data in a Text field.

 

[Guest]

I tried changing to a textfield, but got the same results.

 

[Douglas J. Steele]

If you're insisting on using your flawed approach, you can force the
preceding zero using:

Right("0" & TextDate, 10)

You're mentioning, though, that you're getting 1/21/2007. Are you in fact
getting 1/01/2007 for the first 9 days of the month? If you're only getting
1/1/2007, you're still going to need to change your approach. One way to
deal with the text is to use the Split function to split the value into the
three components.

Split(TextDate, "/")(0) will give you the month,Split(TextDate, "/")(1) will
give you the day, Split(TextDate, "/")(2) will give you the year.