Using the alphabet

Hi all...

I'm passing the value of txtSurname to a function, which returns the correct
'Team' to txtTeam depending on the 'alphabetic' value of txtSurname e.g.


All surname names between: A* - BAR* = Team 1
BAS* - COL* = Team 2
COM* - Z* = Team 3


Can someone drum up a quick example of the best way to do this?

Thanks,
Paul

re: Using the alphabet

Create a column with 2 fields:
TeamID Number primary key
NameStart Text

Enter the records:
1 A
2 Bas
3 Com
and so on.

Download the ELookup() function from:
http://allenbrowne.com/ser-42.html

You can now get the team number from this expression:
=ELookup("TeamID", "tblTeam", """" & [Surname] & """ >= [NameStart]",
"NameStart DESC")

If you want something more sophistocated, see Tom Ellison's article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul Wagstaff" <paulwagstaff@blueyonder.co.uk> wrote in message
news:0BYdg.11717$8W1.7222@fe1.news.blueyonder.co.u k...[color=blue]
>
> I'm passing the value of txtSurname to a function, which returns the
> correct 'Team' to txtTeam depending on the 'alphabetic' value of
> txtSurname e.g.
>
>
> All surname names between: A* - BAR* = Team 1
> BAS* - COL* = Team 2
> COM* - Z* = Team 3
>
>
> Can someone drum up a quick example of the best way to do this?
>
> Thanks,
> Paul[/color]

re: Using the alphabet

Thanks Allen. I may well come back to this at some point. For now, i'll
continue with what i have which is...

If strIn < "BAS" Then 'A -BAR
AssignTo = "Group 1"
ElseIf strIn > "BAR*" And strIn < "BROX" Then 'BAS -BROW
AssignTo = "Group 2"
........etc all the way to Z*

Else
AssignTo = "Error"
End If

The alphabet variables aren't likely to change, so I think this is will
suffice. Unless I'm making some serious error with this method?

Paul.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:447861a7$0$3626$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> Create a column with 2 fields:
> TeamID Number primary key
> NameStart Text
>
> Enter the records:
> 1 A
> 2 Bas
> 3 Com
> and so on.
>
> Download the ELookup() function from:
> http://allenbrowne.com/ser-42.html
>
> You can now get the team number from this expression:
> =ELookup("TeamID", "tblTeam", """" & [Surname] & """ >= [NameStart]",
> "NameStart DESC")
>
> If you want something more sophistocated, see Tom Ellison's article:
> Lookup in a range of values in a query
> at:
> http://allenbrowne.com/ser-58.html
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Paul Wagstaff" <paulwagstaff@blueyonder.co.uk> wrote in message
> news:0BYdg.11717$8W1.7222@fe1.news.blueyonder.co.u k...[color=green]
>>
>> I'm passing the value of txtSurname to a function, which returns the
>> correct 'Team' to txtTeam depending on the 'alphabetic' value of
>> txtSurname e.g.
>>
>>
>> All surname names between: A* - BAR* = Team 1
>> BAS* - COL* = Team 2
>> COM* - Z* = Team 3
>>
>>
>> Can someone drum up a quick example of the best way to do this?
>>
>> Thanks,
>> Paul[/color]
>
>[/color]

re: Using the alphabet

"Paul Wagstaff" <paulwagstaff@blueyonder.co.uk> wrote in
news:Vd0eg.12345$8W1.10224@fe1.news.blueyonder.co. uk:
[color=blue]
> Thanks Allen. I may well come back to this at some point. For
> now, i'll continue with what i have which is...
>
> If strIn < "BAS" Then 'A -BAR
> AssignTo = "Group 1"
> ElseIf strIn > "BAR*" And strIn < "BROX" Then 'BAS -BROW
> AssignTo = "Group 2"
> .......etc all the way to Z*
>
> Else
> AssignTo = "Error"
> End If
>
> The alphabet variables aren't likely to change, so I think
> this is will suffice. Unless I'm making some serious error
> with this method?
>
> Paul.[/color]


Firstly, the asterisk is wrong. (> "BAR*") It only works as a
wildcard with the LIKE keyword, not with >. "BAR A" is >
"bar"

