How do I remove zero length string from the fields in my table?

[Todd]

While trying to do a query to return null using IIF, I found irregularities.
I know the problem is that the field allows zero length strings and that the
field has both null and ZLS in them. I wish to know how to remove the ZLS
and set it back to null.

 

[KARL DEWEY]

Try this ---
Replace([YourField], "", NULL)

 

[Jeff Boyce]

Todd

One approach might be to ... (MAKE BACKUP FIRST!) ... create an update query
and use "" for the selection criterion on that field. The update you'd do
would be to update the "value" to Null.


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Todd]

Thank you for a response. When I tried the update query I received an error.
So that did not work the way I did it. I did go back into table design and
change the ZLS to no. That forced it back to Null. I swear I tried that
before posting my question. I must have not performed the action properly
because computers never do anything unexpected, right? Garbage In Garbage Out
etc. Therefore, the answer is to change the field in table design to not
accept ZLS. It pops up a warning but just continue. Magically the query
worked. Thanks for your help.

Try this ---
Replace([YourField], "", NULL)
--
KARL DEWEY
Build a little - Test a little

While trying to do a query to return null using IIF, I found irregularities.
I know the problem is that the field allows zero length strings and that the
field has both null and ZLS in them. I wish to know how to remove the ZLS
and set it back to null.

 

[Todd]

Thank you for a response. When I tried the update query I received an error.
So that did not work the way I did it. I did go back into table design and
change the ZLS to no. That forced it back to Null. I swear I tried that
before posting my question. I must have not performed the action properly
because computers never do anything unexpected, right? Garbage In Garbage Out
etc. Therefore, the answer is to change the field in table design to not
accept ZLS. It pops up a warning but just continue. Magically the query
worked. Thanks for your help.