Error in SQL query containing Dates when invalid date is entered [resolved]

[DKasler]

ok i have an SQL statement that generates an error if a non-existant date (ie. 2/30/2005) is entered in to the field text2

sSQL = "select patdob,insurance,vaccine,Dose,dov,type,datediff('m',patdob,dov),vaccine from vfcrec Where type='" & Combo1.Text & "' and dov between #" & Text1.Text & "# and #" & Text2.Text & "# and vaccine= '" & DBCombo1.Text & "' order by datediff('m',patdob,dov)asc ;"

Is there any sort of statment like notdate or something I can use to tell the system that if the date can not exist on a calander to ignore the value?

thanks tons =)

[Hack]

First, since you are getting your dates from a Textbox, it would be simpler to validate the dates there rather than modify your query.

Second, you could use IsDate on the data in the textboxes to validate the entries.

Finally, why not lose the textboxes thus saving the need to validate and provide your customer with a date control from which it is not possible to pick a bad date. Something like the DatePicker control, or the Microsoft Calendar control.

[DKasler]

well... The MS Calander control would be perfect. except its a bit large for the form I am using...

Where can I get the "DatePicker" control you mentioned?

[Hack]

Where can I get the "DatePicker" control you mentioned?

Project/Components Microsoft Windows Common Controls-2 6.0

The thing I like about the datepickter is that it takes up no more room than a combo box.

To get the user selection, use: DTPicker1.Value and you probably will want to use Format in conjunction with this to get the selected date just the way you want it.

[DKasler]

Awesome. Thanks so much.