Change string to date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a string such as 122403 that I need to change to a date. When I use format() it gives me the numerical date. Which step am I missing?

Expr1: Format(Left([fApptCreated],6),"mm/dd/yy"

Thank

Da
 
Parse the string into its component parts (day, month and year), and use
DateSerial to convert it:

Expr1: DateSerial(Right([fApptCreated],2), Left([fApptCreated],2),
Mid([fApptCreated],3, 2))



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Daniel Dickover said:
I have a string such as 122403 that I need to change to a date. When I
Click to expand...
use format() it gives me the numerical date. Which step am I missing?
Expr1: Format(Left([fApptCreated],6),"mm/dd/yy")

Thanks

Dan
Click to expand...
 
One method:

DateValue(Format(Left("120304xx",6),"@@/@@/@@")) returns 12/3/04 if you want
that displayed as Dec 3, 2004 you would need to format it again

Format(DateValue(Format(Left("120304xx",6),"@@/@@/@@")),"mmm d, yyyy")

Or you can use the DateSerial function along with the string functions to return
a date and then format that. See Douglas Steele's post, which I just noticed.
His method is probably better, although you may need to modify it slightly if
your field has more than six characters (which I read as a possibility since you
were using the Left function to get the first 6 characters. If your field is
always six characters, then there is no need to use the left function in my
example above.
 
Is there a CDATE function in access. If there is, you may try that.


John Spencer (MVP) said:
One method:

DateValue(Format(Left("120304xx",6),"@@/@@/@@")) returns 12/3/04 if you want
that displayed as Dec 3, 2004 you would need to format it again

Format(DateValue(Format(Left("120304xx",6),"@@/@@/@@")),"mmm d, yyyy")

Or you can use the DateSerial function along with the string functions to return
a date and then format that. See Douglas Steele's post, which I just noticed.
His method is probably better, although you may need to modify it slightly if
your field has more than six characters (which I read as a possibility since you
were using the Left function to get the first 6 characters. If your field is
always six characters, then there is no need to use the left function in my
example above.

Daniel said:
I have a string such as 122403 that I need to change to a date. When I use format() it gives me the numerical date. Which step am I missing?

Expr1: Format(Left([fApptCreated],6),"mm/dd/yy")

Thanks

Dan
Click to expand...
Click to expand...
 
Back
Top