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

 

[Pat Hartman]

Between is NOT limited to dates. But it is limited to SQL. It is not a VBA command.

 

[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.