Why can’t zero-length strings be inserted using IN

Topic

New Reply

Why can’t zero-length strings be inserted using INSERT INTO SQL?

When I try to insert fields of a new record, and if one of them is a zero-length, the insert fails.

I can insert nulls but not zero-length- very odd! Following is my SQL:

strSQL = “INSERT INTO tblNTLogIgnoredEntries (Server, LogName, Detail02) ”
strSQL = strSQL & “VALUES (‘” & rsTrimAdd(“Server”) & “‘, ‘” & rsTrimAdd(“LogName”) & “‘, ”
strSQL = strSQL & “‘” & rsTrimAdd(“Detail02”) & “‘”
strSQL = strSQL & “);”

If any of the values is zero-length, the insert will fail. If all other cases (non-zero and null), insert is successful;

What should I do to have success? My workaround is to populate the source table with a single space character for each zero-length field.

Thank you!

–llyal

Reply | Quote

Replies

My understanding is that the entire SQL string is checked for syntax/compatibility with the design of the table. When I have had problems with this, I have gone to the design of the table I am trying to add records to and ensured that the field is marked as Required=No.

That works, but whether it is the correct thing to do or not, I have no idea .

HTH.

Kiwi44

Reply | Quote

A zero-length string and a null are two very different creatures. A null is the absence of a value. You aren’t really populating the field at all. However, a zero-length string IS a value, although you’ll have a hard time spotting it in a table or query.

There isn’t any good reason to append a zero-length string that I can think of. Text fields and number fields should both accept Nulls. In this case, have you examined why you would have zero-length strings in any of these values in the first place?

Reply | Quote

I converted Null values to zero-length strings because i was having problems with queries when doing text searches (example, queries with LIKE ‘%info%’). I think the queries would return nothing if i search several fields of a record looking for a string match and one of the fields contained a null.

So this is why I replaced nulls with zero-length string. Now I want to filter this table and make a new table of the filtered data. Well, i cannot insert zero-length strings!

The workaround i am using is- to replace the zero-length with a single-space character. I get the results, but i don’t understand why Access cannot handle zero-length strings in INSERT statements.

Thanks!

–llyal

Reply | Quote

It CAN handle them, but only if the field is set to allow them. Access is simply doing what you tell it to do. If you tell it to do something illegal (because the AllowZeroLength property is set to false), you can’t expect it to accommodate you. You need to set the constraint in the Create expression.

As far as the query goes, it will only return records with “info” in the field. It will ignore records with null in the field. If you want to see records with nulls as well, you have to change the criteria expression to include Or Is Null.

Reply | Quote

Got it, and thanks!

–llyal

Reply | Quote

I think I understand the difference (now!) between zero-length strings and nulls. My question now relates to parsing a tagged text file into a database.

I dim the variables as strings, then loop through each record in the file, assigning the text headed by a tag to one of the variables. How do I ensure that the contents of the variable is not carried over to the next record (with nothing to be assigned to the variable). Can I use ‘set myVar = Nothing’, or is there something else?

Kiwi44

Reply | Quote

Not unless MyVar is an object! You only use the Set keyword with an object variable. Ditto for Nothing. What you want is MyVar = Null, assuming that MyVar is a variant. Otherwise, initialize it as an empty string.

Reply | Quote