Is the value your are seaching on a text or number field?

The % sign as a wildcard works like this:
If %4 Returns any thing ending in 4
If %4% Returns any thing with a 4 anywhere in it
If 4% Returns any thing with a 4 at the start.

So if you want a 4 in the second position then you need the single character wild card indicator ( an underscore _ )
If _4% returns any thing with a 4 in the second position followed by any thing.