 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / March 2007How to Extract Part Of field Based on Criteria
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 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 <-- Thanks. But is there a way to do this in a query without a function? Thanks, Matt > hi Matt, > [quoted text clipped - 26 lines] > mfG > --> stefan <-- Not really.  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) Thanks. But is there a way to do this in a query without a function? Thanks, Matt > hi Matt, > [quoted text clipped - 26 lines] > mfG > --> stefan <-- hi, Thunderbird displays here a lot of ?, instead of the correct chars. Have i used the wrong encoding? >> � �Dim a() As String mfG --> stefan <-- 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 > hi Matt, > [quoted text clipped - 26 lines] > mfG > --> stefan <-- 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" SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (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 > hi Matt, > [quoted text clipped - 26 lines] > mfG > --> stefan <-- > 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 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 SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) >> GetWeight = Left(a(i), Len(a(i) - 1) > [quoted text clipped - 25 lines] > > Matt hi Douglas, > GetWeight = Left(a(i), Len(a(i)) - 1) Yeah, shame upon me for that...) mfG --> stefan <-- 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 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 <-- 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.