Others

How to Count Vowels in Each Word Using an Access Query

Natalie Taylor Oct 16, 2026 869 views

Question details

The user needs to create a query in Microsoft Access that calculates and returns the number of vowels contained within each word stored in a database table.

How to Count Vowels in Each Word Using an Access Query

Product: Microsoft Access
Device & OS: not provided
Scenario: Extracting specific character counts from text data in a table, requiring the query to output the word ID, the word itself, and its vowel count.
Observed behavior: A custom VBA function must be written to loop through characters and count the vowels, which is then called within an SQL SELECT query to display the result.

Before you start

Ensure you have access to the database containing your word table and that macros or VBA code execution are enabled in your Microsoft Access Trust Center settings.

Solution 1Recommended

Create a Custom VBA Function and Use it in a Query

Develop a custom VBA function to evaluate each character in a string and return the total vowel count, then call this function directly from your SQL query.

Since Microsoft Access SQL does not have a built-in function to dynamically count specific characters within a string, creating a user-defined function in VBA is the standard approach. This function iterates through the word, checks each letter, and increments a counter if a vowel is found.

Open the VBA Editor

In your Access database, navigate to the 'Create' tab on the ribbon and click on 'Module'. This will open the VBA editor where you can write your custom code.

Write the Vowel-Counting Function

In the new module, write a function that uses a For loop and the Mid function to test characters. For example: Function CountVowels(myWord As String) As Integer Dim j As Integer, count As Integer, ch As String For j = 1 To Len(myWord) ch = LCase$(Mid$(myWord, j, 1)) If ch = "a" Or ch = "e" Or ch = "i" Or ch = "o" Or ch = "u" Then count = count + 1 End If Next j CountVowels = count End Function

Save the Module

Save the module with a distinct name, such as 'modStringFunctions'. Do not name the module the same as the function itself, as this will cause a #Name? error in your query.

Create the Select Query

Go to the 'Create' tab, click 'Query Design', and switch to 'SQL View'. Write your query to call the function: SELECT WID, MyWord, CountVowels(MyWord) AS VowelCount FROM tblWords;

Run the Query

Click the 'Run' button on the ribbon. The datasheet view will now display the word ID, the word, and the calculated number of vowels for each record.

Create a Custom VBA Function and Use it in a Query

Handling Uppercase and Lowercase: Using the LCase$() function inside the VBA loop ensures that both uppercase and lowercase vowels are correctly identified and counted.

Solution 2

Create a Generic Character-Counting Function

Develop a flexible VBA function that references a table of characters, allowing you to count any specific symbols or letters without changing the code.

Free Microsoft Office alternative

Looking for a Free Alternative to Microsoft Office?

While WPS Office does not include a direct relational database application like Microsoft Access, it offers a powerful, lightweight, and free alternative for Word, Excel, and PowerPoint. For data management and analysis, WPS Spreadsheet provides robust formula functions, VBA macro support, and data processing tools that can handle many complex data tasks easily and affordably.

1. Visit the Official Website: Go to the WPS Office official website to access the latest version.
2. Download the Installer: Click the 'Free Download' button to get the setup file for your operating system.
3. Install and Process Data: Run the installer, open WPS Spreadsheet, and start managing your data and macros seamlessly.

Completely free to download and use for documents, spreadsheets, and presentations.High compatibility with Microsoft Office formats, including .docx, .xlsx, and .pptx.Supports advanced spreadsheet functions and macros for efficient data management.Lightweight design ensuring fast installation and smooth operation on any device.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA function return a #Name? error in the Access query?

This error typically occurs if the VBA module is saved with the exact same name as the function itself (e.g., naming both 'CountVowels'), or if macros are disabled. Rename the module to something distinct like 'modFunctions' and ensure macro execution is enabled in your Access Trust Center settings.

Can I count uppercase vowels using this VBA function?

Yes. By wrapping the character extraction in the LCase$() function within your VBA code (e.g., ch = LCase$(Mid$(myWord, j, 1))), the code temporarily converts every letter to lowercase before checking. This ensures both uppercase and lowercase vowels are counted accurately.

Is it possible to count vowels using only SQL in Microsoft Access without VBA?

Microsoft Access SQL is somewhat limited in string manipulation compared to platforms like SQL Server. It lacks built-in iterative functions or Regular Expressions (RegEx) to dynamically count characters within a single query. Therefore, using a custom VBA function is the most efficient and standard solution.

Office Quick Solutions