Can you make your problem somewhat more clear than you did now?
For example:
- What's a filenumber?
- Is 'OPS103' a department?
- If the filename is 'ABCDEF' is the department AB or ABC ("the first 2 or 3 letters")?
- Why an update query? Why not fill the separate field immediately if the original field is entered or changed?
And I wonder how you implemented the check for correctness of the filename entered. For example, if I would enter MA0102, how do you check if MA is a valid department?
And I wonder why you use Access 97? It's not supported on any later OS than Windows XP (i.e. Vista and 7).
The easy way to solve this seems to me to have 2 basic fields:
1. Department (including the correctness check), f.e. OPS
2. File number, f.e. 0102
Then all you need to derive the filename is a string concatenation. That's an easy formula.
Kees
I'm creating a database using Microsoft Access '97. Within a table I have one field which has a file number. The first 2 or 3 letters relate to the department such as 'OPS103' for operations for e.g. There are 8 departments in total and I'm wanting a seperate department field to be automatically updated when I run an update query. I realise I will need a formula which runs a wildcard and I have tried several formula with no success. My knowledge isn't great, so if anyone can help can you explain in basic terms. Any help would be greatly appreciated, thank you.

Chowhound
Comic Vine
GameFAQs
GameSpot
Giant Bomb
TechRepublic