How to Look for a Range of Last Names in a Recordset 1

[Ziriya]

Hi, I haven't programmed in a LONG time and am having trouble figuring out how to code the comparison criteria for looking for a range of last names in a Recordset. This is what I want to do:

While Not EOF
If Name Field Begins with A thru M Then
Do Action
Move to Next Record
End While

 

[PaulBricker]

You could look at something like this.

Dim strVal
While Not EOF
strVal = Asc(Left(rst!NameField,1))
If strVal >=65 and strVal<=77 Then '65 = A and 77 = M
Do Action
Move to Next Record
Else
Move to Next Record
End If
End While

Paul

 

[wvdba]

would it be faster if he just did this?
if left(rst!NameField1, 1) >= lcase("A") and
left(rst!NameField1, 1) <= lcase("M") then
found = "yes"
end if

 

[Ziriya]

I was able to get Paul's suggestion to work, but had some trouble with WvDba's.

Thanks for your help guys!

 

[wvdba]

there's no problem with the code i posted. the two parts of the "and" must be on the same line, or continued on the next line with a "_". the posting box, just didn't allow that, i suppose. could be that i pressed an extra "enter".
cheers.

 

[Ziriya]

I did have the if...then statement on one line, but that doesn't mean I didn't do something else dumb. I didn't say your's wouldn't work, just that I wasn't able to get it to before I got the other solution to work. I do appreciate the help from both of you!

 

[Spork52]

Why don't you retrieve the records you want instead of filtering them later? If you are using T-SQL then BETWEEN should work:

SELECT * FROM tablename WHERE LEFT(fieldname, 1) BETWEEN 'A' AND 'M'

 

[Ziriya]

Spork, I had thought of that already, but I was not given any control over the data pulled from the database in the Recordset. That was handled by someone else.