[RESOLVED] Clearing date fields (ms access / vb6 / ado)

I have been working for some time on a large MS Access DB with a VB6 front end, and have come across an issue that I haven't been able to resolve or work around.

My DB has many date fields that I have no problem reading or writing to, until it comes time to occasionally totally delete a date from a given record in the DB, which always gives me a format error. I've tried every possible format and still get an error.

I'm using Jet / ADO / SQL to read and write to the DB.

Aby help??

Thanks as always.

Chief Brad
Fort Worth, TX

use a checkbox in your dtpicker so if its deleted it will be null.

Can you post the code that gives you an error?

Well, that might be headed in the right direction, or at least converging on the solution, but not entirely. Setting dtpicker.value=vbnull, either via the dtpicker checkbox or any other method, apparently ACTUALLY sets dtpicker.value to 12/31/1899, and that's what gets written to the DB field when I run the applicable SQL UPDATE statement.

I just need to be able to write an "empty" value to a given date field in my Access DB. It seems like this should be a trivial task, but I'm still scratching my trivial head trying to make it work.

The one option that keeps floating through the back of my head (Behind the trivial section.), is to change the fields in my database from date fields to text fields and read and write them to the DB that way. All I have to do is write a vbNullString to empty that field out. While this would work, it would require a lot of labor to reformat all of the DB date fields (Lots of them!) and all of the code associated with them.

Chief Brad

Here's the code snippet and the error that I'm getting. Before you look at the code itself, let me explain one thing, for various reasons now lost to antiquity, when I select a date using the dtpicker control, it displays that date in the VB6 application in a text field. The text field is also populated when the date field is read from the DB. While this may seem "goofy", there really was a reason to do it at the time. Please note that as long as the txtHasImpact field from the application contains a valid date string, this code snippet works just fine. It's only when I want to clear out or empty the field in the DB that I have problems.

Here's the code snippet:

Code:

---

"UPDATE GSCoordinationDetail SET HasImpactDate = #" & txtHasImpact.Text & "# WHERE CRIndex = " & strCRIndex"

---

. . . . . and here's the run-time error message.

Quote:

---

Run-time error '-2147217913(80040e07)'
Syntax error in date in query expression '##'

---

It obviously will NOT let me use any non-date string in the SQL statement, which takes me back to my original question, "How do I empty a previously filled date field in my DB??"

Chief Brad

If you want to set a date to nothing the use

Code:

---

cmd = "UPDATE GSCoordinationDetail SET HasImpactDate = NULL WHERE CRIndex = " & strCRIndex

---

What is the value of txtHasImpact.Text when you get the error? As you pointed out, a valid date is required. If it is not a valid date then that is your problem.

Also you should be using parameters. Then you can set the parameter to DBNull and that should solve your problem.

GaryMazzone's response above does the trick!! I know I tried setting the date to Null in the SQL string before, but I'm betting I was leaving the ## on either side which still caused an error.

By the way, just a bit of minutia; do y'all know what the # is really called, other than the "pound sign" or the "number sign"?? It's called an octothorpe. Various unconfirmed and competing claims for the source of this name exist, with the most common being either AT&T or Bell Telephone.

Anyway, y'all gave me just what I needed once again. Thanks y'all!!

Chief Brad