Run-time error '3315': Field ...can not be a zero-lenght string

I am getting this error (Run-time error '3315': Filed ...can not be a zero-lenght string) when I execute a sub procedure in Access 2000.

My code sets a recordset to a SQL string which pulls a single record in my clients table.
It then attempts to edit that record using a value in a textbox on my form.
I am getting the error EVEN THOUGH MY CODE CHECKS FOR AN EMPTY CONTROL.
The 'Description' field (below example) that which this edit is updating does not have the required property set to YES.
Nor is the control on the form have any restrictions of the like.

It actually used to work fine...now it doesn't...weird.

Here is the sample of code is giving me trouble:
.....
        Client = Me.Client_listClient
       
        strSQL = "SELECT Clients.ClientID, Clients.Date, Clients.Company, Clients.Description,"
        strSQL = strSQL & " Clients.StreetAddress, Clients.City, Clients.State, Clients.Zip, Clients.Contact,"
        strSQL = strSQL & " Clients.OfficePhone, Clients.Ext, Clients.MblPhone, Clients.OfficeFax, Clients.OfficeEmail,"
        strSQL = strSQL & " Clients.RevDate, Clients.Ini, Clients.Notes"
        strSQL = strSQL & " FROM Clients"
        strSQL = strSQL & " WHERE (((Clients.ClientID)=" & Client & "));"

       
        Set rs = db.OpenRecordset(strSQL)
        rs.Edit
   
        If Not IsNull(Me.Client_ctlDescription) Then      'Not required
            rs.Fields!Description = Me.Client_ctlDescription
        Else
            rs.Fields!Description = Null
        End If

.....(more of the same above for different fields)

        rs.update



   
RickJaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Change:

strSQL = strSQL & " WHERE (((Clients.ClientID)=" & Client & "));"


To:

strSQL = strSQL & " WHERE ClientID=" fnClient()


Where (in amodule):

Public Function fnClient() As Long

   fnClient=...Client wherever it is  'Forms!MyForm!Client


End If

If it works, I have no explanation for it.  The reason I am offering this solution is beacause I had similar cases like this in the past.
Mike EghtebasDatabase and Application DeveloperCommented:
To:

strSQL = strSQL & " WHERE ClientID=" & fnClient()

& was missing


ornicarCommented:
There are TWO different properties for a table: 'Required' and 'Allow zero length'. Verify the 'Allow zero length' property, which I guess is set to NO.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 Acronis Global Cyber Summit 2019 in Miami

The Acronis Global Cyber Summit 2019 will be held at the Fontainebleau Miami Beach Resort on October 13–16, 2019, and it promises to be the must-attend event for IT infrastructure managers, CIOs, service providers, value-added resellers, ISVs, and developers.

ornicarCommented:
Or you could change your test like this:

If Not IsNull(Me.Client_ctlDescription) and len(Me.Client_ctlDescription) > 0 Then      'Not required
davidWCommented:
Null and Zero Length String are not the same

Null = nothing
Zero Length String = ""

suggest replace

If Not IsNull(Me.Client_ctlDescription)

with

If Me.Client_ctlDescription <> "" AND Not IsNull(Me.Client_ctlDescription) Then      

Mike EghtebasDatabase and Application DeveloperCommented:
If you suspect some values endup being null, eqip them with Nz function:

strSQL = "SELECT Clients.ClientID, Clients.Date), Nz(Clients.Company,"n/a"), ...
-------------
Nz(Clients.Company,"n/a")

null entry for company will return "n/a"

You need investigate each fied separately.

See help file see what Nz(Clients.Company) does.
 
Thanks,


Mike


Mike EghtebasDatabase and Application DeveloperCommented:
Nz(variant[, valueifnull])

The Nz function has the following arguments.

variant: A variable of data type Variant.

valueifnull: Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string. If you use the Nz function in an expression in a query without using the ValueIfNull argument, the results will be empty in the fields that contain null values


From help file.


Mike
RickJaAuthor Commented:
WOW,,,
Thanks everyone.

Quick comment on what I found after posting question.
I created the client table with imported data.

My code above runs when I click a save button, it gets all the data from unbound controls and saves it to the record.

If when I click save while in a record that was blank upon importing, I get the error.

If when I click save while removing data from a field (within the access environment) I don't get it.

I think the imported data is what contained 'zero-length strings'.  

Does that sound right?
Does it make sense that Access will place a Null when I clear data from an field and other apps (I think I imported an excel sheet) use the 'zero-L...strings'.

Thank you all for your feedback.  I will test and 'accept' an answer tomorrow.
ornicarCommented:
Yes, that sounds. If you created the table from imported data, for sure the 'Allow zero length' is set to NO. Check it and be aware that this is different from 'Allow Null values'.

It is clear that for your app, both have the same meaning, so the settings should be YES for both properties.

Mike EghtebasDatabase and Application DeveloperCommented:
After importing data, you may want to run an update query to replace null numbers with zero and null string with emply string ("") and so on.

In 'Update To' cell of your such an update query you enter:

Nz([MyTable].[Quantity],0)  where Quantity in MyTable you want update.

This way your field shouldn't cause any problem.


Mike
RickJaAuthor Commented:
This seems like the answer for this specific problem.
I am sure all comments posted will be of use as I move on.

Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.