Date/Time Error 3421

[box2003]

Greetings,

In table T1, I have fields of which five are recognized as text data type,
which hold a date value. On form F1, I have text boxes, of which five have a
input mask of short date, which save to the matching fields in T1. In
testing of my save function, information saves correctly to table T1, in the
date format from form F1. The save function works whether there is a date
value in respective F1 text box or not.

Scenario 1:

When changing the table fields related to the form F1 text boxes, from text
data type, to date/time data types, and saving the values from F1, with ALL
respective F1 text box fields filled with a formatted date, the save works
fine.

Scenario 2

When saving the same text box values from F1, with some of the date fields
blank, the save fails, producing a 3421 error, data type conversion error.
All respective tables fields are in Date/Tiime data type.


Scenario 3

Changing the respective T1 field data types back to Text, then saving the
same text box values from F1, with some of the F1 text box fields left blank
from Scenario 2, that would normally contain a formatted date value, the save
is successfull.


Finding

Even if the T1 field data type is Date/Time, and it is not required a value
be present in the box, when attempting to save a value from form F1, that
should contain a date value and is left blank, the save fails.

What is, or is there, a work around for this? I just changed the field data
type values back to text for the time being, since not all form date fields
will have a date contained in the field all of the time for each save.

 

[Jeanette Cunningham]

The method you used for testing has given you the wrong impression.
Date/time fields can be left empty when saving records in Access, unless the
field is required.

Create a new table with one date/time field and a text field. Make a form
from the table.
Test entering data and saving it. You will find that you can enter something
in the text field, leave the date/time field empty and save the record.

Jeanette Cunningham

 

[box2003]

Your method does work in your example using a bound form. I do not use bound
forms for any Access application I write. None of my forms are bound by
query or table. I write all the code to make a data connection and then save
the records and then close the connection when done for each event procedure.
I am doing something wrong that I have not caught. I have done this before
with success, just now I am having a glitch.

Thank you for your reply, I appreciate another set of eyes.

 

[Jeanette Cunningham]

Unbound forms are not my specialty, neither is ADO.
Better hope someone else spots this, or repost as a new topic and mention
the information that you are using unbound forms and ADO.

Jeanette Cunningham

 

[Allen Browne]

Your method does work in your example using a bound form. I do not use
bound
forms for any Access application I write. None of my forms are bound by
query or table. I write all the code to make a data connection and then
save
the records and then close the connection when done for each event
procedure.
I am doing something wrong that I have not caught. I have done this
before
with success, just now I am having a glitch.

Then why use Access? Why not use a web-interface, or something thinner than
and independent of Access to run your software?

By not using bound forms, you are missing out on all the rapid development
stuff that makes Access so good: automatic handling of data types, automatic
engine-level validation, all the data-centric events and properites of the
forms controls (Dirty, OldValue, BeforeUpdate, ...), continuous forms,
subforms, filters, and so on.

If you really must use unbound forms, you will have to find other ways to
manipuate the data, be that 3rd party controls or grids, or your own data
type validation and generating SQL statements to INSERT/DELETE/UPDATE the
table.

The particular case you describe sounds like you are not handling the values
correctly in your form, or not generating the correct SQL. If Access
believes the value in a control to be a Date/Time type, it will be
represented internally as a peculiar kind of floating point number, where
the integer part represents the date (number of days since 30 Dec 1899) and
the fractional part the time of day. To put that value into a SQL string you
need to express it in the format JET expects, i.e. mm/dd/yyyy, and delimit
with #.

However, if Access believes the value in your unbound control is Text, it is
represented internally as a string of characters, and subject to
interpretation according to the user's regional settings if you try to force
it into a date.

 

[box2003]

The problem appears to be the way I iniitialized the text box values, to "",
and not Null. When initializing the text box values to Null, and saving, the
saves work fine when no date is present in the text box and when the table
data types are Date/Time. An elementary error on my part but, I am glad I
found it and thank you for insight.

Allen,

From day one I learned to program Access 2.0 using the same techniques I do
today, using DAO. I program VB front-ends also and I guess to address part
of the issue with use of Access, I program Access using the same methods I
use when programming in VB 3.0 - present, just about the same techniques.

In the manner I make use of unbound forms, I am more comfortable with my
control over the application when I make use of queries for data
manipulation, recordset, etc...

I am by far no expert, I am just using what works best for me. Perhaps I
should investigate other options. Thanks.