Syntax between two times in VBA? 1

[MrKABC]

Hello all:

I have an MS Word 2003 document with two VBA text boxes. In one (txtDate) is displayed the current time. The other (txtAfterHrs)is to be blank unless the current time is "after hours". The time displayed in "txtDate" is subject to change by another control in the Word doc, which is why I have the code under a "change" routine.

My problem is that my code worked for a while, and now it does not? I'm thinking I have a syntax error here. Here's the code:

Private Sub txtDate_Change()

    'This statement controls the *after hours* text box
    If txtDate.Value > #4:46:00 PM# And txtDate.Value < #7:59:00 AM# Then
       txtAfterHrs.Value = "**After Hours**"
    Else
        txtAfterHrs.Value = ""
    End If
      
 End Sub

Did I miss something? It seems to be pretty straightforward.

Any help would be most appreciated.

 

[fumei]

Hi. You need to explain:

My problem is that my code worked for a while, and now it does not?

Ok.....uh, it does not. So, hmmm, what IS happening?

Gerry

 

[MrKABC]

Hello Fumei:

I am trying to have "txtAfterHours" display the message "After Hours" between the hours of 4:45PM and 7:59AM.

"txtAfterHours" is NOT displaying the message "After Hours" no matter what time is entered into the "txtDate" text box (by VBA, not by user, so no possibility of mis-keying the entry).

Thank you for the quick response. <smile>

A.

 

[MrKABC]

Hmmm... I may have figured it out, but I'd like you guys to double check me...

'This statement controls the *after hours* text box
        If txtDate.Value < #7:59:00 AM# Then
           txtAfterHrs.Value = "**After Hours**"
        ElseIf txtDate.Value > #4:46:00 PM# Then
            txtAfterHrs.Value = "**After Hours**"
        Else
            txtAfterHrs.Value = ""
        End If

My assumption is that I need to define two scenarios relative to midnight since they occur on two different days?

 

[SkipVought]

If txtDate ALSO contains a DATE component (interger values) then your logic will not work as TIME is a value less than 1; therefore If txtDate.Value < #7:59:00 AM# Then can NEVER be true.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[MrKABC]

Thanks for the info Skip!

I'm sorry... "txtDate" is a misnomer, the text box only contains a TIME now in HH:MM:SS format. <chagrined> Didn't mean to leave out any relevant info.

A.

 

[SkipVought]

So its TEXT and not TIME?

If you execute

msgbox CDbl(txtDate.Value)

what is returned?

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[MrKABC]

Hello Skip:

When I execute your code I get a "run time error 13, type mismatch".

Hmmmm.... digging a little deeper here. This text box is populated by a VBA code elsewhere that is formatted as follows:

MountainTime = DateAdd("h", -1, FormatDateTime(Now(), 3))

The result is indicated in the text box as "11:38:34 PM". When the text box is highlighted I can't increment beyond what is indicated (tells me that the only value in the text box is what is shown.)

Also, the variable "MountainTime" is defined as a DATE.

Your thoughts? I really appreciate the assist!

A.

 

[SkipVought]

So if its TEXT then ...

If txtDate.Value < [b][red]"[/red][/b]7:59:00 AM[b][red]"[/red][/b] Then

I suppose that AM/PM format will evaluate properly. I'd rather use 24 hour format.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}