Parsing text string (1 field) to number and date (2 fields)

[kennedyr4]

I have a query with one field (text) that needs to be parsed into two
separate fields (num, date)

Ie.

Field 1 - [Hits]

Number of hits: 22 since 2005-09-21.
Number of hits: 1 since 2006-03-14.
Number of hits: 534 since 2005-09-20.
Number of hits: 212 since 2005-10-07.
Number of hits: 4231 since 2006-03-14.
Number of hits: 54 since 2005-09-20.
Number of hits: 1217 since 2005-10-07.
Number of hits: 1 since 2006-03-14.


I would like it to be:

Field 2 - [NumHits] Field 3 - [Date]

22 2005-09-21
1 2006-03-14
534 2005-09-20
212 2005-10-07
4231 2006-03-14
54 2005-09-20
1217 2005-10-07
1 2006-03-14

The original text string always follows the same format with the only
variable being the size of the integer between (Hits and (since). All text
strings end with a (.)

Any suggestions would be helpful. I'm hoping that there an easy way of doing
this since I am not a code writer. I'm using Access 2003.

 

[Tom Ellison]

Dear Kennedy:

By finding the index (position) of the word "since" you should be able to do
this. I'll call this P.

The NumHits is at position 17 and its length is P - 18.

The [Date] begins at position P + 6.

The code would be:

For NumHits
Mid([Hits], 17, InStr([Hits], "since") - 18

For [Date]
Mid([Hits], InStr([Hits], "since") + 6, 10)

Please let me know if this works for you.

Tom Ellison

 

[mscertified]

To get the date
LEFT$(RIGHT$(MyString,11),10)
To get the number
LEFT$(MyString,INSTR(MyString," "))

Untested but give it a shot

-Dorian

 

[Klatuu]

Try this:
strParseIt=replace([hits]," ","")
strParseIt=replace(strParseIt,".","")
strParseIt=replace(strParseIt,"numberofhits:","")
varTwoFieds=split(strParseIt,"since")
[num] = varTwoFields(0)
[date] = varTwoFields(1)