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

for 18/0 & like we have pricing like that

1/0 to 5/0 7200
6/0 to 10/0 6600
and so on

further I have not create any pricing table, I am unable to get an idea how
to design it, because we have dozens of customers and product prices are
different for each of them, should I create separate table for each
customer??? moreover If I create a table for pricing data should be in the
form like this

1 7200
2 7200
3 7200
4 7200
5 7200
6 7300 and so

perhaps access doesn't recognized (1 to 5 = 7200 ) OR (1-5 = 7200) for
lookup

have you any idea for setting an array for customers in VBA call the prices
from select case...

On Tue, Apr 10, 2012 at 3:03 PM, John Viescas <JohnV@msn.com> wrote:

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