Microsoft Access Sample Code

In Microsoft Access, the InStr function is a powerful tool that returns the position of the first occurrence of one string within another. This function is particularly useful when working with text strings in your database, allowing you to locate specific characters or phrases with ease. For example, if you had a string value of "this is a sentence" and needed to find the first occurrence of a space, you could use the InStr function like this:

Example:

Dim sValue As String
sValue = InStr(1, "this is a sentence", " ")

In this example, sValue would contain a numeric value of 5, as that is the position of the first space in the string.

The InStr function becomes even more powerful when combined with custom functions. Below, we explore three versatile examples:

• Function 1: This function allows you to pass a string and a search term. It returns all the characters up to the first occurrence of the search term. This is ideal for extracting substrings to the left of a specific point.
• Function 2: Similar to the first, but instead of returning the left portion of the string, it returns everything to the right or after the search term is found. This is useful for isolating text that appears after a delimiter.
• Function 3: This function identifies the exact position of the search term and even allows you to specify which occurrence to locate. For instance, in the string "ab ab ab ab djfkd ab dfjkd," you can find the position of the fourth occurrence of "ab" by calling:

sFindByOccurrence("ab ab ab ab djfkd ab dfjkd", "ab", 4)

The result would be 10, as the fourth "ab" starts at position 10. This flexibility makes the InStr function invaluable for text parsing and string manipulation in Microsoft Access.

If you’re looking for advanced string handling or need help implementing custom functions to optimize your database, don’t hesitate to Contact Us. Our team can help tailor solutions to your unique needs.

Public Function sLeftOfFind(ByVal sIn As String, sFind As String) 'returns the part of sIn preceding sFind Dim lPos As Long 'init output sLeftOfFind = sIn 'get position of first sFind in sIn lPos = InStr(1, sIn, sFind) 'return part of sIn to left of first sFind if found If lPos > 0 Then sLeftOfFind = Mid$(sIn, 1, lPos - 1) End If End Function Public Function sRightOfFind(ByVal sIn As String, sFind As String) 'returns the part of sIn after sFind Dim lPos As Long 'init output sRightOfFind = sIn 'get position of first sFind in sIn lPos = InStr(1, sIn, sFind) 'return part of sIn to right of first sFind if found If lPos > 0 Then sRightOfFind = Mid$(sIn, lPos + Len(sFind), Len(sIn) - lPos) End If End Function Public Function sFindByOccurrence(ByVal sIn As String, sFind As String, dOccurrence As Double) 'returns the xth (whatever is specified in the dOccurrence parameter) location of sFind in sIn Dim lPos As Long Dim lPosSecond As Long Dim iCount As Long 'init output sFindByOccurrence = 0 iCount = 1 lPosSecond = 0 Do Until iCount > dOccurrence 'get position of first sFind in sIn lPos = InStr(lPosSecond + 1, sIn, sFind) lPosSecond = lPos 'if no occurrence found, exit the procedure If lPos = 0 Then sFindByOccurrence = 0 Exit Function End If iCount = iCount + 1 Loop sFindByOccurrence = lPos End Function

The Microsoft Access InStr Database is a sample database that shows you how to implement the InStr inside your own Access Database.

Please note that this is a template database. If it does not meet your needs 100%, and you are unable to customize it yourself, let WSI know and we will provide a quote to develop the customizations you need to get you exactly what you want in your Access InStr Database System.

Main Menu

Main Form - Form Criteria

Main Menu Form

Output

Main Menu Form

Main Menu - Modern Look (Access 2010/2013)

Previous Next

$9.99 - this allows you to use this database template however you wish within your organization.

$19.99 for unlimited developer use - this allows you to use this database template however you wish for any applications you develop and distribute.