Secondly, you don't even need to test again for the > condition
The elseif construction has already determined that your string
is less that BAS. just test for the upper limit, until the last
breakpoint, then let a simple ELSE handle those.

Thirdly, you shouldn't store "Group 2"
all you need is the 2, stored as a number.
If you try to sort on the "Group x" Column, you will get
confused because the order will be
GROUP 1
GROUP 10
GROUP 12
GROUP 2
GROUP 3

Add the word GROUP as a label in the form or report.


[color=blue]
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:447861a7$0$3626$5a62ac22@per-qv1-newsreader-[/color]
01.iinet.net.a[color=blue]
> u...[color=green]
>> Create a column with 2 fields:
>> TeamID Number primary key
>> NameStart Text
>>
>> Enter the records:
>> 1 A
>> 2 Bas
>> 3 Com
>> and so on.
>>
>> Download the ELookup() function from:
>> http://allenbrowne.com/ser-42.html
>>
>> You can now get the team number from this expression:
>> =ELookup("TeamID", "tblTeam", """" & [Surname] & """ >=
>> [NameStart]",
>> "NameStart DESC")
>>
>> If you want something more sophistocated, see Tom Ellison's
>> article:
>> Lookup in a range of values in a query
>> at:
>> http://allenbrowne.com/ser-58.html
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Paul Wagstaff" <paulwagstaff@blueyonder.co.uk> wrote in
>> message
>> news:0BYdg.11717$8W1.7222@fe1.news.blueyonder.co.u k...[color=darkred]
>>>
>>> I'm passing the value of txtSurname to a function, which
>>> returns the correct 'Team' to txtTeam depending on the
>>> 'alphabetic' value of txtSurname e.g.
>>>
>>>
>>> All surname names between: A* - BAR* = Team 1
>>> BAS* - COL* =
>>> Team 2
>>> COM* - Z*
>>> = Team 3
>>>
>>>
>>> Can someone drum up a quick example of the best way to do
>>> this?
>>>
>>> Thanks,
>>> Paul[/color]
>>
>>[/color]
>
>
>[/color]



--
Bob Quintal

PA is y I've altered my email address.

re: Using the alphabet

Bob Quintal <rquintal@sympatico.ca> wrote in
news:Xns97D0A489CF8CCBQuintal@207.35.177.135:
[color=blue]
>
> Firstly, the asterisk is wrong. (> "BAR*") It only works as a
> wildcard with the LIKE keyword, not with >. "BAR A" is >
> "bar"
>
> Secondly, you don't even need to test again for the >[/color]
condition[color=blue]
> The elseif construction has already determined that your[/color]
string[color=blue]
> is less that BAS. just test for the upper limit, until the[/color]
last[color=blue]
> breakpoint, then let a simple ELSE handle those.
>
> Thirdly, you shouldn't store "Group 2"
> all you need is the 2, stored as a number.
> If you try to sort on the "Group x" Column, you will get
> confused because the order will be
> GROUP 1
> GROUP 10
> GROUP 12
> GROUP 2
> GROUP 3
>
> Add the word GROUP as a label in the form or report.
>
>[/color]
After reflection, I'd replace the IF/ELSEIF/ELSE construct with
SELECT CASE strIn
case < "BAS"
assignTo = 1
case < "BROX"
case else
end select

It's faster.
--
Bob Quintal

PA is y I've altered my email address.

re: Using the alphabet

Thanks Bob.

I started using a Select and not using wildcards, but I ran into some
problems if a surname was 'Bar' etc. As you suggest, just testing for the
upperbound value works fine. Many thanks, I suspected my method was flawed.

Point taken about sorting, but it's not a priority for now. The 'Assinged
To' teams aren't sequential anyway - some are just strings - 'Belfast' -
etc. I could (and may in future) create a 1-M table of these, including the
separator points (Bar; Group 1 etc) which will allow the users flexibility
to modify these. Then I'll need a way to ensure there no gaps (i.e. Bar,
Bat)

If I can just find an extra day somewhere between Tuesday and Wednesady,
I'll have time to implement this.

Paul


