Using the And function

[Rob Amor]

I would like a message box to display if one of my reception staff tries to check a customer in on a Monday or a Thursday between 15:30 and 17:30.

So I typed my times into excel and formatted the cell as a number to see how it was stored and then used those numbers in the following hoping that if it worked for one day I could simply add another if statement for the other day;

If Weekday(Date) =6 And Time() > 0.645833333333333 And Time() < 0.729166666666667 Then

Msgbox “Blah”

It doesn’t work. Any 2 of the 3 variables work but when I add the third I get nothing. I am guessing that you can’t use 3 variables in an and statement so I am hoping that somebody can point me in the right direction.

Thanks

[ADezii]

Hopefully, the following will point you in the right direction:

Dim dteDate As Date
Dim dteTime As Date
 
'Will evaluate to True
dteDate = #1/17/2011#           'Monday
dteTime = #4:27:00 PM#          'Within Range (3:30 P.M. - 5:30 P.M.
 
'Will NOT evaluate to True (Out of Range by 1 Minute, Date OK)
'dteDate = #1/13/2011#           'Thursday
'dteTime = #3:29:00 PM#          '1 minute < Lower Range Boundary
 
 
If (Weekday(dteDate) = 2 Or Weekday(dteDate) = 5) And _
   (dteTime >= #3:30:00 PM# And dteTime <= #5:30:00 PM#) Then
  MsgBox "Monday or Thursday between 3:30 P.M. and 5:30 P.M."
Else
  MsgBox "Date and/or Time out of Range!"
End If

[Rob Amor]

Thanks for your reply. I can't work out why, but the above code always evaluates to NOT true and so the out of range msgbox is displayed? It is Monday today and I have set the first time variable (on line 14.) to 11:30 AM to ensure that the current time (1:45 pm) is in range to test everything. Am I being daft and missing something?

Thanks

[ADezii]

Post the Code so we can see exactly what is going on.

[Rob Amor]

Hi ADezil and thanks for your help on this. I am using the code exactly as you posted it except that I quoted out lines 5 and 6 (Although I tried with the lines in). I am using it in the on update event of a textbox So it looks like this..

Private Sub Text188_AfterUpdate()

Dim dteDate As Date
Dim dteTime As Date

'Will evaluate to True
'dteDate = #1/17/2011# 'Monday
'dteTime = #4:27:00 PM# 'Within Range (3:30 P.M. - 5:30 P.M.

'Will NOT evaluate to True (Out of Range by 1 Minute, Date OK)
'dteDate = #1/13/2011# 'Thursday
'dteTime = #3:29:00 PM# '1 minute < Lower Range Boundary


If (Weekday(dteDate) = 2 Or Weekday(dteDate) = 5) And _
(dteTime > #11:30:00 AM# And dteTime < #5:30:00 PM#) Then
MsgBox "Monday or Thursday between 3:30 P.M. and 5:30 P.M."
Else
MsgBox "Date and/or Time out of Range!"
End If

End Sub

[ADezii]

Is Text188 a Text Box containing a Date Value?

[Rob Amor]

Sorry I should have explained that bit. text188 is just a box I added to see if I could get this bit of code working before integrating it with my booking system. I am using its onupdate event as a means of testing my code easily. The box contains no relevant information and in the finished database will not be there.

[ADezii]

If you quoted out Lines 5 and 6 in Post #2, Date and Time Values for comparison would be non-existent.

[Rob Amor]

I tried the code exactly as posted and then quoted out those lines to see if it made any difference. With the lines in, the code always evaluates to true. Quoted out it always evaluates to Not True.

[ADezii]

You must vary either the Date or Time in order to achieve different results.