Does anyone know how to return all records with the same first three characters in MS Access (I'm guessing this has to be done in the SQL view)?

For example

andria -doesn't match

Angie -match

Angela -match

Angel -match

Ann -doesn't match

closed as off-topic by mustaccio, LowlyDBA, Gaius, Marco, Jon of All Trades Mar 11 at 16:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, LowlyDBA, Gaius, Marco
If this question can be reworded to fit the rules in the help center, please edit the question.

  • You need the "Like" operator. However, this site is meant for database design, tuning, server setup, business intelligence, advanced SQL, etc. - basic SQL questions are better asked on Stack Overflow. – Jon of All Trades Mar 11 at 16:34

You can use the LIKE operator to perform a wildcard search and return words that contain/start with specific patterns. Given your example above, you can issue the following query to return only those values that begin with "Ang":

SELECT * FROM people WHERE first_name LIKE 'Ang*';

You could CREATE a VIEW that is pre-filtered to only include the data that you want to be displayed:

CREATE VIEW filtered_people (first_name)
SELECT first_name 
  FROM people
 WHERE first_name LIKE 'ang*';

Alternatively, you could just use the query in your code to obtain the data you need:

Sub LikeX() 

Dim dbs As Database, rst As Recordset 

' Modify this line to include the path to your database 
' on your computer. 
Set dbs = OpenDatabase("my.mdb") 

' Return a list of people whose name begins with ANG
Set rst = dbs.OpenRecordset("SELECT first_name" _ 
    & " FROM people" _ 
    & " WHERE first_name Like 'ang*';") 

' Populate the Recordset. 

' Call EnumFields to print the contents of the  
' Recordset. Pass the Recordset object and desired 
' field width. 
EnumFields rst, 15 


End Sub

You can find more information in the official documentation for MS Access SQL.

Not the answer you're looking for? Browse other questions tagged or ask your own question.