To show what you can do, make a new query. If the field of honor is Field1, put these 3 fields in the query:
Field1
foo: InStr([field1],"_")
bar: Mid([field1],[foo]+1)
Running that query produces everything after the first underscore as output field bar.
I've assumed that you use the Access graphic query builder. I'm not sure that those are available in Jet SQL per se; I've only used Instr and Mid in the Query Builder.
This is simplistic, just to show you how you can get to what you want. For example, foo and bar could be done in one step.
As to your wondering where to do this, my advice would be either
1. use this logic in each query that needs the city suffix;
or if you find that several queries need it, and you can afford the overhead of lodging it in the table, and are tired of typing it in many queries,
2. Add a field to the table, and use this logic as an Update Query.
You can go either way; the first factors to consider are performance and storage size. I suspect that for this little action neither will matter to you, so you might just go with number 2. I will say that 2. has the drawback that you must "stay on top of it" - as the table changes, you must ensure that the table field is maintained regardless of who or what changed it; yet it's handy to have the field instantly at your disposal for other queries or reports. The maintainability aspect is often severe enough that you should stick with approach 1.
As to whether to do this in a report, vs. a query, I suggest that you keep report logic as minimal as possible, for several reasons. Again largely for maintainability, but also for portability, do as much as possible in queries rather than reports. Ideally a report's only variables are its recordsource, filter, and Where clause; and otherwise let the report only do the things that must be done in the report. Even the where clause and filter can be done in the queries rather than the report, so you need not ever even worry about them, though there are occasions where they are beneficial. I even go further to suggest that a report always use a query as recordsource, even when a table is completely ready-to-go-for-reporting and could be specified as recordsource. Queries are vastly more versatile (and so easily) if you want to sort, etc.
Practice with the query builder on test data and focus first on the select, update, and Create Table forms. Many of the excel functionalities are available for queries, though sometimes with a different name. To browse and test out the query functions like Instr, right click on the query builder's Field field and click Build to enter the "Expression Builder." In particular, then double click Functions and click Built-In Functions to see the counterparts to the things you did in Excel (I still do this when I can't remember exactly whether to use CInt or INT or whatever). Sadly, it's not directly linked to the Help file, but I suggest working that puppy at the same time to learn what you can do and how.
FWIW: though already fluent in Excel, I began Access with a "30-day" book and there's absolutely no stigma on anyone's IQ to use "dummy" books when you're starting out with a product, so I'll offer that tip if you haven't already. Before I did the 30 day book, everything I typed in this post sounded like jibberish to me; afterwards, everything I've said above is pretty kindergartenish. IMO! HTH
Naturally, note the sticky message atop this forum's message threads. MrExcel has too much integrity to recommend anything less than excellent.
