VBA - Greater than but less than specific date

[Lee Rabbit]

Hi All,

I have a simple login form that requires a passcode, pretty basic.

I am using specified dates to control what passcode is required to continue but I am having a bit of trouble configuring it.

I have 2 dates currently, 08/06/2020 and 09/06/2020 and I am looking for the correct passcode. However, if I enter the incorrect passcode I am getting 2 incorrect passcode msgbox's and then when I enter the correct passcode, it initially says the passcode is incorrect but then on clicking ok on the msgbox it tells me the passcode is correct and continues to destination.

Below is the code I am using. I am still learning so all help is gratefully received.

Private Sub InputButton_Click()

    Dim toDAY As String
    Dim D1, D2 As Date
    Dim P1, P2 As String
    Dim passcode As String
    
    
    passcode = Pword.Text
    toDAY = Date
    D1 = DateSerial(2020, 6, 8)
    D2 = DateSerial(2020, 6, 9)
    
    P1 = "123456"
    P2 = "987654"
    
    If toDAY < D1 And passcode = P1 Then
    MsgBox "Welcome Back", vbInformation
    Unload Me
    
    Else
    
    MsgBox " PASSCODE INCORRECT, PLEASE TRY AGAIN " _
        , vbCritical, "ERROR MESSAGE"
        
    
    If toDAY > D1 <= D2 And passcode = P2 Then
    MsgBox "Welcome Back", vbInformation
    Unload Me
    
    Else
    
    MsgBox " PASSCODE INCORRECT, PLEASE TRY AGAIN " _
        , vbCritical, "ERROR MESSAGE"
    
    End If
    End If
    
End Sub

Thanks in advance.
Lee

 

[Lee Rabbit]

I thought it was working by using the code below but now, regardless of date, the userform accepts both passcodes as correct.

Basically I need to have P1 expire when D1 date has passed.

Please can I get a little assistance on this.

Private Sub InputButton_Click()

    Dim toDAY As String
    Dim D1, D2 As Date
    Dim P1, P2 As String
    Dim passcode As String
    
    
    passcode = Pword.Text
    toDAY = Date
    D1 = DateSerial(2020, 6, 8)
    D2 = DateSerial(2020, 6, 9)
    
    P1 = "123456"
    P2 = "987654"
    
    If toDAY <= D1 And passcode = P1 Then
    MsgBox "Welcome Back", vbInformation
    Unload Me
    
    Else
    
    If toDAY > D1 <= D2 And passcode = P2 Then
    MsgBox "Welcome Back", vbInformation
    Unload Me
    
    Else
    
    MsgBox " PASSCODE INCORRECT, PLEASE TRY AGAIN " _
        , vbCritical, "ERROR MESSAGE"
    
    End If
    End If
    
End Sub

Thanks in advance.
Regards,
Lee

 

[Joe4]

I have not fully analyzed your code, but this line jumps out at me as not being structured correctly:

   If toDAY > D1 <= D2 And passcode = P2 Then

You cannot do it like that. Each comparison needs to be separate and explicit like this:

   If (toDAY > D1) And (D1<= D2) And (passcode = P2) Then

Note that the parentheses really are not required, but I like to use them for readability.
I think they make it easier to read and to see exactly what is happening.


Also, this is not doing what you think it is:

Dim D1, D2 As Date

That is declaring D2 as Date, but D1 as Variant. Each declaration needs to be explicit, like this:

Dim D1 as Date, D2 as Date

or like this:

Dim D1 as Date
Dim D2 as Date

Note that is not what is causing the issue, but it can allow for errors, as it will only accept date values for D2, but anything at all for D1.

 

[Lee Rabbit]

I have not fully analyzed your code, but this line jumps out at me as not being structured correctly:

   If toDAY > D1 <= D2 And passcode = P2 Then

You cannot do it like that. Each comparison needs to be separate and explicit like this:

   If (toDAY > D1) And (D1<= D2) And (passcode = P2) Then

Note that the parentheses really are not required, but I like to use them for readability.
I think they make it easier to read and to see exactly what is happening.


Also, this is not doing what you think it is:

Dim D1, D2 As Date

That is declaring D2 as Date, but D1 as Variant. Each declaration needs to be explicit, like this:

Dim D1 as Date, D2 as Date

or like this:

Dim D1 as Date
Dim D2 as Date

Note that is not what is causing the issue, but it can allow for errors, as it will only accept date values for D2, but anything at all for D1.

Hi Joe, thanks for the help.

I have implemented what you have advised but I am still able to enter either of the 2 passcodes successfully. I do not get a passcode incorrect message when the date has passed.

Private Sub InputButton_Click()

    Dim toDAY As String
    Dim D1 As Date, D2 As Date, D3 As Date 
    Dim P1 As String, P2 As String, P3 As String 
    Dim passcode As String
    
    
    
    passcode = Pword.Text
    toDAY = DateSerial(2020, 7, 24) '''''''''''' I have set this date to test if P1 does not work when entered but it does
    D1 = DateSerial(2020, 6, 1)
    D2 = DateSerial(2020, 6, 30)
    D3 = DateSerial(2020, 7, 31)
  
    
    P1 = "580214"
    P2 = "639148"
    
    
    If (toDAY > D1) And (D1 <= D2) And passcode = P1 Then
    
    MsgBox "Welcome Back", vbInformation
    Unload Me
    
    Else
    
    
    If (toDAY > D2) And (D2 <= D3) And passcode = P2 Then
        
    MsgBox "Welcome Back", vbInformation
    Unload Me
    
    Else
    
    MsgBox " PASSCODE INCORRECT, PLEASE TRY AGAIN " _
        , vbCritical, "ERROR MESSAGE"
    Pword.Text = ""
    
    End If
    
    
End If
    
End Sub

 

[Joe4]

The big problem is you declared "toDAY" to be a String, when it should be declared as a Date.

Also, I think the logic on your IF statements is faulty. I think D1 will always be less than D2, and D2 will always be less than D3.
So, I think this line:

   If (toDAY > D1) And (D1 <= D2) And passcode = P1 Then

should be this:

   If (toDAY > D1) And (toDAY <= D2) And passcode = P1 Then

and this line:

   If (toDAY > D2) And (D2 <= D3) And passcode = P2 Then

should be this:

   If (toDAY > D2) And (toDAY <= D3) And passcode = P2 Then

 

[Lee Rabbit]

The big problem is you declared "toDAY" to be a String, when it should be declared as a Date.

Also, I think the logic on your IF statements is faulty. I think D1 will always be less than D2, and D2 will always be less than D3.
So, I think this line:

   If (toDAY > D1) And (D1 <= D2) And passcode = P1 Then

should be this:

   If (toDAY > D1) And (toDAY <= D2) And passcode = P1 Then

and this line:

   If (toDAY > D2) And (D2 <= D3) And passcode = P2 Then

should be this:

   If (toDAY > D2) And (toDAY <= D3) And passcode = P2 Then

Thanks Joe, I did notice that toDAY was string not date, I forgot to amend it on here....sorry about that.

The logic now works thank you very much. I am still learning and really appreciate your help.

Lee

 

[Joe4]

You are welcome. Glad it is all working now.
Posting here is a great way to enhance your learning.
Keep Excel-ling!