Do Loop for Error check

[Stephen]

Hi Folks,

In my solution I'm asking for a user to input two parameters that are
strings that I am then passing to an SQL query. Everything works great but I
don't think I am properly or efficiently checking that the parameters are not
either blank or mis formatted. Any suggestions are always greatly appreciated.

' Ask for Data Range Input
dtStartDate = InputBox("Enter a starting date for the report range.",
"Beginning Date Range", "XX/XX/XXXX")
dtEndDate = InputBox("Enter an end date for the report range.", "Ending Date
Range.", "XX/XX/XXXX")

' Check to make sure there has been data entered for the report dates
Do While dtStartDate = ("XX/XX/XXXX")
dtStartDate = InputBox("You must enter a start date for the report
range.", "Beginning Date Range", "XX/XX/XXXX")
Loop
Do While dtStartDate = ("")
dtStartDate = InputBox("You must enter a start date for the report
range.", "Beginning Date Range", "XX/XX/XXXX")
Loop

Do While dtEndDate = ("XX/XX/XXXX")
dtEndDate = InputBox("You must enter an end date for the report range.",
"Ending Date Range", "XX/XX/XXXX")
Loop

Do While dtEndDate = ("")
dtEndDate = InputBox("You must enter an end date for the report range.",
"Ending Date Range", "XX/XX/XXXX")
Loop

Thanks.

 

[Jim Thomlinson]

For this type of thing I like to convert the input string to a date. If the
conversion fails then I let the user know and have them re-enter the date.
Otherwise I Format the date as a string in the correct way and pass that to
the query. This way I do not rely as heavily on the user to do the right
thing. Just my two cents... If you need help with that let me know...

 

[Stephen]

Jim,

I understand what you are saying but the conversion is a little over my
head. Additionally what may be helpful to you is my query statement as I am
using an ADO conection...

strSQL = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
BETWEEN '" & dtStartDate & "' AND '" & dtEndDate & "' ORDER BY order_no ASC"

Will the conversion change the cyntax of the query statement?

 

[Jim Thomlinson]

This could be made a bit better but it gets you started...

Sub test()
MsgBox GetDate
End Sub

Public Function GetDate() As String
Dim blnIsOk As Boolean
Dim strInput As String

blnIsOk = False
Do Until blnIsOk
strInput = InputBox("Please enter a date")
If IsDate(strInput) Then
GetDate = Format(CDate(strInput), "yyyy/mm/dd")
blnIsOk = True
End If
Loop
End Function

 

[Stephen]

Thanks Jim,

I'll try messing around with it and let you know. I appreciate all the input.

 

[Stephen]

Jim,

I changed your example to better suit my needs and it works like a charm.
Thank you very much for your assistance.

blnIsOk = False
Do Until blnIsOk
dtStartDate = InputBox("Please enter a start date.")
If IsDate(dtStartDate) Then
GetDate = Format(CDate(dtStartDate), "mm/dd/yyyy")
blnIsOk = True
End If
Loop
blnIsOk = False
Do Until blnIsOk
dtEndDate = InputBox("Please enter an end date.")
If IsDate(dtEndDate) Then
GetDate = Format(CDate(dtEndDate), "mm/dd/yyyy")
blnIsOk = True
End If
Loop

 

[RB Smissaert]

It will be simpler and quicker for the user if you used a dedicated date
control.
I use the MonthView control of MSComCtl2 and that works perfect.

RBS

 

[Stephen]

RB,

Thanks for the input but I have to say that I am not familiar at all with
the control you are talking about. If you could provide me with an example
I'd be very much appreciative.

Thanks!

 

[RB Smissaert]

I haven't got a simple example available, but some Googling should find you
one. I can send you my code, but that is complex as it has a few extra
features added and you will have to pick the relevant bits out yourself.
I am sure if you ask in this group somebody will post a simple example.

RBS