CODEWITHSUNDEEP
sqlms-accesssubstring
Chris
Chris

Reputation: 193

MS Access Substring

I created a search Form for my db. It works great, if you enter everything in 100% the same as the db has it stored. What I want to change is the 100% part. If I enter "Jones" in the address tect box and hit search, I want "123 Jones", "497 Jones", etc. to appear in my query. Is it possible to do this?

This is my code. Only thing missing is more of the same basic if statements.

ElseIf sA <> "" Then

qry = "Address"

DoCmd.OpenQuery qry

Edit: This is the SQL

SELECT [Service Calls].[Project Name], [Service Calls].[Service Address], [Service Calls].[Date of Service], [Service Calls].[Type of Call], [Service Calls].Technician, [Service Calls].[Total Billed], [Service Calls].[Zip Code], [Service Calls].[Description of Work], [Forms]![Search]![sA] AS Expr1
FROM [Service Calls]
WHERE ((([Forms]![Search]![sA])=[Service Calls]![Service Address]));

Upvotes: 0

Views: 969

Answers (1)

luk2302
luk2302

Reputation: 57184

SELECT ...
FROM [Service Calls]
WHERE ([Service Calls].[Service Address]) LIKE "*" & ([Forms]![Search]![sA]) & "*";

The * is a placeholder for anything. Pre- and appending it will result in a LIKE query matching every string that contains your search phrase [Forms]![Search]![sA] anywhere in it.

Upvotes: 1

Related Questions