Querying Subform From Textbox

[mdex]

I have a form with a subform postcode lookup.

I currently have the SQL

SELECT Sites.Site, Sites.Region, Sites.[Site Group], Sites.Street, Sites.City, Sites.[Postal Code], Sites.[Vetting Location]
FROM Sites
WHERE (((Sites.[Postal Code])=[forms]![Site Search]![postsearch]"));

But wanted to be able to search only part of the Post Code.

I've changed the SQL to

SELECT Sites.Site, Sites.Region, Sites.[Site Group], Sites.Street, Sites.City, Sites.[Postal Code], Sites.[Vetting Location]
FROM Sites
WHERE (((Sites.[Postal Code]) Like "*[forms]![Site Search]![postsearch]"));

But no sites are found regardless of part or full postcode being used.

Any ideas?

 

[Golom]

You have [blue][forms]![Site Search]![postsearch][/blue] inside quotes so it is being treated as a string of text and not as a reference to a control on a form. Try

[blue]WHERE [Postal Code] LIKE & "*" & [forms]![Site Search]![postsearch] & "*"[/blue]

which should find any occurrence of [forms]![Site Search]![postsearch] in the field [Postal Code].

The general rule is
[pre]
X LIKE "*ABC" <-- Finds ABC at the end of X
X LIKE "ABC*" <-- Finds ABC at the beginning of X
X LIKE "*ABC*" <-- Finds ABC anywhere in X[/pre]

 

[mdex]

Thanks Golom,

It works perfectly when I took out the errant & just after LIKE.