Microsoft Access Database Functions

Microsoft Access Database Functions

This is something I have touched on briefly in the past with an article called Microsoft Access Functions which introduced you to the popular functions and the tool to generally browse for other not so popular function calls.

So as a revisit to this topic, there’s no point describing each and everyone that’s at your disposal – that would be silly as we all have different uses for our Access databases but instead you can find a full list (by category) by going to Microsoft’s Office Access Functions (by category) page instead.

microsoft access database functions office

Microsoft Access Database Functions

It clearly states that this page contains links to articles that provide details about common functions used in expressions in Microsoft Access.

They are arranged first by Continue reading “Microsoft Access Database Functions”

Concatenating Multiple Rows Into Single Line In MS Access

Microsoft Access Database: Concatenating Multiple Rows Into Single Line In MS Access

Now I’m not going to take credit for this next piece of Microsoft Access database VBA code as it was originally created by a well-known Allen Browne – many thanks!

Instead, I’m going to borrow his code for you (my regular readers) just in case you haven’t seen this before and add an element of quickly adding criteria to a query as a workaround to avoid expanding the VBA code itself.

concatenating multiple rows into single line in ms access

There are potential problems and errors this code will throw at you if you try to add criteria in the normal manner or embed any SQL directly into the calling custom function which means using some common sense and logic to deal with the challenge here.

Microsoft Access Database: Concatenating Multiple Rows Into Single Line In MS Access

First of all, let’s get the function to work and the query understood. In a new module, you will need Continue reading “Concatenating Multiple Rows Into Single Line In MS Access”

How To Create A Running Totals Query In Microsoft Access

How To Create A Running Totals Query In Microsoft Access

If you wanted to calculate a running sum (or a cumulative total) for a report; this is simply set by using the correct ‘Running Sum‘ property for a selected control like a numeric bound field (text box) and all you need to do is decide whether this will be over a group or the whole report.

However, this doesn’t exist in a query and to mimic the report feature to recreate running totals query in Microsoft Access, you will need to be a little creative!

running totals query in microsoft accessEven though MS Access queries you do have a ‘group by’ and ‘where clause’ features where you can choose one of the aggregate functions, there is Continue reading “How To Create A Running Totals Query In Microsoft Access”

Microsoft Access Tutorial: How To Create A Leading Dot List

Microsoft Access Tutorial: How To Create A Leading Dot List

If you wanted to generate a table of contents or a set of index pages, the format for this type of document tends to have leading dots connecting narratives with page numbers.

Using Microsoft Word or other word processors and DTP applications; generating these pages is a breeze. With Microsoft Access however, it simply doesn’t exist and to mimic such a document we need to be a little creative here. 

In this Microsoft Access tutorial, I’m going to create a report showing the company name, a contact name and the telephone number sitting (as right aligned) at the end of a dot leader line (see image below).

microsoft access tutorial string function

In fact, the telephone number may appear as right aligned but is in fact Continue reading “Microsoft Access Tutorial: How To Create A Leading Dot List”

MS Access Functions: How To Build A Soundex Or Sounds Like Custom Access VBA Function

MS Access Functions: How To Build A Soundex Or Sounds Like Custom Access VBA Function

Working with MS Access functions (of the built-in variety), you may have noticed a vast list of functions across different categories. By combining (or nesting) functions, you can certainly produce complex output values which will serve you well with your queries, forms and reports.

However, there is one lacking feature (or function) in Microsoft Access that could hinder searching for values and that is a function that can find similar or ‘sounding like’ a string match of characters. This function is widely known as ‘Soundex’ and some database applications do provide their own algorithm to support this technique (including the Soundex SQL Server function).

ms access functions soundex

The algorithm is a phonetic list of character strings based on the pronounced English language and of course this Continue reading “MS Access Functions: How To Build A Soundex Or Sounds Like Custom Access VBA Function”

How To Convert Numbers To Words: Access VBA Function

How To Convert Numbers To Words: Access VBA Function

Once again, I’ve been asked by a client to create procedure to turn a monetary number (numerical value) into its text equivalent and so I wanted to share with you how to convert numbers to words using Visual Basic code that can also be applied not just to your Access database but with any application that entertains VBA code! how to convert numbers to words

How To Convert Numbers To Words: The Steps

This article is intended for users who know and have some VBA code knowledge but need a little push to Continue reading “How To Convert Numbers To Words: Access VBA Function”

How To Concatenate Access Fields In Queries

How To Concatenate Access Fields In Queries – Simple Really!

To join or not to join, that’s concatenation for you! How do you join or concatenate Access fields together for your queries or other objects (including forms and reports) is a matter of mastering the rules and knowing the operators.

There are plenty of simple demonstrations and video tutorials around on the web showing you how to combine and joins fields from tables in a calculation using the concatenate principle.

Most Access developers and users will use the & (ampersand) operator which will join the fields together – like the glue in between two fields. For example:

Fullname: [First name] & " " & [Surname]

However, have you seen this example?

Fullname: [First name] + " " + [Surname]

The above will provide the same results – or does it?

How To Concatenate Access Fields In Queries – Knowing The Difference

So the difference between using the & (ampersand) and the + (plus sign) will sometimes show Continue reading “How To Concatenate Access Fields In Queries”

Access DLookup Function: Why You May Need To Apply This Function

Access DLookup Function: Why You May Need To Apply The Lookup Function

Using the Access DLookup function may be called upon where users need to output a value from other data sources (i.e. tables or queries) where there is no direct relationship.

Hang on a moment! I though Microsoft Access was a relational database system (RDBMS) so why calculate to connect to a value?

Yes it is an RDBMS but depending on how the database was designed and built or if you had inherited an Access database this could well mean having to handle workarounds and the collection of ‘D’ based Access functions are at your disposal.

access dlookupThe Access DLookup function amongst Continue reading “Access DLookup Function: Why You May Need To Apply This Function”

Access Requery Or Not To Requery – That Is The Question

Access Requery Or Not To Requery – That Is The Question

The following four methods can be found in Microsoft Access and have different uses:

  1. Access Requery
  2. Access Refresh
  3. Access Repaint
  4. Access Recalc

They can be split into two parts where the first two points handles records (the data) and the latter two the objects and their components (no data). Therefore you can start by answering the first simple question to help navigate to which part is best to use:

“Do I want to update data or components in my Microsoft Access database?”

access requeryAccess Requery Or Not To Requery – That Is The Question: The Definitions

Access Requery

Using the Requery method you are calling a complete reloading of all records from the underlying table or query, This means Continue reading “Access Requery Or Not To Requery – That Is The Question”

Microsoft Access String Function: Borrowing String Functions from Excel

Microsoft Access String Function: Borrowing String Functions from Excel

I published an article last week on how you can use an Access string function that doesn’t really exist in Microsoft Access. In fact, you can build any function you like but using Microsoft Excel which has the function I wanted to utilise saved my having to create a function from scratch.access string functionCheck it out below how you can do this…

Custom Microsoft Access Functions: How To Borrow Functions From The Microsoft Excel Library

The definition of any function no matter the application is the ability to calculate and process passing variables or values to return a single value (the answer).

All we do is simply Continue reading “Microsoft Access String Function: Borrowing String Functions from Excel”