Problem Updating Date Fields

I have a form with approximately 20 date fields. Anytime it is updated, only
1 or 2 fields might be populated. The update code updates all 20 fields, but
the code fails on any fields returning "".

What am I missing with saving these fields?

Code example:

docmd.runsql ("update tblTestingDates set testLLC = txttestllc, testHDV =
txttestHDV where customerID = txtcustomerid;")

If one of the controls returns "" I get a conversion failure.

Any ideas are greatly appreciated.

Thanks.

Sharkbyte

Maybe I'm misunderstanding ... it sounds like you are saying your data
includes multiple fields for date-related information. Is that your
underlying data (i.e., tables), or just how you've chosen to display a
well-normalized table structure on a form?

We aren't there. "Anytime it is updated" means ... ?!what?!

It all starts with the data ... start with the data to give us a better
context.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Sharkbyte" - wrote in message
...
I have a form with approximately 20 date fields. Anytime it is updated,
only
1 or 2 fields might be populated. The update code updates all 20 fields,
but
the code fails on any fields returning "".

What am I missing with saving these fields?

Code example:

docmd.runsql ("update tblTestingDates set testLLC = txttestllc, testHDV =
txttestHDV where customerID = txtcustomerid;")

If one of the controls returns "" I get a conversion failure.

Any ideas are greatly appreciated.

Thanks.

Sharkbyte

On Mon, 27 Oct 2008 16:22:09 -0700, Sharkbyte - wrote:

I have a form with approximately 20 date fields. Anytime it is updated, only
1 or 2 fields might be populated. The update code updates all 20 fields, but
the code fails on any fields returning "".

What am I missing with saving these fields?

Code example:

docmd.runsql ("update tblTestingDates set testLLC = txttestllc, testHDV =
txttestHDV where customerID = txtcustomerid;")

If one of the controls returns "" I get a conversion failure.

A Date/Time value can never contain a text string, even the empty text string
"". It can contain NULL unless the field is required - might any of the target
fields have their Required property set to yes? Or are you perhaps copying
text fields into date/time fields?
--

John W. Vinson [MVP]

John, Thank you.

This problem was a by-product of my just starting out w/ developing a
full-blown app, about 3 years ago. And I haven't had to deal w/ a large
number of date fields, on anything else...

Turns out I was running several IIF statements, replacing the NULLs w/ "".
After changing that, everything saves fine.

Thanks, again.

Sharkbyte


"John W. Vinson" wrote:

On Mon, 27 Oct 2008 16:22:09 -0700, Sharkbyte - wrote:

I have a form with approximately 20 date fields. Anytime it is updated, only
1 or 2 fields might be populated. The update code updates all 20 fields, but
the code fails on any fields returning "".

What am I missing with saving these fields?

Code example:

docmd.runsql ("update tblTestingDates set testLLC = txttestllc, testHDV =
txttestHDV where customerID = txtcustomerid;")

If one of the controls returns "" I get a conversion failure.

A Date/Time value can never contain a text string, even the empty text string
"". It can contain NULL unless the field is required - might any of the target
fields have their Required property set to yes? Or are you perhaps copying
text fields into date/time fields?
--

John W. Vinson [MVP]