We haven’t had an Access post in a while, so today, I’ll show Access some love.
We all know that we can create our own User Defined Functions in Excel. Well, did you know you can also create and use your own User Defined Functions in Access?
 » More: Creating and Using User Defined Functions in Access
Wendy writes to ask:
“In Excel, I can use the PROPER function to change my text to proper case. Why is there is no PROPER function in Access? “
Wendy has stumbled on one of the mysteries of life – there are many functions you use in Excel that are not found in Access. Well…it’s not really a mystery. I actually have an idea why this may be.
A Histogram is essentially a visual representation of a frequency distribution – a kind of analysis that categorizes data based on the count of occurrences where a variable assumes a specified value attribute.
Although most Excel users think of Access as simply a place to store data, I often encourage building analyses, like histograms, directly in Access. I have tons of thoughts on why Access should be incorporated in these types of analyses, but I’ll go into all that later. For now, I’ll show you an easy way to create a histogram in Access.
It’s always a little embarrassing when you roll your own Function to achieve some functionality that already exists in Excel or Access.Â
Recently, I stumbled across a user-defined function designed to find a week number in Access. I asked the owner of this code why he wrote a function to do this when Access has one. He gave me the ‘thousand-yard stare’, then he went on to explain that he couldn’t find the WEEKNUM function in Access.Â
» More: Getting Week Numbers in Access
For small tables, the Find-and-Replace functionality in Access does provide a quick and easy way to alter data and save time.
But for large tables, the Find-and-Replace functionality essentially crashes and burns, able to process only a few thousand records at a time.
If you do have large tables that choke the Find-and-Replace functionality in Access, one alternative is the REPLACE function.
