Convert text to number or date

  • Thread starter Thread starter Luis
  • Start date Start date
L

Luis

I have the following text field 9/2009 (date/year) but would like to change
that to a date format. Is there anyway to perfrom that transformation. Is
there anyway to change that using vba or a function.

Guidance and/or suggestions appreciated.

Luis
 
Luis

The problem Access will have with what you are trying to do is that "9/2009"
is NOT a date. If you had "9/1/2009" or "9/15/2009" or "9/30/2009", those
would be dates.

If you can decide to use the 1st or the 15th or the last day of a month,
then you'll be able to change as you've asked.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
9/2009 isn't a date. You need a day in order for it to be a date.

If you're willing to default to, say, the 1st of the month, you can use

DateSerial(Mid([TextValue], InStr([TextValue], "/") + 1), Left([TextValue],
InStr([TextValue], "/") - 1), 1)

(Watch out for word-wrap: that should all be on one line)
 
Have you tried
DateValue("9/2009")
that should return
Sept 1 2009

If you want 9/2009 to return some other value, please explain what value you
do want.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Given that 9/2009 is only part of a date, you cannot convert it to a date.
But you can do:

=CDate(Left([TextField],1) & "/1/" & Right([TextField],4))

which will return: 9/1/2009
 
Thank you, Luis

John Spencer said:
Have you tried
DateValue("9/2009")
that should return
Sept 1 2009

If you want 9/2009 to return some other value, please explain what value you
do want.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
 
Thank you, Luis

Douglas J. Steele said:
9/2009 isn't a date. You need a day in order for it to be a date.

If you're willing to default to, say, the 1st of the month, you can use

DateSerial(Mid([TextValue], InStr([TextValue], "/") + 1), Left([TextValue],
InStr([TextValue], "/") - 1), 1)

(Watch out for word-wrap: that should all be on one line)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Luis said:
I have the following text field 9/2009 (date/year) but would like to change
that to a date format. Is there anyway to perfrom that transformation. Is
there anyway to change that using vba or a function.

Guidance and/or suggestions appreciated.

Luis
Click to expand...
Click to expand...
 
Back
Top