need help sorting text by trailing Numbers Value

I am looking for some help sorting a text field (PartNumbers) alphabetically 
but correctly depending on the value of the ending few charachters if theyre 
numbers..

Here is some sample data

an960pd10
an960pd300
an960pd6

i would like it sorted like this
an960pd6
an960pd10
an960pd300

any ideas?
Thanks
Barry
0
Utf
3/4/2010 7:18:04 PM
access.reports 4434 articles. 0 followers. Follow

9 Replies
1125 Views

Similar Articles

[PageSpeed] 54

I looks like your values have a fixed length to the left of the numbers. If 
this is true, you can use the following in the Sorting and Grouping dialog 
expression:

=Left([PartNumbers],7)
=Val(Mid([PartNumbers],8))

-- 
Duane Hookom
Microsoft Access MVP


"Barry A&P" wrote:

> I am looking for some help sorting a text field (PartNumbers) alphabetically 
> but correctly depending on the value of the ending few charachters if theyre 
> numbers..
> 
> Here is some sample data
> 
> an960pd10
> an960pd300
> an960pd6
> 
> i would like it sorted like this
> an960pd6
> an960pd10
> an960pd300
> 
> any ideas?
> Thanks
> Barry
0
Utf
3/4/2010 7:44:02 PM
Duane

Thanks for looking at my post..  Im sorry but i gave a pretty poor example 
of my sample data as they are not fixed length PartNumbers and i forgot some 
have a trailing alpha.

i tried out your suggestion in a query and although my results (because of 
the non fixed length) are a little goofy you have me heading in the right 
direction.

Unless maybe my data is too complex and needs a little VBA code??

here is what a hardware p/n consists of
an960pd416L
an960 is the style
pd is the material
416 is the size
L is a revision attribute

Here is another sample
an960-416
an960 is the style
- is added in because material is not specified
416 is the size 
and there is no revision

additional samples
ms28778-1-034A
ms28778-1-066A
ms28778-1-4

so my sort needs to (starting from the end of the partNumber) first split 
off a trailing alpha charachter (if Present)

