VBA function to return letters of alphabet

[SOS]

Hi All,

I'd be grateful for some help please. I'm looking for a VBA functio
that will return each letter of the alphabet in turn.

I have a query that brings me back all the people in my list whos
surname begins with the letter "A". I know I could run this query 2
times (to return surnames for each letter) but I'd like to be able t
run it with the result of the function passed as a paramater so that
could end up with a table of all my names. The reason I have to spli
it up 26 times is that the query is so big that it falls over if I as
for all names at once.

Hope that makes sense

TIA

Seamu

 

[Frank Kabel]

Hi
have a look at the Chr function in the VBA helpt. Combine this with a
For next loop

 

[Bob Phillips]

Seamus,

Here is a simple function

Dim iLetter As Long

Function NextLetter() As String
If Val(iLetter) = 0 Then
iLetter = 65
Else
iLetter = iLetter + 1
End If
NextLetter = Chr(iLetter)
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[SOS]

Thanks to both Bob and Frank - I will work away with this informatio
and hope that I can incorporate the variable into my SQL statement.

Thanks again

Seamu

 

[Chip Pearson]

Seamus,

Try a loop structure like the following:

Dim FirstLetter As String
Dim Ndx As Integer
For Ndx = 65 To 90 ' upper case letters
FirstLetter = Chr(Ndx)
' do you query using FirstLetter
Next Ndx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[SOS]

Thanks for the help. Admittedly the work I'm doing is in Access but th
VBA rules apply. I have now got my userform working having adopted th
loop and have my queries running using SQL. I feel quite proud o
myself (IMHO)(LOL)

Thanks again - you guys always come through

Seamu