Error Message when closing form: Cannot contain zero length string (1 Viewer)

JPR

Registered User.
Local time
Today, 06:40
Joined
Jan 23, 2009
Messages
192
Hello,

I am getting the following error message when closing a form: Run Time Error 3315 – Field XXX cannot contain zero length string.

My db is update every month with data that I import from a text file.

Data is imported in a table (table1) and displayed in form1 using a query as records source as to to open the form users select certain criteria.
This data cannot be changed or modified.

This form has also some fields that are duplicated in another table that I call History, which saves data as table1 every month is updates with new records and i need to save the updated information. The old records in Table1 are deleted before I import the new ones.

To save data into table History, I run the following code on the close event:

'Append new data from controls in Table1 to table History.

Dim chgreqrec As Recordset
Set chgreqrec = CurrentDb.OpenRecordset("History")

chgreqrec.AddNew

chgreqrec![NewPhone] = NewPhone
chgreqrec!NewAddress = NewAddress

chgreqrec.Update

Me.NewPhone.Value = Null
Me.NewAddress.Value = Null

DoCmd.Close

The form should be able to close with or without updates to the various fields, meaning even if they are left blank.

Thank you for any help with this matter.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:40
Joined
May 7, 2009
Messages
19,175
check the 2 fields you showed in Design view of your History Table.
on their Property Sheet (below sheet) Allow Zero Length should be set to Yes.
 

JPR

Registered User.
Local time
Today, 06:40
Joined
Jan 23, 2009
Messages
192
Thank you. Considering that these field can even be left blank (not update), should I also indicate REQUIRED YES?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:40
Joined
May 7, 2009
Messages
19,175
required Yes, means the field Cannot be Blank.
 

Micron

AWF VIP
Local time
Today, 09:40
Joined
Oct 20, 2018
Messages
3,476
The old records in Table1 are deleted before I import the new ones.
Why? If you changed the process to append rather than over-write, you would not need the other table nor this process. If that would mean you would have duplicate records, you can do a combination of append and update.

Don't confuse an empty string with null. If you use Is Null (or Is Not Null) on a query field with zls values, you will not get records with zls in them.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:40
Joined
Feb 19, 2002
Messages
42,981
Null and ZLS might look the same and that causes confusion. They are quite different. ZLS is something (blank). Null is nothing (empty) Importing text data can be a problem depending on the source so even though I NEVER allow ZLS in my tables, I might have to deal with them in an import. You can use an expression like the following in your append query. I hope you are not importing directly into the permanent table. If you are, change that to link to the spreadsheet and then run a query to append the data.

IIf(YourField & "" = "", Null, YourField) As FixedYourFirld

The expression concatenates a ZLS with your field contents and compares it to "". So Null & "" = "" and "" & "" = "" which makes the expression work regardless of whether the field is null or a ZLS. It then converts the ZLS result to Null and passes though any other value.

Numeric fields and dates can never be a ZLS (Zero Length STRING) because those data types can not hold a string. So, if you allow ZLS in the fields where the database engine allows strings, you always have to handle two cases - Null and ZLS rather than just a single case of Null. If you go the route of making all columns required, then numeric fields will have to default to 0 or some other value, date fields will have to default to 12/30/1899 or some other value and text fields will have to default to ZLS (blank). The problem with that is, you really don't want to have defaults for every attribute. It makes no sense to allow a company name or a last name to be blank and when you set numeric fields to default to 0, you are also assigning a value. 0 has a meaning. If you're building a studentMarking appliation and you add the record for the test, the grade for the test defaults to 0 until you enter an actual value. This affects the student's average negatively because:

Avg(85, Null, 90) = 87.5 BUT Avg(85, 0, 90) = 58.3 -- that would have sent my mother into a tizzy:)

So my personal decision is to allow nulls but not ZLS in all fields that are not required and then to deal with potential nulls as necessary in the normal processing.

Some versions of Access sets table level defaults of numeric data types to 0 and others leave the default as null. Be conscious of what your version is doing because you might have to modify each field as you create the table definition.
 

Isaac

Lifelong Learner
Local time
Today, 06:40
Joined
Mar 14, 2017
Messages
8,738
Also, the way Access treats values when a record is first created vs. later edited, although technically correct I feel, can be confusing.

For example. If you create a table with a column of Short Text, and Allow Zero Length = Yes, and Required=Yes, you still won't be able to create a record without typing anything in that column. Why? Because it starts out as Null, not ZLS. If you type something in a new record and save it, then come back later and delete the text out of that value, (so it looks Blank again), you can then save the record. Why? Because now you have overridden the text value that was there with a ZLS (not Null).

In Access specifically, I usually handle validation like this on the Form level. I just find it easier to have it in one place (Form) rather than two places (Form and Table). Also, if I put the burden of validation on the Table, that just lends itself to obscure and vague error messages sometimes when working with Forms, and then if I want to trap those or elaborate on the error, I need more Form code, which then means I had to code some validation in the Form anyway.

Also, avoiding a lot of table-level validation allows me an in-road when backend updates or inserts are required that may violate constraints as a temporary project, pre-population for a survey or collection project, data remediation, one-offs, etc.

(Just the way I approach it, usually but not always, in Access. In other RDBMS table constraints is a whole science, and meaningfully so).
 
Last edited:

Users who are viewing this thread

Top Bottom