I need help with this code, keep getting loop without a do error

[Guest]

I am new to writing VBA and just wrote my first function. I am hoping
someone can look at my code. I can't figure out why I keep getting the loop
without a do error message. Thanks in advance to anyone who helps me out.
Here's the code:

Public Function TestSmallDateTime(datValue)

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

TestSmallDateTime = datValue

If datValue >= datLowDate And datValue <= datHighDate Then
TestSmallDateTime = datValue
Else
If datValue >= datHighDate Then
Do Until (datLowDate And datValue <= datHighDate = datValue)
MsgBox "You entered an invalid date!! Must be less than " &
DateAdd("y", 1, datHighDate), vbOKOnly, "Bad Date" & _
datValue = InputBox("Deduction Change Effective Date (mm/dd/yyyy)?
", "Deduction Change", Format$(DATE, "mm/dd/yyyy"))
Loop Until TestSmallDateTime = datValue
End If
End If

End Function

 

[Marshall Barton]

I am new to writing VBA and just wrote my first function. I am hoping
someone can look at my code. I can't figure out why I keep getting the loop
without a do error message. Thanks in advance to anyone who helps me out.
Here's the code:

Public Function TestSmallDateTime(datValue)

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

TestSmallDateTime = datValue

If datValue >= datLowDate And datValue <= datHighDate Then
TestSmallDateTime = datValue
Else
If datValue >= datHighDate Then
Do Until (datLowDate And datValue <= datHighDate = datValue)
MsgBox "You entered an invalid date!! Must be less than " &
DateAdd("y", 1, datHighDate), vbOKOnly, "Bad Date" & _
datValue = InputBox("Deduction Change Effective Date (mm/dd/yyyy)?
", "Deduction Change", Format$(DATE, "mm/dd/yyyy"))
Loop Until TestSmallDateTime = datValue
End If
End If

End Function

You can use Until (or While) on either the Do or the Loop
statement, but not both.

From looking at the code, I can't decipher what you are
trying to do, but the Do statement's Until condition doesn't
make sense to me.

It looks like you retyped your real code in your post and
might have introduced some mistakes during the retyping. To
avoid this kind of confusion, you should Copy the real code
and Paste it into your post.

 

[John Spencer]

You can either have a condition at the beginning of a loop or at the end of
a loop. You cannot put a condition in both places

Do

Loop Until ...

or

Do Until ...

Loop
Beyond that I'm not sure what you are trying to do. Perhaps something like
the UNTESTED revision of your code

Public Function TestSmallDateTime(datValue)

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

If IsDate(datValue) = false then
TestSmallDateTime = Null
ElseIf datValue >= datLowDate And datValue <= datHighDate Then
TestSmallDateTime = datValue
Else
Do Until datValue >= datLowDate And datValue <= datHighDate
MsgBox "You entered an invalid date!! Must be between " & _
datLowDate & " and " & DatHighDate, vbOKOnly, "Bad Date"

datValue = InputBox("Deduction Change Effective Date (mm/dd/yyyy)?",
_
"Deduction Change", Format$(DATE, "mm/dd/yyyy"))
if isDate(datValue) = false then
DatValue = datHighDate
end if
Loop
End If

End Function
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..