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

One more question I was extracting the numbers because the price for each
customers is set through these number separately let say

for CustomerA

1 to 5 => 7200
6 to 9 => 7500

for CustomerB

1 to 5 => 6950
6 to 9 => 7300

and so on, could you please give me an idea, that if I am using northwind
like order form how do I lookup product price for each customer as stated
as above

On Tue, Apr 10, 2012 at 12:12 PM, Abdul Shakeel <maxccess@gmail.com> wrote:

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