Using "Between" in VBA?

[JasonTFleishman]

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.

Excellent! The back story is ... I've decided to change my Datebook TimeFrom control to string format instead of a DateTime format so that partial time entries are accepted, and the computer deduces what was intended. The new control accepts the following partial time entries:
9
08
14
7:3
1122
7p
2230
17:29

and the computer rejects
2230a
73:22
as:00

The output will always be a sortable time format like '10:20 pm'.

I agree using Case statements where the answer is a boolean is a bad idea. I am trying to anticipate odd time entries that don't make sense, like '2230a', and so working through the scenarios, I'm discovering that in many instances, if statements were better. I've starting converting some of the code from Case to If. It's evolving and simplifying as I churn through the non-sensical possibilities of allowing someone to enter '13a', for example, which might be converted to 1:00 pm with a msgbox warning that the deduction may be wrong.

My solution is to iterate through every Case where the entry is Len() = 1 to Len() = 6 so the initial Case is necessary. An example of the tough code-churning for Len()=5 appears at the end.

This is my first big effort to re-write Access to make it appear to have fuzzy logic, more flexible and deductive. I appreciate the theme, 'Use the right tool for the job'.

Dim CC As Control
Dim CCName As String
Dim CCLength As Integer

Set CC = Screen.ActiveControl
CCName = CC.Name
CCLength = Len(CC)

' Partial time entries assume 7am is a cut off for am/pm assumptions, meaning if a partial entry is a '7',
' then the code deduces you mean 7 am, but if '6' then pm is deduced.
' ! ToolTip advising that that if entering a partial time of '7' or higher, it is assumed to be am, and lower is assumed pm.

Case Is = 5 ' For example, '1:30a' = 1:30 am, '12:30p' = 12:30 pm, or '2230p' = 10:30 pm, so the colon may be missing, or it can be in one of two positions
If IsNumeric(CC) Then ' If the entry is all numbers, deducing the time is difficult
GoTo EnterValidTime
End If
If InStr(CC, ":") = False Then ' For example, '2230p' = 10:30 pm
If Right(CC, 1) = "a" And Left(CC, 2) > 12 Then
MsgBox "You may have meant 'pm'?"
Exit Sub
End If
If Left(CC, 2) > 12 And Right(CC, 1) = "p" Then ' Military time and the usage of pm is incompatible and may lead to errors
MsgBox "Military time and the usage of pm is incompatible and may lead to errors."
Exit Sub
End If
CC = Left(CC, 2) & ":" & Mid(CC, 3, 2) & IIf(Right(CC, 1) = "a", " am", " pm")
Exit Sub
End If
Select Case InStr(CC, ":") ' For example, '1:30a' = 1:30 am, '12:30p' = 12:30 pm
Case Is = 2
CC = Left(CC, 1) & ":" & Mid(CC, 3, 2) & IIf(Right(CC, 1) = "a", " am", " pm")
Case Is = 3 ' For example, '11:30' = 11:30 am, '12:4a' = 12:40 am
Select Case Left(CC, 2)
Case Is > 11
CC = (Left(CC, 2) - 12) & ":" & Mid(CC, 4, 2) & " pm"
Exit Sub
Case Is = 12
CC = Left(CC, 2) & ":" & Mid(CC, 4, 2) & " pm"
Exit Sub
Case Else
CC = Left(CC, 2) & ":" & Mid(CC, 4, 2) & Switch(Right(CC, 1) = "a", " am", Right(CC, 1) = "p", " pm", 1 = 1, " am")
Exit Sub
End Select
Case Else
CC = Left(CC, 2) & ":" & Mid(CC, 3, 2) & " " & IIf(Right(CC, 1) = "a", " am", " pm")
Exit Sub
End Select
End Select

 

[The_Doc_Man]

I am not going to say that this suggestion will do everything for you, but the samples you have shown can easily be parsed according to simple grammar rules. Therefore, this offering MIGHT be helpful.

Text Parsing Support as Class Module

I've been working on something I used a lot at work to help me more easily decipher complex text inputs. For the Navy, I used this as a part of a program to analyze IIS logs, decode complex names, and I even once used a variant of this under OpenVMS to build a command-line calculator for the...

www.access-programmers.co.uk

If you use this, you just give it the whole string to be parsed and then trigger a parse operation. It may be overkill, but this would give you absolute knowledge within no more than four parsing steps to tell you EXACTLY what you had as an input string. As noted in the article, you have both the VBA class module and a Word document describing both setup and use.

 

[plog]

First, use CODE tags to display code in this form--makes it easier to read. Second, you spent a lot of time agreeing that Case wasn't the right tool, then you posted your code filled with them--so I don't know what to make of your code.

If you allow raw user input into a field, you are going to get things you have no idea what to do with--even after running it through your code and even after years of modifying that code to account for new ways people type stuff into your field. If you have expectations of what users can enter, make sure you restrict users to those constraints prior to accepting their data, not after.

