query with wildcard??

[ronda]

I have a table for our sales reps that lists their name,initials,and 20 different accounts (they define which accounts). The problem is some of these codes have 5 letters and some have 8. If they have 5 letters that means that the next 3 could be anything
example of table:

Name=Jan Smith
Initials=JAS
1Act=ABCDE
(in this case I need to search for ABCDE***)
2Act=BCDEFGHI
3Act=CDEFG (search for CDEFG***)
4Act=DEFGHIJK

I am using 2 tables in my select query - the one mentioned above and another that has the data (sratio) - they are linked by the initials field. I am comparing a field called CON (from sratio) to the 1Act,2Act etc fields. I have also set a parameter that prompts for their initials.
When I run the query the fields that only have 5 letters are not coming up at all when I use [1Act] Or [2Act] Or [3Act] etc. as the criteria

I also tried:
Like [1Act]&"*"
Or
Like [2Act]&"*"
but it seems like I am getting data from everyone not just the initials I put in.

Any suggestions?

 

[Mile-O]

Before you go any further I'll advise you that Access is not a spreadsheet and it's fields should not be used to store data in the way you would with a spreadsheet. You should do a search on normalisation to find out why having a repeating group (1act, 2act, 3act, 20act) is poor design.

Further to that, the name field should be split down to forename and surname. As you have the forename and surname, you can get the initials from that as calculated values within tables are, like repeating groups, also advised against.