Change characters in report

[swansonray]

I have a problem and I want to know if I can fix it within the query or the
report.

Data in field equals similar to the following

G-xxxxx
V-xxxxx
A-0xxxx
A-1xxxx

I want the end result if the first 3 characters are A-0 to end up in the
report or query as AA-xxxx
If the first 3 characters areA-1 to end up in the report or query as AB-xxxx
and to leave the others alone, ie... G-xxxxx



It can't be changed upon data entry because of the main program that the
data is entered on. So this is done after I import the tables with the
referenced data.

Thank you in advance...

 

[Duane Hookom]

I would probably create a small user-defined function like:

Public Function ChangeAs(strText as String) as String
strText = Replace(strText,"A-0","AA-")
strText = Replace(strText,"A-1","AB-")
ChangeAs = strText
End Function

You can then use this function almost anywhere in your MDB.

Duane Hookom
MS Access MVP

 

[swansonray]

Hi Duane,

Where would I create this function? As a module or a macro? In the table I
succesfully did a find and replace. If I could get a macro or this function
to do the same thing it would be great. I understand the code, just not sure
where to put it.

Ray Swanson

 

[Duane Hookom]

You create a new, blank module and paste the code into it. Then save the
module with a name like "modStringConversions". You can then use the function
almost anywhere that you would use any function in Access including queries,
code, control sources,...

 

[swansonray]

Thank you Duane, had to tweek it slightly but the end result is there.


Public Function ChangeAs(strText As String) As String
If Left(strText, 3) = "A-0" Then
strText = Replace(strText, "A-0", "AA-")
End If
If Left(strText, 3) = "A-1" Then
strText = Replace(strText, "A-1", "AB-")
End If
ChangeAs = strText
End Function

Ray Swanson