Using "Between" in VBA?

[connexion]

Hi there,
this is just a quickie i'm sure.

I have used "Between" to indicate if a value is between two other values in queries for ages, and i know that i can use it with "WHERE" in an SQL string, BUT

When i just want to use it in an "If" statement Access doesn't want to play?

If *** BETWEEN **** then

OR
If *** BETWEEN **** = true then

etc...etc...etc..

Anyone know the secret?

Vince

 

[ColinEssex]

Use the < or > instead.

If Age >16 and Age <32 Then
Do Something

Else

Don't

End If

Col

 

[connexion]

Thanks so far, but...

Hi Colin,
Thanks for that but i'm already doing that and just wanted to trim the code down by a few lines. It seems strange that you can't use "BETWEEN" in VBA to find out if a value is between two others?

Here's what i'm using so far...

Set rstSageData = CurrentDb.OpenRecordset("SELECT * FROM tblSageData ORDER BY AccountRef ;")

If rstSageData.RecordCount > 0 Then
With rstSageData
.MoveFirst
Do While Not .EOF
.Edit

If Val(!DaysOverdue) >= "0" Then
If Val(!DaysOverdue) < "10" Then
!ChaseLetter = "1"
End If
End If

etc...etc...etc...
finishing up with updating the table...

.Update
.MoveNext
Loop
End With
Else
End If

 

[Kodo]

why not peform that check in the where clause (criteria) of the query..

 

[dcx693]

why not peform that check in the where clause (criteria) of the query..

In fact, why not just use an update query? But then again, I'd ask why you're storing a calculated number in your table.

 

[Kodo]

In fact, why not just use an update query? But then again, I'd ask why you're storing a calculated number in your table.

.

 

[connexion]

So is "BETWEEN" possible then?

The table is being created specifically to pull together data from a whole load of places, including other Access tables, Sage Line 50 tables etc.
The table is created specifically to deal with aged debt and is renewed each time it is required.

Certain values are calculated and stored in the table so that as the table is built the data remains static, to be viewed and commited to print in word.

...so is "BETWEEN" a No-No then?

Vince

 

[Kodo]

basically what we're saying is to do this

UPDATE table set FIELDNAME=VALUE where DaysOverDue >=0 <10

between is only valid in SQL for dates.

 

[dcx693]

basically what we're saying is to do this

UPDATE table set FIELDNAME=VALUE where DaysOverDue >=0 <10

between is only valid in SQL for dates.

Actually, you can use Between for non-dates. Use a syntax like this:

UPDATE tblSageData SET ChaseLetter = 1 WHERE (([DaysOverdue] Between 0 And 10));

 

[Kodo]

learn something new every day...

 

[dcx693]

learn something new every day...

Happens to me everyday!

 

[JasonTFleishman]

I've attempted to use > x and < y in a select statement and it isn't working? Suggestions? The code follows:

Select Case IsNumeric(CCValue)
Case Is = True
Select Case CCValue
Case Is = 7, 8, 9, 10, 11, 24
cc = cc & ":00 am"
Case Is >= 1, 2, 3, 4, 5, 6
cc = cc & ":00 pm"
Case Is >= 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23
cc = (cc - 12) & ":00 pm"

 

[Minty]

Wow, you've resurrected an 18-year-old thread." Impressive!

Case Statements are a slightly special case... Try the following syntax

Case 7 to 11, 24

Case 1 to 6

Case 13 to 23

 

[Jon]

Does this work....?

Select Case IsNumeric(CCValue)
  Case Is = True
    Select Case CCValue
      Case 7, 8, 9, 10, 11, 24
        cc = cc & ":00 am"
      Case 1 To 6
        cc = cc & ":00 pm"
      Case 13 To 23
        cc = (cc - 12) & ":00 pm"
    End Select
End Select

 

[ebs17]

? Eval("27 Between 10 AND 40")
-1

Between would be usable in VBA, but surely that's pretty bad style.

 

[The_Doc_Man]

Since I had a copy of the MS VBA Language Specification (v20140424, release date 30 Apr 2014), I did a simple search. At no time anywhere in that document is the word "BETWEEN" used in the context of a formal operator. All uses of "BETWEEN" (74 of them) are in discussions of the semantics of other syntax constructs. Therefore, I confirm Pat's statement. BETWEEN is not a VBA operator unless it was added in a more recent release, and I have no evidence of such a change.

 

[JasonTFleishman]

Does this work....?

Select Case IsNumeric(CCValue)
  Case Is = True
    Select Case CCValue
      Case 7, 8, 9, 10, 11, 24
        cc = cc & ":00 am"
      Case 1 To 6
        cc = cc & ":00 pm"
      Case 13 To 23
        cc = (cc - 12) & ":00 pm"
    End Select
End Select

Yes, the post of, Minty, AWF VIP, worked well: "Case 7 to 11, 24". It's an honor to have your guidance. I have the ambition of being like all of you.

 

[JasonTFleishman]

Since I had a copy of the MS VBA Language Specification (v20140424, release date 30 Apr 2014), I did a simple search. At no time anywhere in that document is the word "BETWEEN" used in the context of a formal operator. All uses of "BETWEEN" (74 of them) are in discussions of the semantics of other syntax constructs. Therefore, I confirm Pat's statement. BETWEEN is not a VBA operator unless it was added in a more recent release, and I have no evidence of such a change.

Thanks for the research. Forcing the use of 'Between' in the VBA context, when it is meant to dwell only in Query-SQL-land, is a bad idea.

 

[JasonTFleishman]

? Eval("27 Between 10 AND 40")
-1

Between would be usable in VBA, but surely that's pretty bad style.

Creative. Square pegs do fit in round holes after all.

 

[plog]

1. Use the right tool for the job. There is no need to use a Case Statement with a Boolean test:

Select Case IsNumeric(CCValue)

Use an IF there, not a Select Case

2. Use the right tool for the job #2. If each Case of the Select is going to be a test itself, then Select probably isn't the right tool to begin with. Break out of the Select and just make each Case test an If test:

if A>B Then...
if A=C Then...

3. Speciulation and an assumption---Are all those numerical tests going to work on CCValue? I'm not certain. That first Select uses IsNumeric(CCValue) which makes me think CCValue is a string. Then later on everything is a numerical test against CCValue. And numbers and strings don't compare the same when you use > and <:

7 < 10 but '7' > '10'

4. Incomplete logic. Looks like you are doing military time evaluations. normally there's a 0 hour - I don't see a 0 accounted for in all your logic. Perhaps there can't be a 0 hour. Even so, you should have a default case--even if that default is to throw an error. You need to write code that accounts for all possibilities even ones that "shouldn't" be there. Between is fine to use, but make sure there are no unaccounted for end points beyond the Between statements.