ACCESS FEED: Re: [MS_AccessPros] Extracting left most number through query

Selasa, 10 April 2012

Re: [MS_AccessPros] Extracting left most number through query

Dear John

You are right the the first number in EM1 udr 3 is one but as I said I want
to extract left most number which is in this case is 3....any how the
function works fine thanks a lot....

On Tue, Apr 10, 2012 at 11:46 AM, John Viescas <JohnV@msn.com> wrote:

> **
>
>
> 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]
>
>
>