Make report data different from the one in table - Replace function?

[Ramzess II]

Hello!

There is this report based on query. And there is one field which may contain school names (where one word represents a school status), as well as it may be empty.

school names (in Latvian) are like:

Avotu pamatskola
Sidgundas pamatskola
Turaidas pamatskola
Ozolu vidusskola
Irbes vidusskola
Indras internatskola

what i need and to be honest - don't know how to make it is to make a report where these names would be shown as:

Avotu p/sk.
Sidgundas p/sk.
Turaidas p/sk.
Ozolu v/sk.
Irbes v/sk.
Indras i/sk.

as you see - the word representing the school status is changed to it's short version. I believe since I use access 2003, it is better to use nested replace function but I don't know where to put it (on what event, in form or in report), as well as I don't know how to nest this function.

I would be very thankful if somebody could help.
Thank you in advance.

 

[Pat Hartman]

Status should not be stored in the same field as the school name. Modify your table so that these two pieces of data are separate as they should be. Then you can use an IIf() or lookup table to store one value but print another.

 

[Ramzess II]

Pat, thanks for reply. Sorry I wasn't clear explaining the thing -
I called it 'status' since I couldn't find any other word for that.

If that would be converted to english language then it would be like

Avotu primary school
Sidgundas primary school
Turaidas primary school
Ozolu high school
Irbes high school
Indras boarding-school

well you see what I mend by "status"
So you actually can't split this data in two fields; besides sometimes there would be names like "Avotu primary school - development centre" or "Sidgundas special primary school - children's home"

It all is related to the name, it is official and splitting it won't be good idea.

So, any ideas on how to change these names so they are printed in short version? I really thought that it would be Replace function. Maybe not. Anyway - the question is how?

 

[john471]

write a Function, and call it from a query or report

I borrowed some code I've previously written and adapted it a bit to help you out.

In the attached database (ACC97 version) there is a table (tblSchool) with one field; SchoolName. This table has the examples you supplied, including my extrapolation of the special versions. There is a singule module (modUtils) housing two functions, and there is a query which demonstrates how you might use the function, and shows the results.


Hope this helps

Rgds

John

 

[Ramzess II]

Thank you John! This is what I was looking for. I inspected the code, and tried to understand it. Of course I can't tell you I fully do understand it and that I could write such code by myself, though I got the idea of how it was done. Thank you again!