| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Loop for Error check
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Loop for Error check
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Loop for Error check
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Loop for Error check
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Loop for Error check
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Loop for Error check
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" wrote in message ... 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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Loop for Error check
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" wrote in message ... 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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Loop for Error check
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" wrote in message ... 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" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Loop without Do error | Excel Programming | |||
| check blocks in loop | Excel Programming | |||
| Loop or condition? To check down the rows | Excel Programming | |||
| Create a Loop to check cells between workbooks | Excel Programming | |||
| Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Hybrid Mode
