Convert text to number or date

[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

 

[Jeff Boyce]

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

 

[Douglas J. Steele]

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)

 

[John Spencer]

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

 

[Arvin Meyer [MVP]]

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

 

[Arvin Meyer [MVP]]

Best answer.

 

[Luis]

Thank you, Luis

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

 

[Luis]

Thank you, Luis

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!)

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