"Bob Quintal" <rquintal@sympatico.ca> wrote in message
news:Xns97D0A585B88C0BQuintal@207.35.177.135...[color=blue]
> Bob Quintal <rquintal@sympatico.ca> wrote in
> news:Xns97D0A489CF8CCBQuintal@207.35.177.135:
>[color=green]
>>
>> Firstly, the asterisk is wrong. (> "BAR*") It only works as a
>> wildcard with the LIKE keyword, not with >. "BAR A" is >
>> "bar"
>>
>> Secondly, you don't even need to test again for the >[/color]
> condition[color=green]
>> The elseif construction has already determined that your[/color]
> string[color=green]
>> is less that BAS. just test for the upper limit, until the[/color]
> last[color=green]
>> breakpoint, then let a simple ELSE handle those.
>>
>> Thirdly, you shouldn't store "Group 2"
>> all you need is the 2, stored as a number.
>> If you try to sort on the "Group x" Column, you will get
>> confused because the order will be
>> GROUP 1
>> GROUP 10
>> GROUP 12
>> GROUP 2
>> GROUP 3
>>
>> Add the word GROUP as a label in the form or report.
>>
>>[/color]
> After reflection, I'd replace the IF/ELSEIF/ELSE construct with
> SELECT CASE strIn
> case < "BAS"
> assignTo = 1
> case < "BROX"
> case else
> end select
>
> It's faster.
> --
> Bob Quintal
>
> PA is y I've altered my email address.[/color]

re: Using the alphabet

"Paul Wagstaff" <paulwagstaff@blueyonder.co.uk> wrote in
news:fnceg.16129$8W1.3920@fe1.news.blueyonder.co.u k:
[color=blue]
> Thanks Bob.
>
> I started using a Select and not using wildcards, but I ran
> into some problems if a surname was 'Bar' etc. As you suggest,
> just testing for the upperbound value works fine. Many thanks,
> I suspected my method was flawed.
>
> Point taken about sorting, but it's not a priority for now.
> The 'Assinged To' teams aren't sequential anyway - some are
> just strings - 'Belfast' - etc. I could (and may in future)
> create a 1-M table of these, including the separator points
> (Bar; Group 1 etc) which will allow the users flexibility to
> modify these. Then I'll need a way to ensure there no gaps
> (i.e. Bar, Bat)
>
> If I can just find an extra day somewhere between Tuesday and
> Wednesady, I'll have time to implement this.
>
> Paul[/color]

Just testing upper bound and sorting the list alphabetically
will ensure that there are no gaps.
[color=blue]
> "Bob Quintal" <rquintal@sympatico.ca> wrote in message
> news:Xns97D0A585B88C0BQuintal@207.35.177.135...[color=green]
>> Bob Quintal <rquintal@sympatico.ca> wrote in
>> news:Xns97D0A489CF8CCBQuintal@207.35.177.135:
>>[color=darkred]
>>>
>>> Firstly, the asterisk is wrong. (> "BAR*") It only works as
>>> a wildcard with the LIKE keyword, not with >. "BAR A" is
>>> > "bar"
>>>
>>> Secondly, you don't even need to test again for the >[/color]
>> condition[color=darkred]
>>> The elseif construction has already determined that your[/color]
>> string[color=darkred]
>>> is less that BAS. just test for the upper limit, until the[/color]
>> last[color=darkred]
>>> breakpoint, then let a simple ELSE handle those.
>>>
>>> Thirdly, you shouldn't store "Group 2"
>>> all you need is the 2, stored as a number.
>>> If you try to sort on the "Group x" Column, you will get
>>> confused because the order will be
>>> GROUP 1
>>> GROUP 10
>>> GROUP 12
>>> GROUP 2
>>> GROUP 3
>>>
>>> Add the word GROUP as a label in the form or report.
>>>
>>>[/color]
>> After reflection, I'd replace the IF/ELSEIF/ELSE construct
>> with SELECT CASE strIn
>> case < "BAS"
>> assignTo = 1
>> case < "BROX"
>> case else
>> end select
>>
>> It's faster.
>> --
>> Bob Quintal
>>
>> PA is y I've altered my email address.[/color]
>
>
>[/color]



--
Bob Quintal

PA is y I've altered my email address.