The Use of LIKE

[Deleted member 28156]

Hi
I want my database to search for records where the first character is a specified number. Problem is I have stored a number with an associated string e.g 8AF, 8GH, 8FT etc
I want to look for all records that have a user defined number in the first letter something like:

SELECT Tally.[Pupil Surname], Tally.[Pupil Forename], Tally.[Tutor Group], Tally.Demerits
FROM Tally
WHERE (((Tally.[Tutor Group]) like "[Year]__"));

I know that doesnt work but just to give you an idea what I would like to do.
Im stumped

Cheers bikeboardsurf

 

[pbaldy]

Try

...Like [year] & "*"

 

[RV]

Try this:

WHERE Left(Tally.[Tutor Group],1) = [Enter first character]

RV

 

[pablofonto]

Try entering the function Like, the first number followed by an asterisk

eg. Like 8*

 

[Deleted member 28156]

WHERE Left(Tally.[Tutor Group],1) = [Enter first character]

This works but only when you enter in a year with one digit. Years with two digits dont work as its reading 11 as 1 any ideas?

 

[Deleted member 28156]

Got it working used:
WHERE (((Tally.[Tutor Group]) Like [year] & "*"));

Cheers for help

 

[stephen81]

Got it working used:
WHERE (((Tally.[Tutor Group]) Like [year] & "*"));

You might run into problems here with different numbers of digits for the year as you mentioned earlier. i.e If you enter 1 for the year, it will match any year starting with a 1, including 2 digit years. 1AF, 11AF, 12AF, 13AF etc.

If the part to the right of your year is always letters, you could use something along the lines of

Like [year] & "[!0-9]*"

I think. I haven't tried it, but the idea is that instead of just using * as your wildcard, using [!0-9] looks for any character not in the range in the brackets (the ! is the NOT part). So if you enter 1 as the year, it will only match values that start with a 1 and have the second character matching the bit in brackets (i.e. not a number from 0-9).
Apologies if my explanation isn't very clear. I'll try and clarify any bits you're not sure on.