Help with changing the format of a text date?

  • Thread starter Thread starter Dave
  • Start date Start date
D

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
 
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.
 
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

Jerry Whittle said:
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.


Dave said:
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
Click to expand...
Click to expand...
 
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.


Dave said:
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

Jerry Whittle said:
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.


Dave said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Jerry Whittle said:
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.


Dave said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top