 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008How do I remove zero length string from the fields in my table?
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. Try this --- Replace([YourField], "", NULL)  SignatureKARL 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. 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) [quoted text clipped - 3 lines] > > field has both null and ZLS in them. I wish to know how to remove the ZLS > > and set it back to null. 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 > 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. 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. > Todd > [quoted text clipped - 13 lines] > > field has both null and ZLS in them. I wish to know how to remove the ZLS > > and set it back to null. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.