Microsoft Access 2003

I may have mentioned that my job revolves, in part, around producing queries in Access. There are all sorts of terms that get applied to what I do. Monitoring, Reporting, Data Management, Database Administration, Information Systems, Management Information. Many of these terms mean different things depending on who you are speaking with. So I wouldn’t worry about them.

Instead let’s take a look at some of the functions I have been playing with lately to save my time in processing data for reports.

Replace:

This neat little gem allows you to edit the values in a column. For example, if your database uses some values with context values included then you can remove the context element on the fly. For example, your database includes customer type in front of each order reference number, like this ‘Bus – 657483′ for business users. You put the context values there for a good reason but now you just want the order numbers. What do you do? You use a Replace function, that’s what you do. Something like this,

Expr1:Replace([warehouse.orders],”Bus – “,”")

The replace function includes some error handling so you might want to nest this within an IIF to handle exceptions. For instance, if your report includes some null values there will return an error value. I do it this way, it may not be the best way, but hey, we live to learn.

Expr2:IIF([warehouse.orders] is null,”",Replace([warehouse.orders],”Bus – “,”"))

Short, sweet, and simple. I think that’s enough for now.

Word of the day: Demotic
- adjective
1 – of or pertaining to the ordinary, everyday, current form of a language; vernacular: a poet with a keen ear for demotic rhythms.
2 – of or pertaining to the common people; popular.
3 – of, pertaining to, or noting the simplified form of hieratic writing used in ancient Egypt between 700 b.c. and a.d. 500.