How to use "between" function in this code?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Hi

Assuming that [Org Code] is an integer:

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

Cheers.

BW
 
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.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

BeWyched said:
Hi

Assuming that [Org Code] is an integer:

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

Cheers.

BW

notDave said:
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
Click to expand...
Click to expand...
 
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,
 
Oh man... couldn't see the forest on account of the trees. >= and <=, as
compared to Between. Thank you very much.

~notDave

BeWyched said:
Hi

Assuming that [Org Code] is an integer:

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

Cheers.

BW

notDave said:
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
Click to expand...
Click to expand...
 
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

John Spencer said:
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.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

BeWyched said:
Hi

Assuming that [Org Code] is an integer:

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

Cheers.

BW

notDave said:
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
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the suggestion... works great.

~notDave

Paolo said:
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


notDave said:
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
Click to expand...
Click to expand...
 
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


George Nicholson said:
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,


notDave said:
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
Click to expand...
Click to expand...
 
Back
Top