Next get value of all numeric charachters to the next Alpha (hopefully this 
includes - /#)

Now sort first by whats left of the leading part numbers
then by the value of the extracted numerical section
and lastly by the trailing alpha if present

here is the query where i tried your earlier suggestion

SELECT T_PartNumbers.PartNumber
FROM T_PartNumbers
ORDER BY Left([PartNumbers],7), Val(Mid([PartNumbers],8));

I have been trying to add a sort value field but ive only covered a few 
hundred records of a few thousand.

Thanks for any help
Barry

"Duane Hookom" wrote:

> I looks like your values have a fixed length to the left of the numbers. If 
> this is true, you can use the following in the Sorting and Grouping dialog 
> expression:
> 
> =Left([PartNumbers],7)
> =Val(Mid([PartNumbers],8))
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "Barry A&P" wrote:
> 
> > I am looking for some help sorting a text field (PartNumbers) alphabetically 
> > but correctly depending on the value of the ending few charachters if theyre 
> > numbers..
> > 
> > Here is some sample data
> > 
> > an960pd10
> > an960pd300
> > an960pd6
> > 
> > i would like it sorted like this
> > an960pd6
> > an960pd10
> > an960pd300
> > 
> > any ideas?
> > Thanks
> > Barry
0
Utf
3/6/2010 1:24:07 AM
Please provide the "sort" expressions from multiple actual part numbers.
If I were you, I would immediately change the structure so a field stores a 
single value/item.


-- 
Duane Hookom
MS Access MVP


"Barry A&P" <BarryAP@discussions.microsoft.com> wrote in message 
news:DC1729BB-D3E8-4504-B344-07989B244352@microsoft.com...
> Duane
>
> Thanks for looking at my post..  Im sorry but i gave a pretty poor example
> of my sample data as they are not fixed length PartNumbers and i forgot 
> some
> have a trailing alpha.
>
> i tried out your suggestion in a query and although my results (because of
> the non fixed length) are a little goofy you have me heading in the right
> direction.
>
> Unless maybe my data is too complex and needs a little VBA code??
>
> here is what a hardware p/n consists of
> an960pd416L
> an960 is the style
> pd is the material
> 416 is the size
> L is a revision attribute
>
> Here is another sample
> an960-416
> an960 is the style
> - is added in because material is not specified
> 416 is the size
> and there is no revision
>
> additional samples
> ms28778-1-034A
> ms28778-1-066A
> ms28778-1-4
>
> so my sort needs to (starting from the end of the partNumber) first split
> off a trailing alpha charachter (if Present)
>
> Next get value of all numeric charachters to the next Alpha (hopefully 
> this
> includes - /#)
>
> Now sort first by whats left of the leading part numbers
> then by the value of the extracted numerical section
> and lastly by the trailing alpha if present
>
> here is the query where i tried your earlier suggestion
>
> SELECT T_PartNumbers.PartNumber
> FROM T_PartNumbers
> ORDER BY Left([PartNumbers],7), Val(Mid([PartNumbers],8));
>
> I have been trying to add a sort value field but ive only covered a few
> hundred records of a few thousand.
>
> Thanks for any help
> Barry
>
> "Duane Hookom" wrote:
>
>> I looks like your values have a fixed length to the left of the numbers. 
>> If
>> this is true, you can use the following in the Sorting and Grouping 
>> dialog
>> expression:
>>
>> =Left([PartNumbers],7)
>> =Val(Mid([PartNumbers],8))
>>
>> -- 
>> Duane Hookom
>> Microsoft Access MVP
>>
>>
>> "Barry A&P" wrote:
>>
>> > I am looking for some help sorting a text field (PartNumbers) 
>> > alphabetically
>> > but correctly depending on the value of the ending few charachters if 
>> > theyre
>> > numbers..
>> >
>> > Here is some sample data
>> >
>> > an960pd10
>> > an960pd300
>> > an960pd6
>> >
>> > i would like it sorted like this
>> > an960pd6
>> > an960pd10
>> > an960pd300
>> >
>> > any ideas?
>> > Thanks
>> > Barry 

0
Duane
3/6/2010 4:50:05 AM
Duane
I was looking at my data and i am beginning to fear the sort is too complex, 
or atleast too many variables to look at.. 

The field structure im afraid is correct.  as they are manufacturers part 
numbers and i am trying to sort the nuts bolts and screws by physical size 
instead of alphabetically by partnumber.

I would like to refine my question to these..
if my PartNumber ends with a single Alphabetical charachter how can i get 
that value and move it to another field..
so i would remove the "R" from ms234-20R
but would not touch ms35266SS because there are 2 trailing ahpha charachters


Then in the next step how could i move all of the trailing Numerical 
charachters to a new field?
so i could then remove the "20" from ms234-20
or 2445 from M83248-1-A2445

I think if i could somehow create a sub that could count the number of 
charachters in the part number then use if right(partnumber,1)  = alpha and 
right(partnumber,2,1) <> alpha
then new field = right(partnumber,1)

and do the same for numbers but keep all the numbers till i get <> Numberchar

but not sure how to test if its a number or a letter or if thats even 
possible if its all in a text field..

Thanks
Barry

"Duane Hookom" wrote:

> Please provide the "sort" expressions from multiple actual part numbers.
> If I were you, I would immediately change the structure so a field stores a 
> single value/item.
> 
> 
> -- 
> Duane Hookom
> MS Access MVP
> 
> 
> "Barry A&P" <BarryAP@discussions.microsoft.com> wrote in message 
> news:DC1729BB-D3E8-4504-B344-07989B244352@microsoft.com...
> > Duane
> >
> > Thanks for looking at my post..  Im sorry but i gave a pretty poor example
> > of my sample data as they are not fixed length PartNumbers and i forgot 
> > some
> > have a trailing alpha.
> >
> > i tried out your suggestion in a query and although my results (because of
> > the non fixed length) are a little goofy you have me heading in the right
> > direction.
> >
> > Unless maybe my data is too complex and needs a little VBA code??
> >
> > here is what a hardware p/n consists of
> > an960pd416L
> > an960 is the style
> > pd is the material
> > 416 is the size
> > L is a revision attribute
> >
> > Here is another sample
> > an960-416
> > an960 is the style
> > - is added in because material is not specified
> > 416 is the size
> > and there is no revision
> >
> > additional samples
> > ms28778-1-034A
> > ms28778-1-066A
> > ms28778-1-4
> >
> > so my sort needs to (starting from the end of the partNumber) first split
> > off a trailing alpha charachter (if Present)
> >
> > Next get value of all numeric charachters to the next Alpha (hopefully 
> > this
> > includes - /#)
> >
> > Now sort first by whats left of the leading part numbers
> > then by the value of the extracted numerical section
> > and lastly by the trailing alpha if present
> >
> > here is the query where i tried your earlier suggestion
> >
> > SELECT T_PartNumbers.PartNumber
> > FROM T_PartNumbers
> > ORDER BY Left([PartNumbers],7), Val(Mid([PartNumbers],8));
> >
> > I have been trying to add a sort value field but ive only covered a few
> > hundred records of a few thousand.
> >
> > Thanks for any help
> > Barry
> >
> > "Duane Hookom" wrote:
> >
> >> I looks like your values have a fixed length to the left of the numbers. 
> >> If
> >> this is true, you can use the following in the Sorting and Grouping 
> >> dialog
> >> expression:
> >>
> >> =Left([PartNumbers],7)
> >> =Val(Mid([PartNumbers],8))
> >>
> >> -- 
> >> Duane Hookom
> >> Microsoft Access MVP
> >>
> >>
> >> "Barry A&P" wrote:
> >>
> >> > I am looking for some help sorting a text field (PartNumbers) 
> >> > alphabetically
> >> > but correctly depending on the value of the ending few charachters if 
> >> > theyre
> >> > numbers..
> >> >
> >> > Here is some sample data
> >> >
> >> > an960pd10
> >> > an960pd300
> >> > an960pd6
> >> >
> >> > i would like it sorted like this
> >> > an960pd6
> >> > an960pd10
> >> > an960pd300
> >> >
> >> > any ideas?
> >> > Thanks
> >> > Barry 
> 
0
Utf
3/8/2010 5:16:01 PM
Again "Please provide the "sort" expressions from multiple actual part numbers"
How about giving us at least 10 stored values and how they should be sorted?

-- 
Duane Hookom
Microsoft Access MVP


"Barry A&P" wrote:

> Duane
> I was looking at my data and i am beginning to fear the sort is too complex, 
> or atleast too many variables to look at.. 
> 
> The field structure im afraid is correct.  as they are manufacturers part 
> numbers and i am trying to sort the nuts bolts and screws by physical size 
> instead of alphabetically by partnumber.
> 
> I would like to refine my question to these..
> if my PartNumber ends with a single Alphabetical charachter how can i get 
> that value and move it to another field..
> so i would remove the "R" from ms234-20R
> but would not touch ms35266SS because there are 2 trailing ahpha charachters
> 
> 
> Then in the next step how could i move all of the trailing Numerical 
> charachters to a new field?
> so i could then remove the "20" from ms234-20
> or 2445 from M83248-1-A2445
> 
> I think if i could somehow create a sub that could count the number of 
> charachters in the part number then use if right(partnumber,1)  = alpha and 
> right(partnumber,2,1) <> alpha
> then new field = right(partnumber,1)
> 
> and do the same for numbers but keep all the numbers till i get <> Numberchar
> 
> but not sure how to test if its a number or a letter or if thats even 
> possible if its all in a text field..
> 
> Thanks
> Barry
> 
> "Duane Hookom" wrote:
> 
> > Please provide the "sort" expressions from multiple actual part numbers.
> > If I were you, I would immediately change the structure so a field stores a 
> > single value/item.
> > 
> > 
> > -- 
> > Duane Hookom
> > MS Access MVP
> > 
> > 
> > "Barry A&P" <BarryAP@discussions.microsoft.com> wrote in message 
> > news:DC1729BB-D3E8-4504-B344-07989B244352@microsoft.com...
> > > Duane
> > >
> > > Thanks for looking at my post..  Im sorry but i gave a pretty poor example
> > > of my sample data as they are not fixed length PartNumbers and i forgot 
> > > some
> > > have a trailing alpha.
> > >
> > > i tried out your suggestion in a query and although my results (because of
> > > the non fixed length) are a little goofy you have me heading in the right
> > > direction.
> > >
> > > Unless maybe my data is too complex and needs a little VBA code??
> > >
> > > here is what a hardware p/n consists of
> > > an960pd416L
> > > an960 is the style
> > > pd is the material
> > > 416 is the size
> > > L is a revision attribute
> > >
> > > Here is another sample
> > > an960-416
> > > an960 is the style
> > > - is added in because material is not specified
> > > 416 is the size
> > > and there is no revision
> > >
> > > additional samples
> > > ms28778-1-034A
> > > ms28778-1-066A
> > > ms28778-1-4
> > >
> > > so my sort needs to (starting from the end of the partNumber) first split
> > > off a trailing alpha charachter (if Present)
> > >
> > > Next get value of all numeric charachters to the next Alpha (hopefully 
> > > this
> > > includes - /#)
> > >
> > > Now sort first by whats left of the leading part numbers
> > > then by the value of the extracted numerical section
> > > and lastly by the trailing alpha if present
> > >
> > > here is the query where i tried your earlier suggestion
> > >
> > > SELECT T_PartNumbers.PartNumber
> > > FROM T_PartNumbers
> > > ORDER BY Left([PartNumbers],7), Val(Mid([PartNumbers],8));
> > >
> > > I have been trying to add a sort value field but ive only covered a few
> > > hundred records of a few thousand.
> > >
> > > Thanks for any help
> > > Barry
> > >
> > > "Duane Hookom" wrote:
> > >
> > >> I looks like your values have a fixed length to the left of the numbers. 
> > >> If
> > >> this is true, you can use the following in the Sorting and Grouping 
> > >> dialog
> > >> expression:
> > >>
> > >> =Left([PartNumbers],7)
> > >> =Val(Mid([PartNumbers],8))
> > >>
> > >> -- 
> > >> Duane Hookom
> > >> Microsoft Access MVP
> > >>
> > >>
> > >> "Barry A&P" wrote:
> > >>
> > >> > I am looking for some help sorting a text field (PartNumbers) 
> > >> > alphabetically
> > >> > but correctly depending on the value of the ending few charachters if 
> > >> > theyre
> > >> > numbers..
> > >> >
> > >> > Here is some sample data
> > >> >
> > >> > an960pd10
> > >> > an960pd300
> > >> > an960pd6
> > >> >
> > >> > i would like it sorted like this
> > >> > an960pd6
> > >> > an960pd10
> > >> > an960pd300
> > >> >
> > >> > any ideas?
> > >> > Thanks
> > >> > Barry 
> > 
0
Utf
3/9/2010 3:21:01 PM
Duane
i do not have a "sort" expressions other than ORDER BY 
T_PartNumbers.PartNumber;
here is more sample data sorted alphabetically..

AN5-12A
AN5-13A
AN525-10R8
AN526-832R6
AN5-35A
AN5-6A
AN960-6
AN960-616
AN960-616L
AN960-6L
AN960-716
AN960-716L
AN960-8
AN960-816
AN960-816L
AN960-8L
AN960-916
AN960-916L
CD-10
CD-12
CD-16
CD-3
CD-4
CD-6
CD-8
CR2249-4-01
CR2249-4-10
CR2249-4-5
CR2249-5-5
CR2249-6-3

here is the same data hand sorted..

AN5-6A
AN5-12A
AN5-13A
AN5-35A
AN525-10R8
AN526-832R6
AN960-6
AN960-6L
AN960-8
AN960-8L
AN960-616
AN960-616L
AN960-716
AN960-716L
AN960-816
AN960-816L
AN960-916
AN960-916L
CD-3
CD-4
CD-6
CD-8
CD-10
CD-12
CD-16
CR2249-4-01
CR2249-4-5
CR2249-4-10
CR2249-5-5
CR2249-6-3

I have been playing with VBA to do this.. and i can handle a few if 
statements to see if there is a trailing [a-z]   but what i cant figure out 
how to do is some kind of loop that will get the numerical section until it 
gets to the next [!0-9]
I subsituted the Partnumber value that would m
for example if right(PartNumber,1) like [0-9] then  'partnumber = CR2249-4-01
"start code to get digits until it hits the - so results would be strA = 
CR2249-4-    and  strB =  01

elseif mid(PartNumber,len(PartNumber)-1,1) like [0-9] 'for Partnumber = 
an960pd416L
"start code to get digits until it hits the "d" so results would be StrA = 
an960pd   and   StrB = 416

else
strA = partnumber

I feel im so lost on this i am just making stupid comments now.
Hope you can make something of this. 
Or shoot me down and put me out of this misery. if im nuts

Thanks
Barry

"Duane Hookom" wrote:



> Again "Please provide the "sort" expressions from multiple actual part numbers"
> How about giving us at least 10 stored values and how they should be sorted?
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "Barry A&P" wrote:
> 
> > Duane
> > I was looking at my data and i am beginning to fear the sort is too complex, 
> > or atleast too many variables to look at.. 
> > 
> > The field structure im afraid is correct.  as they are manufacturers part 
> > numbers and i am trying to sort the nuts bolts and screws by physical size 
> > instead of alphabetically by partnumber.
> > 
> > I would like to refine my question to these..
> > if my PartNumber ends with a single Alphabetical charachter how can i get 
> > that value and move it to another field..
> > so i would remove the "R" from ms234-20R
> > but would not touch ms35266SS because there are 2 trailing ahpha charachters
> > 
> > 
> > Then in the next step how could i move all of the trailing Numerical 
> > charachters to a new field?
> > so i could then remove the "20" from ms234-20
> > or 2445 from M83248-1-A2445
> > 
> > I think if i could somehow create a sub that could count the number of 
> > charachters in the part number then use if right(partnumber,1)  = alpha and 
> > right(partnumber,2,1) <> alpha
> > then new field = right(partnumber,1)
> > 
> > and do the same for numbers but keep all the numbers till i get <> Numberchar
> > 
> > but not sure how to test if its a number or a letter or if thats even 
> > possible if its all in a text field..
> > 
> > Thanks
> > Barry
> > 
> > "Duane Hookom" wrote:
> > 
> > > Please provide the "sort" expressions from multiple actual part numbers.
> > > If I were you, I would immediately change the structure so a field stores a 
> > > single value/item.
> > > 
> > > 
> > > -- 
> > > Duane Hookom
> > > MS Access MVP
> > > 
> > > 
> > > "Barry A&P" <BarryAP@discussions.microsoft.com> wrote in message 
> > > news:DC1729BB-D3E8-4504-B344-07989B244352@microsoft.com...
> > > > Duane
> > > >
> > > > Thanks for looking at my post..  Im sorry but i gave a pretty poor example
> > > > of my sample data as they are not fixed length PartNumbers and i forgot 
> > > > some
> > > > have a trailing alpha.
> > > >
> > > > i tried out your suggestion in a query and although my results (because of
> > > > the non fixed length) are a little goofy you have me heading in the right
> > > > direction.
> > > >
> > > > Unless maybe my data is too complex and needs a little VBA code??
> > > >
> > > > here is what a hardware p/n consists of
> > > > an960pd416L
> > > > an960 is the style
> > > > pd is the material
> > > > 416 is the size
> > > > L is a revision attribute
> > > >
> > > > Here is another sample
> > > > an960-416
> > > > an960 is the style
> > > > - is added in because material is not specified
> > > > 416 is the size
> > > > and there is no revision
> > > >
> > > > additional samples
> > > > ms28778-1-034A
> > > > ms28778-1-066A
> > > > ms28778-1-4
> > > >
> > > > so my sort needs to (starting from the end of the partNumber) first split
> > > > off a trailing alpha charachter (if Present)
> > > >
> > > > Next get value of all numeric charachters to the next Alpha (hopefully 
> > > > this
> > > > includes - /#)
> > > >
> > > > Now sort first by whats left of the leading part numbers
> > > > then by the value of the extracted numerical section
> > > > and lastly by the trailing alpha if present
> > > >
> > > > here is the query where i tried your earlier suggestion
> > > >
> > > > SELECT T_PartNumbers.PartNumber
> > > > FROM T_PartNumbers
> > > > ORDER BY Left([PartNumbers],7), Val(Mid([PartNumbers],8));
> > > >
> > > > I have been trying to add a sort value field but ive only covered a few
> > > > hundred records of a few thousand.
> > > >
> > > > Thanks for any help
> > > > Barry
> > > >
> > > > "Duane Hookom" wrote:
> > > >
> > > >> I looks like your values have a fixed length to the left of the numbers. 
> > > >> If
> > > >> this is true, you can use the following in the Sorting and Grouping 
> > > >> dialog
> > > >> expression:
> > > >>
> > > >> =Left([PartNumbers],7)
> > > >> =Val(Mid([PartNumbers],8))
> > > >>
> > > >> -- 
> > > >> Duane Hookom
> > > >> Microsoft Access MVP
> > > >>
> > > >>
> > > >> "Barry A&P" wrote:
> > > >>
> > > >> > I am looking for some help sorting a text field (PartNumbers) 
> > > >> > alphabetically
> > > >> > but correctly depending on the value of the ending few charachters if 
> > > >> > theyre
> > > >> > numbers..
> > > >> >
> > > >> > Here is some sample data
> > > >> >
> > > >> > an960pd10
> > > >> > an960pd300
> > > >> > an960pd6
> > > >> >
> > > >> > i would like it sorted like this
> > > >> > an960pd6
> > > >> > an960pd10
> > > >> > an960pd300
> > > >> >
> > > >> > any ideas?
> > > >> > Thanks
> > > >> > Barry 
> > > 
0
Utf
3/10/2010 6:35:01 PM
You can try the following VBA code and see if it works to give you a sort 
string you can use.  It seems to work in my limited testing.

Public Function fStringNumberSort(strIn) As Variant
Dim strReturn As String
Dim i As Long
Dim strNumbers As String
'Set the number of zeros to be used for the sort string
Const csZeroString As String = "00000000"

    If Len(Trim(strIn & vbNullString)) = 0 Then
       'return null or spaces or zero length string
       fStringNumberSort = strIn

    ElseIf strIn Like "*[0-9]*" = False Then
       'No numbers so we are done
       fStringNumberSort = strIn

    Else  'Handle cases where there is one of more number characters
       For i = 1 To Len(strIn)
          If IsNumeric(Mid(strIn, i, 1)) = True Then
             strNumbers = strNumbers & Mid(strIn, i, 1)

          Else
             'Add the number string
             If Len(strNumbers) > 0 Then
                strReturn = strReturn & Format(strNumbers, csZeroString)
                strNumbers = vbNullString
             End If

             'Add the non-number characters
             strReturn = strReturn & Mid(strIn, i, 1)

          End If
       Next i

       If Len(strNumbers) > 0 Then
          strReturn = strReturn & Format(strNumbers, csZeroString)
          strNumbers = vbNullString
       End If

       fStringNumberSort = strReturn
    End If
End Function



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Barry A&P wrote:
> Duane
> i do not have a "sort" expressions other than ORDER BY 
> T_PartNumbers.PartNumber;
> here is more sample data sorted alphabetically..
> 
> AN5-12A
> AN5-13A
> AN525-10R8
> AN526-832R6
> AN5-35A
> AN5-6A
> AN960-6
> AN960-616
> AN960-616L
> AN960-6L
> AN960-716
> AN960-716L
> AN960-8
> AN960-816
> AN960-816L
> AN960-8L
> AN960-916
> AN960-916L
> CD-10
> CD-12
> CD-16
> CD-3
> CD-4
> CD-6
> CD-8
> CR2249-4-01
> CR2249-4-10
> CR2249-4-5
> CR2249-5-5
> CR2249-6-3
> 
> here is the same data hand sorted..
> 
> AN5-6A
> AN5-12A
> AN5-13A
> AN5-35A
> AN525-10R8
> AN526-832R6
> AN960-6
> AN960-6L
> AN960-8
> AN960-8L
> AN960-616
> AN960-616L
> AN960-716
> AN960-716L
> AN960-816
> AN960-816L
> AN960-916
> AN960-916L
> CD-3
> CD-4
> CD-6
> CD-8
> CD-10
> CD-12
> CD-16
> CR2249-4-01
> CR2249-4-5
> CR2249-4-10
> CR2249-5-5
> CR2249-6-3
> 
> I have been playing with VBA to do this.. and i can handle a few if 
> statements to see if there is a trailing [a-z]   but what i cant figure out 
> how to do is some kind of loop that will get the numerical section until it 
> gets to the next [!0-9]
> I subsituted the Partnumber value that would m
> for example if right(PartNumber,1) like [0-9] then  'partnumber = CR2249-4-01
> "start code to get digits until it hits the - so results would be strA = 
> CR2249-4-    and  strB =  01
> 
> elseif mid(PartNumber,len(PartNumber)-1,1) like [0-9] 'for Partnumber = 
> an960pd416L
> "start code to get digits until it hits the "d" so results would be StrA = 
> an960pd   and   StrB = 416
> 
> else
> strA = partnumber
> 
> I feel im so lost on this i am just making stupid comments now.
> Hope you can make something of this. 
> Or shoot me down and put me out of this misery. if im nuts
> 
> Thanks
> Barry
> 
> "Duane Hookom" wrote:
> 
> 
> 
>> Again "Please provide the "sort" expressions from multiple actual part numbers"
>> How about giving us at least 10 stored values and how they should be sorted?
>>
>> -- 
>> Duane Hookom
>> Microsoft Access MVP
>>
>>
>> "Barry A&P" wrote:
>>
>>> Duane
>>> I was looking at my data and i am beginning to fear the sort is too complex, 
>>> or atleast too many variables to look at.. 
>>>
>>> The field structure im afraid is correct.  as they are manufacturers part 
>>> numbers and i am trying to sort the nuts bolts and screws by physical size 
>>> instead of alphabetically by partnumber.
>>>
>>> I would like to refine my question to these..
>>> if my PartNumber ends with a single Alphabetical charachter how can i get 
>>> that value and move it to another field..
>>> so i would remove the "R" from ms234-20R
>>> but would not touch ms35266SS because there are 2 trailing ahpha charachters
>>>
>>>
>>> Then in the next step how could i move all of the trailing Numerical 
>>> charachters to a new field?
>>> so i could then remove the "20" from ms234-20
>>> or 2445 from M83248-1-A2445
>>>
>>> I think if i could somehow create a sub that could count the number of 
>>> charachters in the part number then use if right(partnumber,1)  = alpha and 
>>> right(partnumber,2,1) <> alpha
>>> then new field = right(partnumber,1)
>>>
>>> and do the same for numbers but keep all the numbers till i get <> Numberchar
>>>
>>> but not sure how to test if its a number or a letter or if thats even 
>>> possible if its all in a text field..
>>>
>>> Thanks
>>> Barry
>>>
>>> "Duane Hookom" wrote:
>>>
>>>> Please provide the "sort" expressions from multiple actual part numbers.
>>>> If I were you, I would immediately change the structure so a field stores a 
>>>> single value/item.
>>>>
>>>>
>>>> -- 
>>>> Duane Hookom
>>>> MS Access MVP
>>>>
>>>>
>>>> "Barry A&P" <BarryAP@discussions.microsoft.com> wrote in message 
>>>> news:DC1729BB-D3E8-4504-B344-07989B244352@microsoft.com...
>>>>> Duane
>>>>>
>>>>> Thanks for looking at my post..  Im sorry but i gave a pretty poor example
>>>>> of my sample data as they are not fixed length PartNumbers and i forgot 
>>>>> some
>>>>> have a trailing alpha.
>>>>>
>>>>> i tried out your suggestion in a query and although my results (because of
>>>>> the non fixed length) are a little goofy you have me heading in the right
>>>>> direction.
>>>>>
>>>>> Unless maybe my data is too complex and needs a little VBA code??
>>>>>
>>>>> here is what a hardware p/n consists of
>>>>> an960pd416L
>>>>> an960 is the style
>>>>> pd is the material
>>>>> 416 is the size
>>>>> L is a revision attribute
>>>>>
>>>>> Here is another sample
>>>>> an960-416
>>>>> an960 is the style
>>>>> - is added in because material is not specified
>>>>> 416 is the size
>>>>> and there is no revision
>>>>>
>>>>> additional samples
>>>>> ms28778-1-034A
>>>>> ms28778-1-066A
>>>>> ms28778-1-4
>>>>>
>>>>> so my sort needs to (starting from the end of the partNumber) first split
>>>>> off a trailing alpha charachter (if Present)
>>>>>
>>>>> Next get value of all numeric charachters to the next Alpha (hopefully 
>>>>> this
>>>>> includes - /#)
>>>>>
>>>>> Now sort first by whats left of the leading part numbers
>>>>> then by the value of the extracted numerical section
>>>>> and lastly by the trailing alpha if present
>>>>>
>>>>> here is the query where i tried your earlier suggestion
>>>>>
>>>>> SELECT T_PartNumbers.PartNumber
>>>>> FROM T_PartNumbers
>>>>> ORDER BY Left([PartNumbers],7), Val(Mid([PartNumbers],8));
>>>>>
>>>>> I have been trying to add a sort value field but ive only covered a few
>>>>> hundred records of a few thousand.
>>>>>
>>>>> Thanks for any help
>>>>> Barry
>>>>>
>>>>> "Duane Hookom" wrote:
>>>>>
>>>>>> I looks like your values have a fixed length to the left of the numbers. 
>>>>>> If
>>>>>> this is true, you can use the following in the Sorting and Grouping 
>>>>>> dialog
>>>>>> expression:
>>>>>>
>>>>>> =Left([PartNumbers],7)
>>>>>> =Val(Mid([PartNumbers],8))
>>>>>>
>>>>>> -- 
>>>>>> Duane Hookom
>>>>>> Microsoft Access MVP
>>>>>>
>>>>>>
>>>>>> "Barry A&P" wrote:
>>>>>>
>>>>>>> I am looking for some help sorting a text field (PartNumbers) 
>>>>>>> alphabetically
>>>>>>> but correctly depending on the value of the ending few charachters if 
>>>>>>> theyre
>>>>>>> numbers..
>>>>>>>
>>>>>>> Here is some sample data
>>>>>>>
>>>>>>> an960pd10
>>>>>>> an960pd300
>>>>>>> an960pd6
>>>>>>>
>>>>>>> i would like it sorted like this
>>>>>>> an960pd6
>>>>>>> an960pd10
>>>>>>> an960pd300
>>>>>>>
>>>>>>> any ideas?
>>>>>>> Thanks
>>>>>>> Barry 
0
John
3/11/2010 2:14:31 PM
john 
Absolutely amazing it works great. and handles more data than i expected. I 
was sure i would still have to work with the data after it was sorted..

SELECT T_PartNumbers.PartNumber, fStringNumberSort([partNumber]) AS SortValue
FROM T_PartNumbers
ORDER BY fStringNumberSort([partNumber]);

I do have a few oddballs that i can deffinately live with.. the data that 
begins with alphabetical charachters is perfect but the numbers that start 
with nomerical charachters arent quite as expected. if there is an easy fix 
it would be even better...

here is a big chunk of sample data
notice how it revisits the first set of numbers would it be possible to 
negate the first set numerical string if the code comes up with more than one 
numerical string in its results??

PartNumber	Sort Value
1-300-686-03	00000001-00000300-00000686-00000003
1-1282-65	                00000001-00001282-00000065
1-225663-5	00000001-00225663-00000005
35C4908	                00000035C00004908
35C4909	                00000035C00004909
570-074-315-3	00000570-00000074-00000315-00000003
570-076-002-1	00000570-00000076-00000002-00000001
574-074-272-1	00000574-00000074-00000272-00000001
696-41960	                00000696-00041960
839-00154	                00000839-00000154
950-315	                00000950-00000315
1001-4000-01	00001001-00004000-00000001
1086	                00001086
1151	                00001151
1190K37	                00001190K00000037
1204K12	                00001204K00000012
1220-2410-2	00001220-00002410-00000002
1242T431	                00001242T00000431
1266T2	                00001266T00000002
1268T3	                00001268T00000003
1275K33	                00001275K00000033
1277K24	                00001277K00000024
1277K34	                00001277K00000034

Thank you very much for your suggestion
i can deffinately get where i am going with this..

Barry




"John Spencer" wrote:

> You can try the following VBA code and see if it works to give you a sort 
> string you can use.  It seems to work in my limited testing.
> 
> Public Function fStringNumberSort(strIn) As Variant
> Dim strReturn As String
> Dim i As Long
> Dim strNumbers As String
> 'Set the number of zeros to be used for the sort string
> Const csZeroString As String = "00000000"
> 
>     If Len(Trim(strIn & vbNullString)) = 0 Then
>        'return null or spaces or zero length string
>        fStringNumberSort = strIn
> 
>     ElseIf strIn Like "*[0-9]*" = False Then
>        'No numbers so we are done
>        fStringNumberSort = strIn
> 
>     Else  'Handle cases where there is one of more number characters
>        For i = 1 To Len(strIn)
>           If IsNumeric(Mid(strIn, i, 1)) = True Then
>              strNumbers = strNumbers & Mid(strIn, i, 1)
> 
>           Else
>              'Add the number string
>              If Len(strNumbers) > 0 Then
>                 strReturn = strReturn & Format(strNumbers, csZeroString)
>                 strNumbers = vbNullString
>              End If
> 
>              'Add the non-number characters
>              strReturn = strReturn & Mid(strIn, i, 1)
> 
>           End If
>        Next i
> 
>        If Len(strNumbers) > 0 Then
>           strReturn = strReturn & Format(strNumbers, csZeroString)
>           strNumbers = vbNullString
>        End If
> 
>        fStringNumberSort = strReturn
>     End If
> End Function
> 
> 
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> Barry A&P wrote:
> > Duane
> > i do not have a "sort" expressions other than ORDER BY 
> > T_PartNumbers.PartNumber;
> > here is more sample data sorted alphabetically..
> > 
> > AN5-12A
> > AN5-13A
> > AN525-10R8
> > AN526-832R6
> > AN5-35A
> > AN5-6A
> > AN960-6
> > AN960-616
> > AN960-616L
> > AN960-6L
> > AN960-716
> > AN960-716L
> > AN960-8
> > AN960-816
> > AN960-816L
> > AN960-8L
> > AN960-916
> > AN960-916L
> > CD-10
> > CD-12
> > CD-16
> > CD-3
> > CD-4
> > CD-6
> > CD-8
> > CR2249-4-01
> > CR2249-4-10
> > CR2249-4-5
> > CR2249-5-5
> > CR2249-6-3
> > 
> > here is the same data hand sorted..
> > 
> > AN5-6A
> > AN5-12A
> > AN5-13A
> > AN5-35A
> > AN525-10R8
> > AN526-832R6
> > AN960-6
> > AN960-6L
> > AN960-8
> > AN960-8L
> > AN960-616
> > AN960-616L
> > AN960-716
> > AN960-716L
> > AN960-816
> > AN960-816L
> > AN960-916
> > AN960-916L
> > CD-3
> > CD-4
> > CD-6
> > CD-8
> > CD-10
> > CD-12
> > CD-16
> > CR2249-4-01
> > CR2249-4-5
> > CR2249-4-10
> > CR2249-5-5
> > CR2249-6-3
> > 
> > I have been playing with VBA to do this.. and i can handle a few if 
> > statements to see if there is a trailing [a-z]   but what i cant figure out 
> > how to do is some kind of loop that will get the numerical section until it 
> > gets to the next [!0-9]
> > I subsituted the Partnumber value that would m
> > for example if right(PartNumber,1) like [0-9] then  'partnumber = CR2249-4-01
> > "start code to get digits until it hits the - so results would be strA = 
> > CR2249-4-    and  strB =  01
> > 
> > elseif mid(PartNumber,len(PartNumber)-1,1) like [0-9] 'for Partnumber = 
> > an960pd416L
> > "start code to get digits until it hits the "d" so results would be StrA = 
> > an960pd   and   StrB = 416
> > 
> > else
> > strA = partnumber
> > 
> > I feel im so lost on this i am just making stupid comments now.
> > Hope you can make something of this. 
> > Or shoot me down and put me out of this misery. if im nuts
> > 
> > Thanks
> > Barry
> > 
> > "Duane Hookom" wrote:
> > 
> > 
> > 
> >> Again "Please provide the "sort" expressions from multiple actual part numbers"
> >> How about giving us at least 10 stored values and how they should be sorted?
> >>
> >> -- 
> >> Duane Hookom
> >> Microsoft Access MVP
> >>
> >>
> >> "Barry A&P" wrote:
> >>
> >>> Duane
> >>> I was looking at my data and i am beginning to fear the sort is too complex, 
> >>> or atleast too many variables to look at.. 
> >>>
> >>> The field structure im afraid is correct.  as they are manufacturers part 
> >>> numbers and i am trying to sort the nuts bolts and screws by physical size 
> >>> instead of alphabetically by partnumber.
> >>>
> >>> I would like to refine my question to these..
> >>> if my PartNumber ends with a single Alphabetical charachter how can i get 
> >>> that value and move it to another field..
> >>> so i would remove the "R" from ms234-20R
> >>> but would not touch ms35266SS because there are 2 trailing ahpha charachters
> >>>
> >>>
> >>> Then in the next step how could i move all of the trailing Numerical 
> >>> charachters to a new field?
> >>> so i could then remove the "20" from ms234-20
> >>> or 2445 from M83248-1-A2445
> >>>
> >>> I think if i could somehow create a sub that could count the number of 
> >>> charachters in the part number then use if right(partnumber,1)  = alpha and 
> >>> right(partnumber,2,1) <> alpha
> >>> then new field = right(partnumber,1)
> >>>
> >>> and do the same for numbers but keep all the numbers till i get <> Numberchar
> >>>
> >>> but not sure how to test if its a number or a letter or if thats even 
> >>> possible if its all in a text field..
> >>>
> >>> Thanks
> >>> Barry
> >>>
> >>> "Duane Hookom" wrote:
> >>>
> >>>> Please provide the "sort" expressions from multiple actual part numbers.
> >>>> If I were you, I would immediately change the structure so a field stores a 
> >>>> single value/item.
> >>>>
> >>>>
> >>>> -- 
> >>>> Duane Hookom
> >>>> MS Access MVP
> >>>>
> >>>>
> >>>> "Barry A&P" <BarryAP@discussions.microsoft.com> wrote in message 
> >>>> news:DC1729BB-D3E8-4504-B344-07989B244352@microsoft.com...
> >>>>> Duane
> >>>>>
> >>>>> Thanks for looking at my post..  Im sorry but i gave a pretty poor example
> >>>>> of my sample data as they are not fixed length PartNumbers and i forgot 
> >>>>> some
> >>>>> have a trailing alpha.
> >>>>>
> >>>>> i tried out your suggestion in a query and although my results (because of
> >>>>> the non fixed length) are a little goofy you have me heading in the right
> >>>>> direction.
> >>>>>
> >>>>> Unless maybe my data is too complex and needs a little VBA code??
> >>>>>
> >>>>> here is what a hardware p/n consists of
> >>>>> an960pd416L
> >>>>> an960 is the style
> >>>>> pd is the material
> >>>>> 416 is the size
> >>>>> L is a revision attribute
> >>>>>
> >>>>> Here is another sample
> >>>>> an960-416
> >>>>> an960 is the style
> >>>>> - is added in because material is not specified
> >>>>> 416 is the size
> >>>>> and there is no revision
> >>>>>
> >>>>> additional samples
> >>>>> ms28778-1-034A
> >>>>> ms28778-1-066A
> >>>>> ms28778-1-4
> >>>>>
> >>>>> so my sort needs to (starting from the end of the partNumber) first split
> >>>>> off a trailing alpha charachter (if Present)
> >>>>>
> >>>>> Next get value of all numeric charachters to the next Alpha (hopefully 
> >>>>> this
> >>>>> includes - /#)
> >>>>>
> >>>>> Now sort first by whats left of the leading part numbers
> >>>>> then by the value of the extracted numerical section
> >>>>> and lastly by the trailing alpha if present
> >>>>>
> >>>>> here is the query where i tried your earlier suggestion
> >>>>>
> >>>>> SELECT T_PartNumbers.PartNumber
> >>>>> FROM T_PartNumbers
> >>>>> ORDER BY Left([PartNumbers],7), Val(Mid([PartNumbers],8));
> >>>>>
> >>>>> I have been trying to add a sort value field but ive only covered a few
> >>>>> hundred records of a few thousand.
> >>>>>
> >>>>> Thanks for any help
> >>>>> Barry
> >>>>>
> >>>>> "Duane Hookom" wrote:
> >>>>>
> >>>>>> I looks like your values have a fixed length to the left of the numbers. 
> >>>>>> If
> >>>>>> this is true, you can use the following in the Sorting and Grouping 
> >>>>>> dialog
> >>>>>> expression:
> >>>>>>
> >>>>>> =Left([PartNumbers],7)
> >>>>>> =Val(Mid([PartNumbers],8))
> >>>>>>
> >>>>>> -- 
> >>>>>> Duane Hookom
> >>>>>> Microsoft Access MVP
> >>>>>>
> >>>>>>
> >>>>>> "Barry A&P" wrote:
> >>>>>>
> >>>>>>> I am looking for some help sorting a text field (PartNumbers) 
> >>>>>>> alphabetically
> >>>>>>> but correctly depending on the value of the ending few charachters if 
> >>>>>>> theyre
> >>>>>>> numbers..
> >>>>>>>
> >>>>>>> Here is some sample data
> >>>>>>>
> >>>>>>> an960pd10
> >>>>>>> an960pd300
> >>>>>>> an960pd6
> >>>>>>>
> >>>>>>> i would like it sorted like this
> >>>>>>> an960pd6
> >>>>>>> an960pd10
> >>>>>>> an960pd300
> >>>>>>>
> >>>>>>> any ideas?
> >>>>>>> Thanks
> >>>>>>> Barry 
0
Utf
3/11/2010 11:25:01 PM
This may not be what you want, but here is an attempt at what I understand you 
want.  If you want the first number string to not be formatted no matter what 
then you will need to add a variable to track if the number string is the 
first one.  And increment the variable every time you add a number string to 
the return string.

Public Function fStringNumberSort(strIn) As Variant
Dim strReturn As String
Dim i As Long
Dim strNumbers As String
'Set the number of zeros to be used for the sort string
Const csZeroString As String = "00000000"

    If Len(Trim(strIn & vbNullString)) = 0 Then
       'return null or spaces or zero length string
       fStringNumberSort = strIn

    ElseIf strIn Like "*[0-9]*" = False Then
       'No numbers so we are done
       fStringNumberSort = strIn

    Else  'Handle cases where there is one of more number characters
       For i = 1 To Len(strIn)
          If IsNumeric(Mid(strIn, i, 1)) = True Then
             strNumbers = strNumbers & Mid(strIn, i, 1)

          Else
             'Add the number string
             If Len(strNumbers) > 0 Then
'================ Modification to Not format number if it is the first
'thing to be added to the string
'=====================================================================
		If Len(strReturn) > 0 then
                	   strReturn = strReturn & Format(strNumbers, csZeroString)
                 Else
                    strReturn = strReturn & strNumbers
                 End IF
                strNumbers = vbNullString
             End If

             'Add the non-number characters
             strReturn = strReturn & Mid(strIn, i, 1)

          End If
       Next i

       If Len(strNumbers) > 0 Then
          strReturn = strReturn & Format(strNumbers, csZeroString)
          strNumbers = vbNullString
       End If

       fStringNumberSort = strReturn
    End If
End Function

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
0
John
3/11/2010 11:50:27 PM
Reply:

Similar Artilces:

Graph
Hi, Here is the problem - There is a graph I have to create, based on the following information: I have projects names, and each project has the following data: 1. Estimated cost. 2. Actual cost. 3.Status, which can be one of 3: a. over with b. in progress c. per demand I need to put the data on a 3D column graph, in a way that each project will have 2 columns that compare estimated and actual cost, but the problem is I also need to put some kind of an attribute on every project which reflects its status. How do I do that? Thanks in advance, Chen. Chen - I'd suggest a 2D chart type in...

how do i add my phone number to outlook address book ?
how do i add my phone number to outlook address book ? You can't. Why would you? The Outlook Address Book is only a list of electronic addresses to which you can address messages. Why would you need a phone number there? Phone numbers, like all Contact data, are stored in your Contacts Folder. -- Russ Valentine [MVP-Outlook] "Ingaga" <Ingaga @discussions.microsoft.com> wrote in message news:2C2224F0-0E8B-4E20-A2AC-200B044053FD@microsoft.com... > how do i add my phone number to outlook address book ? What in the world is the purpse of the Outlook address book whe...

Not sure if Excel can do what i need
Hello all, I am obviously a new user to excel. I have a psychological assessmen that needs to be computerised for ease of use in obtaining results. Here is what i would like to happen. I have 28 questions with fiv Likert scale options(0-4) for answers. Each question addresses 1 of different personality traits. So questions 1, 6, 8, 22 look at anger. Where as questions 2, 9, 28 look at happiness. What i would like is fo excel to group the answers and give me the total number for each trait. Then based on that number, give out a prewritten (which i would provide result of the assessment. ...

Text Values #2
I'm tryng to build a spreadsheet to track employee vacations. How do I sum 8 hours if a cell has a "V" entered and 4 hours if a cell has an "X" entered? Assuming you're using column A, and each row is either the number of hours worked, or a V or a X, then use a helper column, B, and insert this in B1 and copy down.......change the A1's to A2's if you have a header in Row 1, and put this in B2. =IF(A1="V",8,IF(A1="X",4,A1)) Vaya con Dios, Chuck, CABGx3 "Brando" <carpenterchsd@earthlink.net> wrote in message news:1113...

Match value in one column and return value in same row in next col
Hi, I have a a set of values in A1 through A100. I need to look up each value and find a match in another set of values located in C1 through C200. If a match is found then I need the formula located in column B to return the value in the same row but the next column over (D). Example: A B C D 359 375 10012 456 125 10031 125 10031 952 10052 854 444 10017 The formula located in B3 should find that the value of 125 (located in A3) matches the value in C2 and returns 10031 which is the v...

Need Min Value from Row and Heading Value that Corresponds to it.
I have a table that looks somewhat like this w/o the *'d headings: h1 h2 h3 h4 h5 h6 h7 *Min Price *Heading $5.00 $6.00 $3.00 $4.00 $9.00 $1.00 $9.00 $1.00 h6 $3.00 $6.00 $8.00 $65.00 $9.00 $2.00 $4.00 $2.00 h6 $4.00 $9.00 $5.00 $7.00 $1.00 $2.00 $8.00 $1.00 h5 $9.00 $7.00 $6.00 $3.00 $9.00 $8.00 $2.00 $2.00 h7 $7.00 $8.00 $9.00 $7.00 $9.00 $8.00 $8.00 $7.00 h1 I have several SKU's with various price contract/programs. I want to find the best price in one cell and the heading that would correspond to that cell in another. I was trying to do this with an expression ...

Excel need help
I have 2 columns a and b . a contains payment b contains balance. I have the entire col b with the formula. my ? is when the balance appearsin b it is in all the cells in that column.I only want it in the used ones, not the unused ones:( example $270.00 16,730.00 $270.00 16,460.00 $100.00 16,360.00 $300.00 16,060.00 $270.00 15,790.00 15,790.00 15,790.00 15,790.00 15,790.00 15,790.00 this 15790.00 is in col b...not a...I want no bal in unused cells -- fiftieslady ------------------------------------------------------------------------ fiftieslady's Profile: http://www.excelforum.co...

HELP.....Setting up Outlook express again
I've just installed everything on my computer again and im having trouble with outlook express. its asking for my incoming and outgoing server names and i have no idea what they are or where i get them, so i can access or set my account up again. if anyone knows can they please help me thanks!!!! you can reach me at : bl182ink@hotmail.com Amanda Arbuckle what type of account? who is the email provider? have you checked their website for assistance? -- "Amanda" <bl182ink@hotmail.com> wrote in message news:A37A86BC-5E22-4B1F-B6E3-9459C70971C1@microsoft.com... > I...

Need Help Linking Sheets
How can I link 2 pages without using the actual sheet as a reference? In other words, can I have two sheets linked by Identifying the same exact names one on each sheet? ...

Excel newbie needs help from math wiz (poker related)
I want to create a Excel workbook that will help in specific poker situations. Short version: I want to be able to calculate how often my opponent will need to fold for an all-in raise to show a positive expectation. Explanation: In poker you can win a hand by having the best hand at the end OR getting your opponent to fold. A common situation comes up where you have a "drawing hand" and face a bet by your opponent. In this situation you know you are currently behind in the hand, but have a chance to win if you catch some of your "outs" (cards that will give you th...

Macro help needed........
How do I sort a particular column in ascending order and change the pag setup to landscape and margins to 0.25 using a macro code ? does anyone have a macro code for this ????? -- Message posted from http://www.ExcelForum.com Hi you have already received some answers in your previous threads please don't multipost! -- Regards Frank Kabel Frankfurt, Germany > How do I sort a particular column in ascending order and change the > page setup to landscape and margins to 0.25 using a macro code ? > > does anyone have a macro code for this ?????? > > > --- > Me...

Need to test a mobo
I had a power surge about 3 weeks ago - lost a drive, now making ticking noises. I had another two drive that seem to work sporadically now. So, I thought it might have been my power supply. Bought a tester and found out that all seems well. Next, I bought a new hard drive and found that it was also acting up - making noises like whirring up and down (like the other drives I thought were dead). So I am starting to wonder if it is my mobo - but how can I test this out? I am using a dual boot - Win XP and Win 7 - both drives work and don't work. Up and down. Right now my Win7 ...

text boxes filled with color
When I fill any text bos with color while creating anm e-mail publication, the text boxes are converted into images instead of text, thus making my file size bigger. How to change this? Lavanya <Lavanya@discussions.microsoft.com> was very recently heard to utter: > When I fill any text bos with color while creating anm e-mail > publication, the text boxes are converted into images instead of > text, thus making my file size bigger. How to change this? There is no way to change it. This is the way Publisher works. Publisher is not designed for outputting small HTML code. --...

A full page of email text shrinks to one inch or less w/ new batte
I was answering emails and everyhting was fine. The batteries in my wireless mouse died. I replaced them and went back to answering emails. When I clicked the "forward" or "reply" button , a full page text in an email shrank to about one inch. Changing the font size did not correct the problem. HELP it's your zoom setting. Hold ctrl and roll the mouse wheel to increase. Depending on the mouse, you could have accidently pressed a mouse button and changed it when you changed the batteries. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.o...

Help needed
is there anyway you can see what links to other workbooks are associated with the workbook you have open. The problem i am having is when i open the workbook in question a message appears and states "The workbook you opened contains automatic links to information in another workbook. do you want to update this workbook with changes made to the other workbook" Yes....... No. if i click No the workbook opens, however if i click Yes the workbook just hangs and i have to go into task manager to shut down the system. I can only assume that there is a problem with one of the links....

Combo Box Text Alignment
Is it possible to tweek the text alignment in a combo box other then the standard "Left-Right-Center" preset options ? Thanks - George What specifically are you looking for? Assuming you use a query as the RowSource, you've always got the option of concatenating spaces in front (or after) a field. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "George" <George@discussions.microsoft.com> wrote in message news:E1366215-05A0-45BB-A27B-272A47458FC8@microsoft.com... > Is it possible to tweek the text alignment in a ...

Help with formula 12-17-09
Let's say I have in cell A1 a combo box with options: 1. Expense 2. Refund 3. Tax In cell B1 I will type a number In cell C3 I want a formula like this: If in cell A1 I have selected 1. Expense than calculate B1*25 If in cell A1 I have selected 2. Refund than calculate B1*50 If in cell A1 I have selected 3. Tax than calculate B1*150 How can I apply the combo box to entire column, like if I go to row 2 to find in B1 the combo box from A1 and in C2 the formula from C1 Thanks for the help In c1: =IF(A1="Expense",B1*25,IF(A1="Refund",B1*50,B1*150)) Micky ...

Need help on this....thanks
I have an Excel worksheet which stored a survey outcome of over 1200 members. There are over 30 questions in the survey and the first column captured the member ID. The problem is 3 of the survey questions that allow multiple answers, instead the answer for those three questions are captured in seperate column, the answers are stored in a single column and it makes it very difficult to analysis those result. As a result I created addittional columns depending on the total number of the answers available for that question and assign the result manually to that column. It is very time consuming ...

Help with Error
I got this error recently, and just want to make sure I'm reading it right. Logon Failure on database "First Storage Group\Mailbox Store (EXCHANGE)" - Windows 2000 account PARADIGM\user1; mailbox /o=Paradigm/ou=First Administrative Group/cn=Recipients/cn=user2. Error: -2147221231 Does this mean the user1 tried to access the user2 mailbox and failed? Thanks in advance hello albert, well, this looks familiar to: http://support.microsoft.com/default.aspx?scid=kb;en-us;260782 and http://support.microsoft.com/default.aspx?scid=kb;en-us;309113 regards, Alex "AlbertP&quo...

Ranking
I previously revived a formula (Thanks Max) to Sort and Rank my Stores by the Strongest Store to the Lowest Store. It did exactly what it was supposed to do with my sample layout I made with just Four stores and scores. But when I added all 14 stores to the list (each store as a rank like 2.45 or 3.56 or 11.75) it will not sort them properly. I am using =IF(ISNA(RANK(Q7,Q7:Q20,1)),"",INDEX(O7:O20,RANK(Q7,Q7:Q20,1))) for the ranking system. It works when the stores Ranks are SINGLE digit numbers, but when they go into the tenths or hundredths the formula can't ...

I need Help
I have a random popup that i cant stop,when it does come up it seems to know what i was looking at,like when i was looking for registry cleaner it popped up and showed me one,any help would be great. On Jan 22, 6:57=A0am, Larry <La...@discussions.microsoft.com> wrote: > I have a random popup that i cant stop,when it does come up it seems to k= now > what i was looking at,like when i was looking for registry cleaner it pop= ped > up and showed me one,any help would be great. To eliminate questions and guessing, please provide additional information about your sys...

need help badly
How do you print ole objects.. Thanks lachanda -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200710/1 On Wed, 03 Oct 2007 18:13:40 GMT, "misschanda via AccessMonster.com" <u36612@uwe> wrote: >How do you print ole objects.. >Thanks >lachanda What *kind* of OLE objects? Many of them are binary blobs (such as programs) which are inherently not printable. More details please! John W. Vinson [MVP] ...

Stuck in review and need to be in design.
I am using Visio 2007. I need to complete the diagram that I have been working on and I hit the review key by mistake. How do I get back to the design screen? On Wed, 4 Nov 2009 16:14:01 -0800, cameron <cameron@discussions.microsoft.com> wrote: >I am using Visio 2007. I need to complete the diagram that I have been >working on and I hit the review key by mistake. How do I get back to the >design screen? menu Tools -> Track Markup and then close the Review window. -- Regards, Paul Herber, Sandrila Ltd. DFD/SSADM for Visio http://www.visio-dfd.sa...

Sort range changes during sort
After highlighting the columns and rows I want sorted, I select data and sort. At this point the sort range changes to include the entire row from A to IV of each row I selected. I've searched the options screen and help menu, but don't know why this is happening. Thanks in Advance. This is a complete guess--I couldn't duplicate what happened to you in xl2003. Do you have merged cells in that range? If yes, maybe that's the problem. coffedrinker2003 wrote: > > After highlighting the columns and rows I want sorted, I select data and > sort. At this point the so...

Missing Text #5
Hello, I have a problem with Outlook XP (2002) that I can't find a solution for. When this particular user receives a reply to an email she sent, the frist couple of lines of the reply text will appear in the auto preview (the preview in the message listing in the inbox) but when she opens the message itself by double clicking it, that reply text is missing. I've updated Outlook to SP3 and even tried reinstalling it once. Does anyone know of a fix for this? Thanks, Rod ...