RickJa
asked on
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_ctlDescri
rs.Fields!Description = Me.Client_ctlDescription
Else
rs.Fields!Description = Null
End If
.....(more of the same above for different fields)
rs.update
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_ctlDescri
rs.Fields!Description = Me.Client_ctlDescription
Else
rs.Fields!Description = Null
End If
.....(more of the same above for different fields)
rs.update
To:
strSQL = strSQL & " WHERE ClientID=" & fnClient()
& was missing
strSQL = strSQL & " WHERE ClientID=" & fnClient()
& was missing
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or you could change your test like this:
If Not IsNull(Me.Client_ctlDescri
If Not IsNull(Me.Client_ctlDescri
Null and Zero Length String are not the same
Null = nothing
Zero Length String = ""
suggest replace
If Not IsNull(Me.Client_ctlDescri
with
If Me.Client_ctlDescription <> "" AND Not IsNull(Me.Client_ctlDescri
Null = nothing
Zero Length String = ""
suggest replace
If Not IsNull(Me.Client_ctlDescri
with
If Me.Client_ctlDescription <> "" AND Not IsNull(Me.Client_ctlDescri
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
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
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
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
ASKER
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.
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.
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.
It is clear that for your app, both have the same meaning, so the settings should be YES for both properties.
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)
This way your field shouldn't cause any problem.
Mike
In 'Update To' cell of your such an update query you enter:
Nz([MyTable].[Quantity],0)
This way your field shouldn't cause any problem.
Mike
ASKER
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!
I am sure all comments posted will be of use as I move on.
Thank you!
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.