Abdul-
What do you do with a value like 18/0?
You might be able to use the AfterUpdate event of the Product ID in you order
details. Call the function to get the number, then look it up based on the
customer in the outer form. How is your pricing table set up?
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)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Abdul Shakeel
Sent: Tuesday, April 10, 2012 9:41 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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]
>>
>>
>>
>
>
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar