Microsoft Access Sample Code

In Microsoft Access, the Instr function returns the position of the first occurrence of a string in another string. For example, if you had a string value in your database of "this is a sentence" and you wanted to find the first occurrence of a space, you could use the InStr function as follows:
Dim sValue As String
sValue = InStr(1,"this is a sentence"," ")
sValue would contain a numeric value of 5 as that's the location in the string where the first space occurs.

Below, we have a function that makes use of the InStr function. It allows you to pass in a string as the first parameter. Then, a second parameter contains the phrase or character of which you want to find the first occurrence. The function then returns all the characters to the first occurrence of the search term. The second function is very similar, but instead of returning everything to the left, it returns everything to the right or after the search occurrence is found. The third function is similar to the first two, but returns the position in which the search term is found. You can also specify the occurrence number for which you wish to search. For example, if you have the string "ab ab ab ab djfkd ab dfjkd" and you wanted to find the location where the fourth "ab" is at, this function would work by calling: sFindByOccurrence("ab ab ab ab djfkd ab dfjkd","ab",4) and the function would return 10 telling you that the fourth occurrence of "ab" starts at position 10.

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

Live Demonstration: To experience a more interactive and personalized demonstration of this WSI template, we invite you to contact WSI and schedule a live demonstration with one of our team members. This opportunity allows you to engage directly with us, either as an alternative to or in conjunction with downloading and exploring the demo version of the template at your leisure. Please note that downloading Microsoft Office files, including Microsoft Access database files, might trigger a security warning on your computer. To avoid this and get a comprehensive overview, you have the option of viewing the template through a virtual meeting on Teams or Zoom with a WSI representative.
Once you purchase the database template, you will receive the full version of the database in a standard Access Database file. The purchased database will have full access to the database window, all design objects, the visual basic project code, and you will be able to modify or add to the design in any way you wish (this does not apply to the run-time/compiled versions). If you lack the time to integrate this database into your system, please Contact Us and we will be glad to help with your integration process. If you like the basics of what you see in the database, but need enhancements to make the system functional for your business, please Contact Us and we will develop a quote for you to implement the additional features and enhancements that you require.

$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.