How to use "between" function in this code?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

notDave - 24 Jul 2007 15:14 GMT

The sample code at the very bottom works, but I have to list each Org Code
using "Or". Is there a way to change the first line of the If statement to
use "Between" or "In"? Such as:

If myRs![Org Code] Between 109011 and 109051 Then...
or
If myRs![Org Code] In ('109011','109021','109031','109041','109051') Then...

---------

sql = "SELECT [Org Code] FROM [Organization]"

Set myDb = CurrentDb()
Set myRs = myDb.OpenRecordset(sql)

While Not myRs.EOF
If myRs![Org Code] = 109011 Or myRs![Org Code] = 109021 Then
Dothis...
EndIf
myRs.MoveNext
Wend

Any suggestions? Thank you!

~notDave

Reply to this Message

Paolo - 24 Jul 2007 16:18 GMT

You can use a select case.
select case myRs![Org Code]
case 109011 to 109051
do something
case ....
do something else
...
....
case else
the value of myRs![Org Code] doesn't interest me
end select

HTH Paolo

> The sample code at the very bottom works, but I have to list each Org Code
> using "Or". Is there a way to change the first line of the If statement to
[quoted text clipped - 21 lines]
>
> ~notDave

Reply to this Message

notDave - 24 Jul 2007 20:14 GMT

Thanks for the suggestion... works great.

~notDave

> You can use a select case.
> select case myRs![Org Code]
[quoted text clipped - 35 lines]
> >
> > ~notDave

Reply to this Message

BeWyched - 24 Jul 2007 16:18 GMT

Hi

Assuming that [Org Code] is an integer:

If myRs![Org Code] > 109010 and myRs![Org Code] < 109052 Then...

Cheers.

BW

> The sample code at the very bottom works, but I have to list each Org Code
> using "Or". Is there a way to change the first line of the If statement to
[quoted text clipped - 21 lines]
>
> ~notDave

Reply to this Message

John Spencer - 24 Jul 2007 16:58 GMT

You have some solutions, but my question is why don't you just limit the
records you return in the recordset.

sql = "SELECT [Org Code] FROM [Organization] WHERE [Org Code] Between 109011
and 109051"

Unless you have other branches in your VBA that processes other records
differently why not limit the recordset before you even start processing.

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Hi
>
[quoted text clipped - 34 lines]
>>
>> ~notDave

Reply to this Message

notDave - 24 Jul 2007 20:14 GMT

Thanks! Yes, I do want to perform these steps on the eintire recordset, I
just want to change the steps on just certain org codes. Both greater
than/less than, and teh Case suggestions would work.

Thank you for the reply though, and I think I may end up filtering that sql
select somewhere else...

~notDave

> You have some solutions, but my question is why don't you just limit the
> records you return in the recordset.
[quoted text clipped - 42 lines]
> >>
> >> ~notDave

Reply to this Message

notDave - 24 Jul 2007 20:08 GMT

Oh man... couldn't see the forest on account of the trees. >= and <=, as
compared to Between. Thank you very much.

~notDave

> Hi
>
[quoted text clipped - 31 lines]
> >
> > ~notDave

Reply to this Message

George Nicholson - 24 Jul 2007 18:12 GMT

Select Case has already been mentioned, but note that Select Case would
accomodate all 3 of the critreria you ask about (OR, LIKE and BETWEEN),
*and* handle them in a ladder-like structure (ELSE-IF) with a safety
net/error trap (ELSE):

Select Case OrgCode
Case 109011, 109021
' Like "OR"
'Do this
Case 109031,109041,109051
'Like "IN"
'Do this
Case 109011 to 109051
' Like "BETWEEN"
' Do this for any OrgCodes within specified range that haven't been
handled already (Like ELSE-IF)
Case Else
' Do this for any OrgCodes not already handled (Like ELSE)
End Select

HTH,

> The sample code at the very bottom works, but I have to list each Org Code
> using "Or". Is there a way to change the first line of the If statement
[quoted text clipped - 23 lines]
>
> ~notDave

Reply to this Message

notDave - 24 Jul 2007 20:24 GMT

Thanks for the info... and thanks for all the detail, exactly what I needed.
The "greater than/less than" suggested earlier simply slipped my mind, and it
certianly would work, but using the Select Case really cut down on the amt of
code. I also like that I can combine the types (In and Between) easier, such
as Case 109011, 109041, 115540 to 116050.

And THAT step got me thinking on something else, which eventually led to me
eliminating two full "pages" of code by creating a seperate function that is
called multiple times, depending on the Case. So three birds with one
stone... or at least one stone to the side of my head, which jarred something
loose. Either way... thanks.

~notDave

> Select Case has already been mentioned, but note that Select Case would
> accomodate all 3 of the critreria you ask about (OR, LIKE and BETWEEN),
[quoted text clipped - 45 lines]
> >
> > ~notDave

Reply to this Message