Change characters in report

  • Thread starter Thread starter swansonray
  • Start date Start date
S

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...
 
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
 
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
 
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,...
 
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
 
Back
Top