|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 29th, 2010, 04:37 AM
posted to microsoft.public.access.reports
|
|||
|
|||
Change characters in report
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... 
|
|
#2
January 29th, 2010, 04:43 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Change characters in report
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" wrote in message ... 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...
|
|
#3
January 29th, 2010, 05:31 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Change characters in report
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" wrote: 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" wrote in message ... 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...
|
|
#4
January 29th, 2010, 03:31 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Change characters in report
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,... -- Duane Hookom Microsoft Access MVP "swansonray" wrote: 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" wrote: 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" wrote in message ... 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...
|
|
#5
February 1st, 2010, 08:24 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Change characters in report
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 "Duane Hookom" wrote: 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,... -- Duane Hookom Microsoft Access MVP "swansonray" wrote: 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" wrote: 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" wrote in message ... 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...
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
