Help with changing the format of a text date?

[Dave]

I have a date as 20081003 that I want to change to 03-OCT-2008. I have been
able to move the charters around to 03-10-2008, but I have not been able to
figure out how to change the 10 to OCT.

Can you help?

Thanks

 

[Jerry Whittle]

Assuming that the field in question is a text field AND that it contains
nothing but things that CDate can read as valid dates AND always has 8
characters in the order you described (no nulls allowed) the following should
work in a query:

TheDate:Format(CDate(left("[DateField]",4) & "-" & mid("[DateField]", 5,2) &
"-" & right("[DateField]", 2)),"DD-MMM-YYYY")

If you must have OCT instead of Oct, wrap it up in the UCase() function.

 

[Dave]

Thanks for the quick response.

IT is a text filed that is always 8 characters long. I keep getting a Data
type mismatch with the formula. I included the double quotes around the
DateField as you indicated

Assuming that the field in question is a text field AND that it contains
nothing but things that CDate can read as valid dates AND always has 8
characters in the order you described (no nulls allowed) the following should
work in a query:

TheDate:Format(CDate(left("[DateField]",4) & "-" & mid("[DateField]", 5,2) &
"-" & right("[DateField]", 2)),"DD-MMM-YYYY")

If you must have OCT instead of Oct, wrap it up in the UCase() function.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a date as 20081003 that I want to change to 03-OCT-2008. I have been
able to move the charters around to 03-10-2008, but I have not been able to
figure out how to change the 10 to OCT.

Can you help?

Thanks

 

[Jerry Whittle]

Ack! Remove the double quotes arond [DateField]. I messed up cutting and
pasting. Leave the quotes around the DD-MMM-YYYY though.

Sorry about that!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks for the quick response.

IT is a text filed that is always 8 characters long. I keep getting a Data
type mismatch with the formula. I included the double quotes around the
DateField as you indicated

Assuming that the field in question is a text field AND that it contains
nothing but things that CDate can read as valid dates AND always has 8
characters in the order you described (no nulls allowed) the following should
work in a query:

TheDate:Format(CDate(left("[DateField]",4) & "-" & mid("[DateField]", 5,2) &
"-" & right("[DateField]", 2)),"DD-MMM-YYYY")

If you must have OCT instead of Oct, wrap it up in the UCase() function.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a date as 20081003 that I want to change to 03-OCT-2008. I have been
able to move the charters around to 03-10-2008, but I have not been able to
figure out how to change the 10 to OCT.

Can you help?

Thanks

 

[John Spencer]

An alternative is to use the following IsDate part takes care of values
that cannot be a date (nulls, values with letters in them, etc).

The expression returns a date. If you want to format that you need to
wrap things in another Format function call or use the control's format
property.

Date returned
IIF(Isdate(Format([TheField],"@@@@-@@-@@")),CDate(Format([TheField],"@@@@-@@-@@")),Null)

Formatted Date string returned.
IIF(Isdate(Format([TheField],"@@@@-@@-@@")),Format(CDate(Format([TheField],"@@@@-@@-@@")),"dd-mm-yyyy"),Null)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks for the quick response.

IT is a text filed that is always 8 characters long. I keep getting a Data
type mismatch with the formula. I included the double quotes around the
DateField as you indicated

Assuming that the field in question is a text field AND that it contains
nothing but things that CDate can read as valid dates AND always has 8
characters in the order you described (no nulls allowed) the following should
work in a query:

TheDate:Format(CDate(left("[DateField]",4) & "-" & mid("[DateField]", 5,2) &
"-" & right("[DateField]", 2)),"DD-MMM-YYYY")

If you must have OCT instead of Oct, wrap it up in the UCase() function.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a date as 20081003 that I want to change to 03-OCT-2008. I have been
able to move the charters around to 03-10-2008, but I have not been able to
figure out how to change the 10 to OCT.

Can you help?

Thanks