> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# VIDEO: "Codifying" Long Names
- URL: https://nolongerset.com/video-codifying-long-names/
- Published: 2022-09-12T18:49:36.000Z
- Updated: 2026-05-08T12:54:51.000Z
- Description: Former Access MVP Jack Stockton translates my SQL Server "Codify()" function to work with Microsoft Access backend databases.
- Author: Mike Wolfe
- Tags: Video, Code Library, #Import 2026-05-20 02:59

Several months ago, I wrote an article about creating shortened codes from long descriptions:

[Abbreviations in Lookup TablesOne way to boost the signal-to-noise ratio on your continuous forms is to use abbreviations rather than full descriptions for lookup tables.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/05/Lookup-Table-Abbreviations.jpg)](https://nolongerset.com/abbreviations-in-lookup-tables/)

## A SQL Server Solution

To make it easier for others to implement, I posted the T-SQL to create a custom function in SQL Server named "**Codify**". The function followed these rules to convert a long description into a short, 2-5 character code (the length is customizable):

- Use the first letter from each word
- Replace instances of 'and' with '&'
- Don't allow a trailing '&'
- If only one word, return the first 2-5 letters in the word

[SQL Server “Codify” FunctionThis function will jump-start the process of converting long descriptions into meaningful abbreviations. It’s great for creating “Code” columns in lookup tables.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/05/Codify-Function.jpg)](https://nolongerset.com/sql-server-codify-function/)

## An MS Access Solution

The big problem with the solution I posted is that it only worked on SQL Server data.

Former longtime Access MVP, Jack Stockton, has rectified that problem by writing an equivalent function that runs in VBA. What's more, Jack presented his solution to a [meeting of the Pacific Access User Group meeting](https://accessusergroups.org/pacific/event/257-2022-09-01/), so now there is [video of the solution](https://www.youtube.com/watch?v=KJs0ENgdwDI) in action.

## Point of Clarification: Uniqueness Not Guaranteed

During the presentation, one of the viewers asked a question about whether the function is guaranteed to create ***unique*** codes.

The short answer to that question is No. 

First off, it's not possible to *guarantee* unique codes that are created from longer strings due to the [pigeonhole principle](https://en.wikipedia.org/wiki/Pigeonhole%5Fprinciple). To make matters worse, the function has no memory. Like a spin of the roulette wheel, it is not influenced by previous calls. 

One could try to account for previously generated codes, but that would be a monumental effort. The costs would far outweigh the benefits.

My preferred approach is to tailor the character code length based on the number of source items. With a half-dozen items, a single-character code may suffice. With several hundred categories, you will want a four- or five-character code to minimize collisions.

Ultimately, you should use the Codify function only as a starting point. I [mentioned this explicitly](https://nolongerset.com/sql-server-codify-function/#creating-abbreviations-from-an-existing-lookup-table) in the original article:

> To be clear, this function only provides a **starting point** for creating lookup table codes. It won't do anything to prevent duplicate abbreviations. Not every abbreviation it creates will be the best fit for the description it goes with. It's main purpose is to jump start the process.

## BONUS MATERIAL: DocTests()

Jack also covered one of my favorite concepts: [](https://nolongerset.com/python-inspired-doc-tests-in-vba/)[D](https://nolongerset.com/python-inspired-doc-tests-in-vba/)ocumentation Tests. He covers the concept at the [12:21 mark](https://youtu.be/KJs0ENgdwDI?t=741) of his presentation.

I love the doc test concept for three reasons:

1. They let you learn by example.
2. The documentation doesn't go stale.
3. Writing tests is simple and easy.

[Python-inspired Doc Tests in VBADoc tests are not a replacement for unit or integration testing. But they do provide the best return on investment (ROI) of any type of test, mostly because the effort to write them is near zero.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2020/09/20200921_173427---gbm18---.png)](https://nolongerset.com/python-inspired-doc-tests-in-vba/)