How to Extract Part Of field Based on Criteria

[doyle60]

How do I return a part of a field if that part matches a certain
format? In this case, being a three digit number (###), or a six
digit number (###-###) followed by a letter g.

More specifically, I have a field for fabric (FabricAdj) which runs
1000 or so lines. Part of the data looks like this:

92% cotton 8% spandex, 32's 220g
100% Cotton Woven 40/180 125-130g
92% cotton 8% spandex, 195g Brushed
92% cotton 8% spandex
92% cotton 8% spandex, 165-175g

In a query, I want to return just the numbers before the g
(representing grams).

220g
125-130g
195g
[This should return a null value]
165-175g

The g figures (the weight of the fabric, representing grms/2) are
standardized into the format ###g or ###-###g.

I know how to return a "Yes" if part of the cell has such a figure:

Grams: IIf([FabricAdj] Like "*" & "###" & "g" & "*","Yes","")

But instead of returning that "Yes," I want to return the actual
numbers, plus the "g."

Thanks,

Matt

 

[Stefan Hoffmann]

hi Matt,

92% cotton 8% spandex, 32's 220g
100% Cotton Woven 40/180 125-130g
92% cotton 8% spandex, 195g Brushed
92% cotton 8% spandex
92% cotton 8% spandex, 165-175g

If the weights are not separated from the 'g' then you can use Split:

Public Function GetWeight(AString As String) As String

Dim a() As String
Dim i As Long

a() = Split(AString, " ")

For i = UBound(a()) To LBound(a()) Step - 1
If Right(a(i), 1) = "g" Then
GetWeight = Left(a(i), Len(a(i)) - 1
Exit Function
End If
Next i
GetWeight = "n/a"

End Function


mfG
--> stefan <--

 

[doyle60]

Thanks. But is there a way to do this in a query without a function?

Thanks,

Matt

 

[Douglas J. Steele]

Not really.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. But is there a way to do this in a query without a function?

Thanks,

Matt

 

[doyle60]

I've tried the function below, anyway. But my database doesn't know
what Split is. I have Access 1997. Also, the line "GetWeight =
Left(a(i), Len(a(i)) - 1" appears in red. I tried fixing it but
without success.

Thanks,

Matt

 

[Douglas J. Steele]

Slight typo. That should be

GetWeight = Left(a(i), Len(a(i) - 1)

However, you're correct that Access 97 doesn't have a Split function. Grab
the code from http://support.microsoft.com/kb/188007: it works in Access 97.
(Note: You'll need to change the declarations from "As VbCompareMethod" to
"As Long"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I've tried the function below, anyway. But my database doesn't know
what Split is. I have Access 1997. Also, the line "GetWeight =
Left(a(i), Len(a(i)) - 1" appears in red. I tried fixing it but
without success.

Thanks,

Matt

 

[doyle60]

GetWeight = Left(a(i), Len(a(i) - 1)

That correction still does not work. Should it be this:

GetWeight = Left(a(i), Len(a(i) - 1))

Also, if I use the Split function from the site you directed me to, I
get an error on the ReadUntil function. So I suppose I have to put
that in too. I did. But I can't, for the life of me, correct the
first part which uses a line continuation _. There is something wrong
with my continuation line ( _) that always puzzles me in my system. I
usually just create long lines, heck if I care. Anyway, it doesn't
work with what is below. I get a compile error on the first three
lines:

Public Function ReadUntil(ByRef sIn As String, _
sDelim As String, Optional bCompare As VbCompareMethod _
= vbBinaryCompare) As String
Dim nPos As String
nPos = InStr(1, sIn, sDelim, bCompare)
If nPos > 0 Then
ReadUntil = Left(sIn, nPos - 1)
sIn = Mid(sIn, nPos + Len(sDelim))
End If
End Function

Can you direct me in what to do? Thanks,

Matt

 

[Douglas J. Steele]

Sorry, it should be:

GetWeight = Left(a(i), Len(a(i)) - 1)

You're getting the length of element a(i) and subtracting 1 from it, then
taking that number of left most characters.

As I said, you need to replace "As VbCompareMethod" with "As Long":

Public Function ReadUntil(ByRef sIn As String, _
sDelim As String, Optional bCompare As Long _
= vbBinaryCompare) As String

 

[Stefan Hoffmann]

hi,

Thunderbird displays here a lot of ?, instead of the correct chars.
Have i used the wrong encoding?




mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Douglas,

GetWeight = Left(a(i), Len(a(i)) - 1)

Yeah, shame upon me for that...)


mfG
--> stefan <--

 

[doyle60]

Thanks Steffen and Doug.

Doug,
I did make the substitution to "As Long" but after the failure fooled
around a bit and copied the wrong thing here. The line continuation _
somehow gets screwed up when I do it. I copied your line and now it
works.

Steffen and Doug,
After I changed the line to GetWeight = Left(a(i), Len(a(i)) - 1), I
got a compile error on another line. It says "Can't assign to array"
and highlights the "a()" in the line "a() + Split(AString, " ")".

Here is the code again:

Public Function GetWeight(AString As String) As String

Dim a() As String
Dim i As Long

a() = Split(AString, " ")

For i = UBound(a()) To LBound(a()) Step -1
If Right(a(i), 1) = "g" Then
GetWeight = Left(a(i), Len(a(i)) - 1)
Exit Function
End If
Next i
GetWeight = "n/a"


End Function

Thanks,

Matt

 

[Stefan Hoffmann]

hi Matt,

After I changed the line to GetWeight = Left(a(i), Len(a(i)) - 1), I
got a compile error on another line. It says "Can't assign to array"
and highlights the "a()" in the line "a() + Split(AString, " ")".

Dim a() As String
a() = Split(AString, " ")

As the Split()-replacment returns a differnt type, you need to change
these lines:

Dim a As Variant
a = Split(AString, " ")

The rest should work.


mfG
--> stefan <--

 

[doyle60]

Thanks so much. The above, however, didn't work. But do not bother to
correct it; I had to complete the project and did so by hand, using a
simple query to do half the job and than making hand adjustments.

Matt