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.

 

[Pat Hartman]

Storing two separate attributes in a single column leads to code like this. It would have been far better to have just left the year where it was. So not only does your design violate first normal form, it also violates second and third so it's a trifecta

Use the Val() function to obtain the leading numeric characters from a text field. It doesn't matter how many characters. It extracts ALL leading numeric characters. Left and Like both have problems.

Where Val(somefield) = someotherfield

BTW - Year is a poor choice for a name since it is the name of a function.