Using the alphabet

[Paul Wagstaff]

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

[Allen Browne]

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" <pa**********@blueyonder.co.uk> wrote in message
news:0B******************@fe1.news.blueyonder.co.u k...

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

[Paul Wagstaff]

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" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...

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" <pa**********@blueyonder.co.uk> wrote in message
news:0B******************@fe1.news.blueyonder.co.u k...

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

[Bob Quintal]

"Paul Wagstaff" <pa**********@blueyonder.co.uk> wrote in
news:Vd*******************@fe1.news.blueyonder.co. uk:

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.

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.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:447861a7$0$3626$5a62ac22@per-qv1-newsreader- 01.iinet.net.a u...

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" <pa**********@blueyonder.co.uk> wrote in
message
news:0B******************@fe1.news.blueyonder.co.u k...

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

--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

Bob Quintal <rq******@sympatico.ca> wrote in
news:Xn**********************@207.35.177.135:

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.

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.

[Paul Wagstaff]

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" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...

Bob Quintal <rq******@sympatico.ca> wrote in
news:Xn**********************@207.35.177.135:

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.

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.

[Bob Quintal]

"Paul Wagstaff" <pa**********@blueyonder.co.uk> wrote in
news:fn******************@fe1.news.blueyonder.co.u k:

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
Just testing upper bound and sorting the list alphabetically
will ensure that there are no gaps.
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...

Bob Quintal <rq******@sympatico.ca> wrote in
news:Xn**********************@207.35.177.135:

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.

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.

--
Bob Quintal

PA is y I've altered my email address.