Question regarding a specific expression in a query...

[Guest]

Howdy folks

Just wonder if I could take a couple moments and pick your brains here

I currently have a query that uses an expression to trim a field value an extract only a portion of the text in the field

Example

Expression here
TRUNPID: Trim(Mid([DESCRIPTION],InStr(1,[DESCRIPTION],"-")+1,InStr(1,[DESCRIPTION],",")-5)

where [DESCRIPTION] is the name of the field I am trimming

Actual DESCRIPTION field values
713-HV1234A, Valve, Han
713-TCV1456, Valve, Temperature Contro

The strings returned from my current expression would be
HV1234
TCV145

Now on to my real problem. Instead of having the HV1234A as one string, I want to be able to break that string into multiple fields, something like this
HV 1234
TCV 145

Is there a way to modify my current expression to handle such a function? I was thinking I could make the expression trigger on the actual alpha-numeric characters instead of the punctuation. Unfortunately I have been unable to find the appropriate wildcards to use as part of the InStr command in place of the punctuation. As you can see I can't use defined breaking points as the strings vary in length, sometimes 2 letters, sometimes 3, etc

My code is very sloppy as I am by no means an expert Access Programmer and I appreciate any help you can provide

Jason

 

[TC]

Even if you could do this with expressions within the SQL, they would get
fairly complex, & lead to ugly SQL.

I suggest you write a function:

public function FieldPart (nPart as integer, vField as variant) as string
select case nPart
case 1 ' get first part.
FieldPart = ...
case 2 ' get second part.
FieldPart = ...
case 3 ' get third part.
Fieldart = ...
end select
end function

Then in the SQL:

UPDATE ...
Set ... = FieldPart (1, [DESCRIPTION]),
... = FieldPart (2, [DESCRIPTION]),
... = FieldPart (3, [DESCRIPTION])

Then you could use whatever code you wanted, in the FieldPart() function:
Left, Mid, Right, Instr, looping, if-tests, whatever.

HTH,
TC

Howdy folks,

Just wonder if I could take a couple moments and pick your brains here.

I currently have a query that uses an expression to trim a field value an

extract only a portion of the text in the field.

Example:

Expression here:
TRUNPID: Trim(Mid([DESCRIPTION],InStr(1,[DESCRIPTION],"-")+1,InStr(1,[DESCRIPTION],",
")-5))

where [DESCRIPTION] is the name of the field I am trimming.

Actual DESCRIPTION field values:
713-HV1234A, Valve, Hand
713-TCV1456, Valve, Temperature Control

The strings returned from my current expression would be:
HV1234A
TCV1456

Now on to my real problem. Instead of having the HV1234A as one string, I

want to be able to break that string into multiple fields, something like
this:

HV 1234 A
TCV 1456

Is there a way to modify my current expression to handle such a function?

I was thinking I could make the expression trigger on the actual
alpha-numeric characters instead of the punctuation. Unfortunately I have
been unable to find the appropriate wildcards to use as part of the InStr
command in place of the punctuation. As you can see I can't use defined
breaking points as the strings vary in length, sometimes 2 letters,
sometimes 3, etc.

My code is very sloppy as I am by no means an expert Access Programmer and

I appreciate any help you can provide.

 

[Guest]

Cool, that cleaned up my code a lot. Took a bit of tinkering but I got that module working. I appreciate your help TC.

Now, the final part of my original post was whether or not I could use those TRIM, MID, RIGHT functions to trim just the numerics from a string. Example, what might the code look like to strip out just the number in this string?

HV1002A

where HV is variable length and A may or may not exist on the string.

Same question applies to stripping just the alpha characters off the front to a new field if the alpha characters are not a set length.

 

[TC]

Cool, that cleaned up my code a lot. Took a bit of tinkering but I got that module working. I appreciate your help TC.

Now, the final part of my original post was whether or not I could use those TRIM, MID, RIGHT functions to trim just the numerics from a string. Example, what might the code look like to strip out just the number in this string?

HV1002A

where HV is variable length and A may or may not exist on the string.

Same question applies to stripping just the alpha characters off the front to a new field if the alpha characters are not a set length.

You could write a loop to look for thr first numeric digit, then take
all successive numeric digts, stopping when you get to the end of the
string, or a non-numeric digit.

Or, I think that the Val() function will stop on the first non-digit
(but I don't have Access here to check). That is, Val("1002A") = 1002
(I think). If so, this would do it:

(untested)

dim the_string as string, the_number as integer
the_string = "HV1002A" ' or whatever.

dim s as string, n as integer
s = the_string & "X"
n = 1
while not (mid$ (s, n, 1) like "[0-9]")
n = n + 1
wend
the_number = val (mid$ (s, n))

That code would return 0 if there was no number in the string. You
could alter it to cause an error if there was no number in the string.

HTH,
TC

 

[TC]

Oops! The code I just posted, is not correct. The easiest fix is to
change this:

s = the_string & "X"

to this:

s = the_string

or just use the_string directly, in place of s.

The amended code assumes that there >is< a number in the string. It
will fail with a runtime error, if there isn't. The original code will
also fail (not return zero) if there's no number in the string. The
coding error can not be fixed by changing 'X' to '0' in the s=
statement. You may like to find out why, as an exercise

TC

 

[Guest]

Thanks TC

I'm going to plug in some of that code and give it a shot

Jason

 

[Guest]

For anyone interested, here is the first part of the code which strips off the alpha characters from the front of the string. This also takes into account the variable length that the alpha characters might be and picks up the entire alpha string until it hits a numeric character.

Big thanks to TC for the assist

Public Function EquipType(fullpid As String) As Strin

Dim equip As Strin

Dim s As String, n As Intege
s = fullpi
n =
While Not (Mid$(s, n, 1) Like "[0-9]"
n = n +
Wen

EquipType = IIf(Val(Left$(s, n)) = 0, Left$(s, n - 1), ""

End Functio

Jason