Do Loop for Error check

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.

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...
--
HTH...

Jim Thomlinson


"Stephen" wrote:

> 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,

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" wrote:

> 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...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Stephen" wrote:
>
> > 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.

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
--
HTH...

Jim Thomlinson


"Stephen" wrote:

> 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" wrote:
>
> > 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...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Stephen" wrote:
> >
> > > 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.

Thanks Jim,

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

"Jim Thomlinson" wrote:

> 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
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Stephen" wrote:
>
> > 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" wrote:
> >
> > > 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...
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Stephen" wrote:
> > >
> > > > 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,

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

"Stephen" wrote:

> 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.

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" <coder@directcarecorp.com.spamalot> wrote in message
news:4EC1B541-FDD3-4009-8134-83B3766E98CB@microsoft.com...
> 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.

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" wrote:

> 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" <coder@directcarecorp.com.spamalot> wrote in message
> news:4EC1B541-FDD3-4009-8134-83B3766E98CB@microsoft.com...
> > 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.
>
>

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

"Stephen" <coder@directcarecorp.com.spamalot> wrote in message
news:CE34AF9D-12A3-4E40-9DDE-FCFA603F902F@microsoft.com...
> 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" wrote:
>
>> 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" <coder@directcarecorp.com.spamalot> wrote in message
>> news:4EC1B541-FDD3-4009-8134-83B3766E98CB@microsoft.com...
>> > 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.
>>
>>