Senin, 09 April 2012

RE: [MS_AccessPros] Extracting left most number through query

 

Abdul-

That's a bit confusing because the first "number" in the string "EM1 udr 3" is
1. Assuming you mean the first number that's preceded by a space, you could
write a function:

Public Function FindNumber(strValue As String) As String
Dim strWork As String, intI As Integer, intFound As Integer

' Copy the string into a working variable
strWork = strValue
' Loop until found
Do Until intFound Or Len(strWork) = 0
' Get the next space
intI = InStr(strWork, " ")
' If no space found,
If intI = 0 Then
' Clear strWork to end the loop
strWork = ""
Else
' Strip off everything up to and including the space
strWork = Mid(strWork, intI + 1)
' See if it starts with a number
If IsNumeric(Left(strWork, 1)) Then
' Set Found!
intFound = True
Else
End If
End If
Loop
' If found it,
If intFound Then
' Find the next space
intI = InStr(strWork, " ")
' If no more spaces,
If intI = 0 Then
' Return the rest of the string
FindNumber = strWork
Else
' Return everything up to the space
FindNumber = Left(strWork, intI – 1)
End If
End If
End Function

Results:

?FindNumber("EM1 udr 3")
3
?FindNumber("C1/2 HW dr 4 C1 MM udr 5/0 C1 UM udr 10/0 H2 dr 10")
4
?FindNumber("C1 SEL udr 18/0")
18/0

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

---------------------------------------------------

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Abdul Shakeel
Sent: Tuesday, April 10, 2012 8:15 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Extracting left most number through query

 
Dear All

I have a product table with product name like following in it

EM1 udr 3
C1/2 HW dr 4 C1 MM udr 5/0 C1 UM udr 10/0 H2 dr 10
C1 SEL udr 18/0
C1/2 EL udr 18/0
EL1 F 16/0

I am trying to create a query to extract the left most number from product
name let say

from EM1 udr 3 I want to extract 3
from C1/2 HW dr 4 I want to extract 4
from C1 MM udr 5/0 I want to extract 5/0 and so on...

any idea??

[Non-text portions of this message have been removed]

__._,_.___
.

__,_._,___

Tidak ada komentar:

Posting Komentar