DateTimePicker Control and Null Values 2

[michiko]

I have an Access 97 database with some null values in date/time fields. I have databound my date time picker controls to a recordset, and set the datafield to the date/time field in my table.

When I pull the record, the dtpicker has no problem with bringing in a null. The problem is when I have to append back to the table. I have to put the DTPicker's value in quotes (ie send it down to the db as a string) in order not to get a syntax error in my sql statement in VB. Access does an implicit conversion on the data, and all works well, but I'm just wondering if anyone else has had this problem?

Connopen and RSopen are general Connection and Recordset procedures.

Dim sql As String

sql = "select * from ser where ser_number = '" & Trim(Text1(0).Text) & "'"
ConnOpen
RSOpen (sql)


If rsSER.EOF Then
sql = "insert into ser (bus_design_rcvd_date, assigned_date, priority) values " & _
"('" & DTPicker1.Value & "', '" & DTPicker2.Value & "','" & Trim(txtPriority.Text) & "')"

 

[mikhailwaxman]

sql="INSERT INTO ser ("
If NOT Isnull(DTPicker1.Value) Then _
sql=sql & "bus_design_rcvd_date,"
If Not Isnull(DTPicker2.Value) Then _
sql=sql & "assigned_date,"
sql=sql "& priority) VALUES ("
If NOT Isnull(DTPicker1.Value) Then _
sql=sql & "#" & DTPicker1.Value & "#,"
If Not Isnull(DTPicker2.Value) Then _
sql=sql & "#" & & "#,"
sql=sql & "'" & Trim(txtPriority.Text) & "')"
It has no meaning to check for rst.EOF,You can delete
this line.

 

[michiko]

Thank you. This is very helpful!

Michiko

 

[HermanvLimbeek]

Hi Michiko,

Click the Let mikhailwaxman know this post was helpful! if you think his/her post helpful. That's the idea...

OK..., I just like to see more stars.

Herman :-Q