Can someone tell me if they spot an endless loop in this code:

[DK8]

I wrote a small function to test date/time and I'm told since I put the code in that users are becoming stuck in an endless loop. I have tested the function myself many times over and haven't encountered any problems. Can someone spot something which I may have overlooked? Thanks in advance.

Code is as follows:

Public Function TestSmallDateTime(datValue)

Dim varRetVal As Variant
Dim datTestSmallDateTime As Date
Dim datDefaultDate As Date
Dim blnContinue As Boolean
Dim datHighDate As Date
Dim datLowDate As Date
datLowDate = #1/1/1900#
datHighDate = #6/6/2079#

Do Until TestSmallDateTime = datValue

If (IsDate(datValue)) Then
If datValue >= datLowDate And datValue <= datHighDate Then
blnContinue = True
TestSmallDateTime = datValue
Else
blnContinue = False
MsgBox "You entered an invalid date!! Must be less than " & DateAdd("y", 1, datHighDate), vbOKOnly, "Bad Date"
End If
Exit Do
Else
blnContinue = False
MsgBox "You entered an invalid date!! Must be less than " & DateAdd("y", 1, datHighDate), vbOKOnly, "Bad Date"
End If
Exit Do

Loop

End Function

 

[rberbe2002]

Loop ?????

wat is this for?

 

[DK8]

The purpose of the loop is to keep prompting them until they enter a date which falls within the parameters of small date time. Thank you

 

[gemma-the-husky]

you arent ever testing the boolean value, and as your function as no type the do unitl test wont succeed

the easiest way to break the loop is after

TestSmallDateTime = datValue put
exit function

 

[DK8]

Thank you, I've got another problem now, but your idea worked to get me past my initial problem.

 

[DK8]

Is 2nd else statement necessary or can it be worked around?

In my code, I have two else statements, both of them identical. All I'm trying to accomplish is to have the user prompted with a msg box until they enter a date which falls into the smalldatetime range. I wonder if I"m going about this the wrong way? Can I simply refocus on the msgbox if a user enters a bad date, or can someone think of another workaround? I'm not sure how to proceed as I'm new to programming and VBA. Thank you,

 

[pbaldy]

Another way (you have to give the user a way to enter another value):

Recheck:
  YourVariableHere = InputBox(...)

  If YourTestHere Then
    MsgBox "Whatever"
    GoTo Recheck
  End If

 

[DK8]

Thank you

Your idea works as well

 

[modest]

I wrote a small function to test date/time and I'm told since I put the code in that users are becoming stuck in an endless loop. I have tested the function myself many times over and haven't encountered any problems. Can someone spot something which I may have overlooked? Thanks in advance.

Code is as follows:

Public Function TestSmallDateTime(datValue)

  Dim varRetVal As Variant
  Dim datTestSmallDateTime As Date
  Dim datDefaultDate As Date
  Dim blnContinue As Boolean
  Dim datHighDate As Date
  Dim datLowDate As Date
  datLowDate = #1/1/1900#
  datHighDate = #6/6/2079#
  
  Do Until TestSmallDateTime = datValue
 
      If (IsDate(datValue)) Then
      If datValue >= datLowDate And datValue <= datHighDate Then
        blnContinue = True
        TestSmallDateTime = datValue
        Else
        blnContinue = False
        MsgBox "You entered an invalid date!! Must be less than " & DateAdd("y", 1, datHighDate), vbOKOnly, "Bad Date"
      End If
  Exit Do
        Else
        blnContinue = False
        MsgBox "You entered an invalid date!! Must be less than " & DateAdd("y", 1, datHighDate), vbOKOnly, "Bad Date"
      End If
  Exit Do
         
        Loop
     
End Function

Just so you know, datTestSmallDateTime is never initialized or used. Furthermore Public Function TestSmallDateTime(datValue) should include the return type:Public Function TestSmallDateTime(datValue) As Date

The following code I've written would be better than using labels and checks if the date value exists. For instance 4/31/1999 is not a valid date value, since there are not 31 days in april.

Public Function InputDate() As Date

    Dim sInput          As String       'Date input from user
    Dim sMsg            As String       'Message for input box
    Dim dtLowrBound     As Date
    Dim dtUpprBound     As Date
    
    dtLowrBound = #1/1/1970#
    dtUpprBound = #12/31/2099#
    

    Do
        sMsg = "Please input the date between " & dtLowrBound & " and " & dtUpprBound & " (ex. 12/20/1995):"
        sInput = InputBox(sMsg, "Input")
        sInput = Format(sInput, "mm/dd/yyyy")
    
        Do Until IsDate(sInput)
            sMsg = sInput & " is an invalid date value." & vbNewLine & _
                   "Please input the date between " & dtLowrBound & " and " & dtUpprBound & " (ex. 12/20/1995):"
            sInput = InputBox(sMsg, "Input")
            sInput = Format(sInput, "mm/dd/yyyy")
        Loop
        
    Loop While Not (sInput >= dtLowrBound And sInput <= dtUpprBound)
    
    InputDate = CDate(sInput)

End Function

 

[gemma-the-husky]

if your date is in a bound field (oe even if not, but a bit more validation required) you dont need to do any of that

all you need is in the beforeupdate event the following, which is much more readable. the cancel = vbcancel tells access not to accept the entry

if nz(datefield,0)=0 then
    call msgbox("date required")
    cancel = vbcancel
    exit sub
end if


if datefield<#[lowdate]# or datefield>#highdate# then
    call msgbox("date must be between etc and etc")
    cancel = vbcancel
    exit sub
end if

 

[DK8]

Thank you both for the replies, I haven't gotten around to it yet, but will evaluate your suggestions. Have a great weekend!

 

[modest]

It all depends on what you're trying to do - there are many solutions out there.