Datebook TimeFrom control to string format instead of a DateTime format so that partial time entries are accepted

I would really be leery of changing to a string from a DateTime if that's what you ultimately need. I would leave the field in the table as the datatype needed but allow users to enter raw string data into a text field--but don't automatically save it--prior to saving you run a validation on that field and if you can make sense of it accept it, if not give the users a clear message saying why you can't accept their data and what you need that input to look like from them.

This is psuedo code (which means its just for illustrating my point and won't actually work):

Sub get_UserTime(in_String)

time_Input As DateTime
  ' will hold correct value input from user if what they submitted is parseable

bool_Valid = False
  ' variable to let code know a valid format for input was found

if (bool_Valid = False) AND (IsDate(in_String) Then
  ' in_String is a Date and is valid, will extract in_String and put into time_Input and sets bool_Valid so will stop looking
  time_Input = DateValue(in_String)
  bool_Valid=True
End If

if (bool_Valid = False) AND (TEST2) Then
  ' in_String didn't pass first test so tried to see if it is in another known format we can extract from
  time_Input= ' code to extract time input from in_STring since it matches this format
  bool_Valid = true
  End IF

... more if tests here

if bool_Valid Then
  ' found a format we could use will do whatever we do with good input

Else
  ' unable to extract user input will notify them unacceptable and have try again

End If

End Sub

In your code, just work through all the possible formats you know--extract the date/time if it matches one and if it doesn't keep testing to see if it matches a pattern you know how to extract from.

 

[JasonTFleishman]

Thank you. I'm grateful.

 

[MajP]

Can you incorporate a Time Picker to ensure they select a valid time? You can then validate on entry that the time is within actual work hours.

 

[MajP]

If you wanted a Between function in VBA you can roll your own. Not sure if it is worth the effort, but if you were doing a lot of comparisons.

Public Function IsBetween(Value As Variant, LowerLimit As Variant, UpperLimit As Variant, Optional IncludeBounds As Boolean = True) As Boolean
  If IncludeBounds Then
    IsBetween = (Value >= LowerLimit And Value <= UpperLimit)
  Else
     IsBetween = (Value > LowerLimit And Value < UpperLimit)
  End If
End Function

Public Sub Test()
  Dim x As Variant
  x = 5
  Debug.Print IsBetween(x, 4, 5)
  Debug.Print IsBetween(x, 4, 5, False)
  x = "B"
  Debug.Print IsBetween(x, "A", "C")
  Debug.Print IsBetween(x, "C", "D")
End Sub

 

[Mindflux]

Eval was already brought up. It also works for IN clauses.

?Eval("28 IN (15,999,28))"

Weird to use in VBA but handy in certain situations.

 

[JasonTFleishman]

If you wanted a Between function in VBA you can roll your own. Not sure if it is worth the effort, but if you were doing a lot of comparisons.

Public Function IsBetween(Value As Variant, LowerLimit As Variant, UpperLimit As Variant, Optional IncludeBounds As Boolean = True) As Boolean
  If IncludeBounds Then
    IsBetween = (Value >= LowerLimit And Value <= UpperLimit)
  Else
     IsBetween = (Value > LowerLimit And Value < UpperLimit)
  End If
End Function

Public Sub Test()
  Dim x As Variant
  x = 5
  Debug.Print IsBetween(x, 4, 5)
  Debug.Print IsBetween(x, 4, 5, False)
  x = "B"
  Debug.Print IsBetween(x, "A", "C")
  Debug.Print IsBetween(x, "C", "D")
End Sub

What a great homework assignment. I'm not great yet, but am studying privately. I should have known I could write my own. I've got a few little public functions that help me process information. Michael Wolfe wrote a function for cascading combo boxes which inspires me. Thanks.

 

[JasonTFleishman]

What a great homework assignment. I'm not great yet, but am studying privately. I should have known I could write my own. I've got a few little public functions that help me process information. Michael Wolfe wrote a function for cascading combo boxes which inspires me. Thanks.

how do I do that orange bar for pasting code?

 

[JasonTFleishman]

Eval was already brought up. It also works for IN clauses.

?Eval("28 IN (15,999,28))"

Weird to use in VBA but handy in certain situations.

Good suggestion. I'll add it to the bag of tricks.

 

[Mindflux]

Good suggestion. I'll add it to the bag of tricks.

It looks like my quote and parenthesis got inverted at the back end of that expression.

Should be

Eval("28 IN (15,999,28)")

 

[JasonTFleishman]

It looks like my quote and parenthesis got inverted at the back end of that expression.

Should be

Eval("28 IN (15,999,28)")

I'm proud of myself. I tried your code, saw the error, fixed it, and didn't say anything. I'm not and low skilled as I thought.

 

[The_Doc_Man]

Sometimes success is merely a matter of building confidence through practice. Give yourself some credit for figuring this one out and thus contributing to your own confidence.