Text to Column query??

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

Guest

I have a field [DateWeek] that has the following information in it:

Week 1 - 1/1/06
Week 2 - 1/8/06
and so on until Week 52

I need just to extract the date from this.
I would try to use Right(), but the number can vary up to 8 so that will not
work.

Any help?
 
Never fails....
As soon as I post, I figure it out..

Replace([DateWeek],"Week - *","")

*********************************
 
This is the reason, you should never store separate bits of information in
the same field. If they were two different fields, you wouldn't have this
problem. I strongly suggest you change this.

However if you can't, you can also use the Mid() function in conjunction
with the Instr() function. Something like this:

Mid([DateWeek], InStr([DateWeek], "-")+1)

The InStr function returns the position of one string within another.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
I couldn't agree more with you.
Thanks Roger!

Roger Carlson said:
This is the reason, you should never store separate bits of information in
the same field. If they were two different fields, you wouldn't have this
problem. I strongly suggest you change this.

However if you can't, you can also use the Mid() function in conjunction
with the Instr() function. Something like this:

Mid([DateWeek], InStr([DateWeek], "-")+1)

The InStr function returns the position of one string within another.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



TimT said:
I have a field [DateWeek] that has the following information in it:

Week 1 - 1/1/06
Week 2 - 1/8/06
and so on until Week 52

I need just to extract the date from this.
I would try to use Right(), but the number can vary up to 8 so that will not
work.

Any help?
Click to expand...
Click to expand...
 
Dude,
Your solution works better.
Thanks a bunch!
Tim

Roger Carlson said:
This is the reason, you should never store separate bits of information in
the same field. If they were two different fields, you wouldn't have this
problem. I strongly suggest you change this.

However if you can't, you can also use the Mid() function in conjunction
with the Instr() function. Something like this:

Mid([DateWeek], InStr([DateWeek], "-")+1)

The InStr function returns the position of one string within another.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



TimT said:
I have a field [DateWeek] that has the following information in it:

Week 1 - 1/1/06
Week 2 - 1/8/06
and so on until Week 52

I need just to extract the date from this.
I would try to use Right(), but the number can vary up to 8 so that will not
work.

Any help?
Click to expand...
Click to expand...
 
You're welcome. Glad it worked for you.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


TimT said:
Dude,
Your solution works better.
Thanks a bunch!
Tim

Roger Carlson said:
This is the reason, you should never store separate bits of information in
the same field. If they were two different fields, you wouldn't have this
problem. I strongly suggest you change this.

However if you can't, you can also use the Mid() function in conjunction
with the Instr() function. Something like this:

Mid([DateWeek], InStr([DateWeek], "-")+1)

The InStr function returns the position of one string within another.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



TimT said:
I have a field [DateWeek] that has the following information in it:

Week 1 - 1/1/06
Week 2 - 1/8/06
and so on until Week 52

I need just to extract the date from this.
I would try to use Right(), but the number can vary up to 8 so that
Click to expand...
will
not
work.

Any help?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top