Numeric data in text field

M

Mary

Is there any way (I don't think so but thought I'd ask) to
pull the numeric entries in a text field. I have an
Access 97 database linked to Oracle. I have no control
over the type of fields. One of the entries looks like
this: Impacts DATA Services. Overall circuits: #

Following the colon there will be a number. We need all
of this data, but would also like a quick way to get the
total circuit count for all records that have this
statement. If this is possible, please let me know.

Thanks!
Mary
 
G

Guest

Try...
CircNum: right([yourfield],len([yourfield]-instr
([yourfield],":"))

Instr() gives you the character position of the first
occurence of the sought string in the field. Having
extracted the numeric, you can then total on the computed
field CircNum.

Hope this works for you.
 
G

Guest

Forgot to transform text to value...
CircNum: Val(right([yourfield],len([yourfield]-instr
([yourfield],":")))
Click to expand...

Sorry. Hope THIS works.

-----Original Message-----
Try...
CircNum: right([yourfield],len([yourfield]-instr
([yourfield],":"))

Instr() gives you the character position of the first
occurence of the sought string in the field. Having
extracted the numeric, you can then total on the computed
field CircNum.

Hope this works for you.
-----Original Message-----
Is there any way (I don't think so but thought I'd ask) to
pull the numeric entries in a text field. I have an
Access 97 database linked to Oracle. I have no control
over the type of fields. One of the entries looks like
this: Impacts DATA Services. Overall circuits: #

Following the colon there will be a number. We need all
of this data, but would also like a quick way to get the
total circuit count for all records that have this
statement. If this is possible, please let me know.

Thanks!
Mary
.
Click to expand...
.
Click to expand...
 
D

Damon Heron

The first part of your question is easy. Assuming two text boxes on a form,
the txtField and the txtReturned, put the following in your form's current
event:
Private Sub Form_Current()
Dim x As String
Dim SearchChar As String
Dim L As String
Dim CharPos As String
x = Me!txtField
L = Len(x)
SearchChar = ":"
CharPos = InStr(1, x, SearchChar, 1)
x = Right(x, (L - CharPos))
Me!txtReturned = Val(x)
End Sub
Since you don't have control over the tables, you can't add a field to store
the resulting integer value - unless you create another table in Access to
hold the number(s) returned. From there you can run a sum of the numbers...

HTH
Damon
 
F

fredg

Is there any way (I don't think so but thought I'd ask) to
pull the numeric entries in a text field. I have an
Access 97 database linked to Oracle. I have no control
over the type of fields. One of the entries looks like
this: Impacts DATA Services. Overall circuits: #

Following the colon there will be a number. We need all
of this data, but would also like a quick way to get the
total circuit count for all records that have this
statement. If this is possible, please let me know.

Thanks!
Mary
Click to expand...

As long as there is only one colon in the field and the number is
immediately afterward.

NumberValue:Val(Mid([FieldName],InStr([FieldName],":")+1))
 
Top