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

RickJa
RickJa used Ask the Experts™
on
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



   
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike EghtebasDatabase and Application Developer

Commented:
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 Developer

Commented:
To:

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

& was missing


Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Or you could change your test like this:

If Not IsNull(Me.Client_ctlDescription) and len(Me.Client_ctlDescription) > 0 Then      'Not required

Commented:
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 Developer

Commented:
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 Developer

Commented:
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

Author

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.

Commented:
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 Developer

Commented:
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

Author

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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial