Selasa, 10 April 2012

RE: [MS_AccessPros] Extracting left most number through query

 

Abdul-

Is the string you are parsing part of the product description? If so, then I
assume that the code number gives you the price. Your pricing table might look
like:

CustomerID
LowRange
HighRange
Price

But be careful getting a range with a string like 6/0 to 10/0. As a string
(text - it can't be a number), 10/0 is lower in collating sequence than 6/0.
You might need a value for each item within the range.

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 12:50 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
>
>
>

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

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

Yahoo! Groups Links

__._,_.___
.

__,_._,___

Tidak ada komentar:

Posting Komentar