Replacing individual characters in a field to something else.

I have a field with names that is pulled from a main frame database.  These
names have the last name first, then a comma, then the first name.  I want to
get rid of the commas.  I can do this easily enough with the edit replace.
But this table will be constantly updated from the main frame Db.  I want to
build it as part of an automatic process when running the update.  I though
of using an update query, using a wildcard to find the commas, but cannot
figure out how to update just the character. It wants to update the untire
contents of the field.

any thoughs?

-- 
Message posted via http://www.accessmonster.com

0
vander
4/29/2010 11:55:48 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
527 Views

Similar Articles

[PageSpeed] 31

On Thu, 29 Apr 2010 23:55:48 GMT, "vander via AccessMonster.com" <u59668@uwe>
wrote:

>I have a field with names that is pulled from a main frame database.  These
>names have the last name first, then a comma, then the first name.  I want to
>get rid of the commas.  I can do this easily enough with the edit replace.
>But this table will be constantly updated from the main frame Db.  I want to
>build it as part of an automatic process when running the update.  I though
>of using an update query, using a wildcard to find the commas, but cannot
>figure out how to update just the character. It wants to update the untire
>contents of the field.
>
>any thoughs?

How are you doing the "pulling" - with an update query, an append query, or
what? In either of these you can use a calculated field such as

Replace([fullname], ",", "")

and update the target table to this string; it will replace "Vinson, John"
with "Vinson John".

However, I'd really recommend having separate FirstName and LastName fields
and migrating your data into these fields. If you could describe the update
process and how you'll be using the local table we'd be able to help.
-- 

             John W. Vinson [MVP]


0
John
4/30/2010 12:51:05 AM
Reply:

Similar Artilces:

Too Many Fields Defined Error
I have a database containing ten queries with many calculations in each query. In each query I have a "total" field to total the calculations in each query. I then have a central query which I use as my source for the report. This central query includes the "total" fields from each of the ten queries. When I run the report, I get "Too Many Fields Defined". When I run my central query, everything works fine with no errors. I tried creating a new report based on all of the fields in my central query. My central query consists of 23 fields, 10 of whic...

How can I change the color of the cell when someone enter any number or character?
Hi... =) I am trying to build a templete with a list of questions where the users will answer YES or NO. Please help me to change the color of the cell to Green if they answer yes, and Red if they answer no. Thank you, Sajan Look at conditional formatting in help. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Saz" <Sajan.sajan@gmail.com> wrote in message news:1187012731.422148.93690@l70g2000hse.googlegroups.com... > Hi... =) I am trying to build a templete with a list of questions > where the users will answer YES or ...

Formatting fields with decimal places
I have a table that has field name of Total_Tax with a data type of currency. The data in the field can range from being blank to 999999999.99. Maximum is 9 numbers before the decimal place and 2 afterward. My problem is that the data was imported from a text file into access as 400. However that should be formatted to 4.00 or 1234 should be 12.34. Also a number that is 123456789 should be 1234567.89. I further want to format it to currency for easier reading and manipulation. So I would like to insert $ signs and delimit them with a comma. So I need to go from 12345678 to $123...

Update Avg Field
I have form with Bowlers ID, Bowlers, Name and Avg Points field. On a sub form it shows all the tournaments the bowler has competed in and at the bottom averages out the points he has earned over the events entered. I then want to ave points to be updated in the original form, or even better would be for it to update the Ave Pts field in the bowlers table. How can i get this to happen. the two forms involved are Bowlers Pts (Main Form), Sub Form - Bowlers Query subform. The solution here is to remove the [Avg Points] field from your table. Instead, have Access calculate the value wh...

Fill Multiple Fields
In my form "Jobs" I have a combo box "Contact" and 3 Text boxes "Cell", "email" and "Work #". The contact information comes from my table "Contacts". I would like the info for the 3 text boxes to be filled automatically upon selecting the Conact from the combo box. How do I do this? Thanks, Rob Rob, Please have a look at this article, which discusses options for handling this type of situation: http://accesstips.datamanagementsolutions.biz/lookup.htm -- Steve Schapel, Microsoft Access MVP Rob Roy CC wrote: > In my form...

How do I delete the last character in a field in Excell?
for some reason when i export my contacts to a csv the email addresses are not separted by a comma but by a semicolon. i used the formula that seperates first name and last name together into two seperate columns searching for semicolon instead of space. the result is that a proportion of the email addresses are suffixed by a semicolon. how do i delete the semicolon? thanks Hi You could just use Find/Replace for this to keep life simple. Andy. "andrewcodd" <andrewcodd@discussions.microsoft.com> wrote in message news:C58BCDAC-C7BF-4340-9D64-E3F1880E6F36@microsoft....

auto Selecting or Clicking a field
How to I set VB to automatically selecting a field (in the same way as manually clicking on a listbox)? I have a form with [fo_num] combo box field. After making a selection from this field, the list box [fo_mgr] is updated. I have no problems in my form. The proble is, it does not update the [fo_mgr] field in my table. It only saves the data onto the table whenever I click on the [fo_mgr] manager list box. I would like to be able to code this in VB so the user doesn't need to click on the [fo_mgr] to update the table with the new info. (I posted a similar question in public.access b...

Concatenate with Javascript if 3 fields are present
I have 3 fields in a form that I need to concatenate into a string to populate a new field called Test ID, the format ulfor test id should equal LNAME_DOB_TestDate. Patient Name (format is LNAME, FNAME) DOB TestDate There should always be a comma between the LNAME, FNAME field. I need to parse out the LName to the comma to create the string LNAME_DOB_TestDate. This should only happen when all 3 fields are present. Any help appreciated. Thanks. ...

subforms fields populating from a list of values
I have 3 forms 1 main form a 2nd subform1 related to that and a 3rd subform 1 related to subform 1. there are 3 tables. table 1 (main form built on) has a primary field called field 1 that relates to table 2 (subform 1 built on) called field 2. Table 2 has a primary field called field 1 that relates to table 3 field 2. I would like to know is it possible for a user if they select from a list of values in field one(table 1) of form 1 to show all data associated with that in the other 2 subforms? like a query would only in a form format Also is it possible to select from a list of values i...

Problems linking the fields in two forms
I created a command button on my form so that I can open up another form to save space and make it look neater on my main form. The common field between these two forms is the PO (Purchase Order) number but the subform that opens up does not autopopulate that same number from the primary form - how can I fix this? -- Kenji Could you explain how you open the secondary form (not subform)? Macro? What action(s)? Code? What lines? -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

Access 2000
I created a form from two different tables, but neglected to include two fields that are necessary. Thus, those two fields are not in the fields list. How do I add those two fields? I have seen advice that says use the Properties for the form, in the Data tab, but unfortunately the data tab for this form is absolutely blank. I cannot see any way to modify the query that created the fields list for this form. Access 2000 is the only version of Access that I have, if this is a problem with Access 2000 vice a later version. Hi You want to go to the query tab. Click on the...

Date field format
The date field appears to require the day, month, and year. I'm setting up a catalogue of items where there is an "acquired date" field. Some items have day, month, year and some items have month, year and some items have year acquired only. Is there a way to keep it as a date field but only enter part of the known dates? If not, then I assume it has to change to a text field. If that is the case is there a "better" way to enter the dates so that if needed, it could be queried or sorted as a date field? Examples coming to mind: year-month-day: 2009-...

cell character limit
11.19.09 can the character limit on an excel cell be increased? there is a standard set up limit of 255 characters. that is too little amount. is there a way where we can access the code that determines this limit and change it to elimitate this problem? please help? thx so much The actual limit is 32,767 characters. Of this, only 1,024 will display in a cell. (info from "Excel specifications and limits" in XL help file) Due note that when you set the format of a cell to text, this is when the 256 limit kicks in. Simply change the formatting of the cell ...

finding the id of the field double clicked
I have a class that is derived from the CEdit Class, with a handler in there to recieve a double mouse click to open a lookup form. I would like to use the same class for all of the controls and just on the double click test which control sent the click and then I can pass a structure to the lookup form with the proper information from the corresponding field. Just not sure how to get the name of the control that just triggered the double click. thanks in advance larry "Larry" <lregas@nowhere.com> wrote in message news:%23Si268bpDHA.1496@TK2MSFTNGP11.phx.gbl... > I ha...

Add task fields in a share standard task list
I have a some problems to add some new personal fields in a task list because if I add one or more fields, in a share list, anyone can't see these fields. This task list is in a public folder in a exchange server 2000 and I'm owner of this task list. Any assistance will be appreciated thanks. Is your custom form published? If so, where? You didn't include the form definition with the form did you, that one-off's the form. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003 Reminder Manager, Exten...

HELP: Access table linked to Excel
The problem is as follows: I linked an Access table to a complex Excel spreadsheet (.xls). I also built a custom Access form to browse and modify the data in that table. Works fine, however the worksheet also has numerous calculated cells (with formulas), and these formulas have no effect while I am using the form. The only way to correctly update these cells is to close my Access application, open the spreadsheet in Excel and save it, which is a hardly acceptable process. Is there a way for those formulas to update the corresponding cells automatically, without having to close Access and o...

Find a record based on field on other form using primary key
I have a subform control where I want to click a button and find a matching record on another subform (which is on a different tab). The forms are bound by a Master/Child record field called 'Run_No' The main form is called: frm_Runs (this holds both the host subform and the target subform) The host subform is called : frm_Points The form control that I want to use for the find is called: 'Run_point_Venue_L' The target Subform is called: frm_Getrounds The target subform control that I want find on is called: 'Note' I want to be able to say: Find matching recor...

Pivot Table -Want to display field rather than preform claculation in Data box
Is there a way in a pivot table to simply display a value of a field in the Data box rather than perform a calculation on that value. I don't want to count it, sum it. I just want it displayed. Thanks! Hi if you only have one filed for a row/column item SUM would display that value. So I'm not sure what you're exactly trying to do. A pivot table is used for the aggregation of values -- Regards Frank Kabel Frankfurt, Germany Vincent wrote: > Is there a way in a pivot table to simply display a value of a field > in the Data box rather than perform a calculation on that v...

len replace part 2
**<__>**........<__>........SS<__>..........<__>DDDDDDDDDD<__> **<__>**........<__>..........<__>..SSDDDDDD<__>DDDD..DDDD<__> **<__>**........<__>........SS<__>....DDDDDD<__>DDDDDDDDDD<__> **<__>**........<__>SSDDDDDDDD<__>DDDDDDDD..<__>..........<__> **<__>**......SS<__>DDDDDDDDDD<__>DDDDDDDDDD<__>..........<__> **<__>**....SSDD<__>DDDDDDDDDD<__>DDDDDDDD..<__>DDDDDDDDDD<__> Hi all, this brilliant code below ...

Special characters in excel
How can I replace a ~ with a ALT+ENTER. Try using two ~. edit|replace Find what: ~~ replace with: alt-0010 (hit and hold the alt key and hit 0010 from the number keypad--not above QWERTY.) John F wrote: > > How can I replace a ~ with a ALT+ENTER. -- Dave Peterson ec35720@msn.com ...

Replace Access error message with more descriptive message.
Back again. This time I am having problems with an input form for new patients. The form can not access existing patients. Similar problem as previous post. Form should not allow duplicate SSN's Table called Patient Data contains SSN (text field). There also is a field call ID that is numeric. I have the following event procedure coded to the before update . Private Sub textSSN_BeforeUpdate(Cancel As Integer) Dim strCriteria As String strCriteria = "[SSN] = '" & Me.textSSN & "' AND " _ & "[ID] <>...

Saving As Something Different then A Pub File
I've been working on a 10 page report. I've been adding pages as I go along. However, I want to email but not everyone can open Pub file. It won't save all 10 pages as a jpeg. Only one page I'm in the process of converting it into PDf. Is that the only option? Adriana wrote: > I've been working on a 10 page report. I've been adding pages as I go along. > However, I want to email but not everyone can open Pub file. It won't save > all 10 pages as a jpeg. Only one page > > I'm in the process of converting it into PDf. Is that the only opt...

Additional Fields available in Letter Writing Assistant
I am customizing a letter in letter writing assistant using Applicant Offers as the base. In Word, under Add-Ins, Add Company Fields, Add, Applicant Fields and Add Applicant Offer Fields are available. Is there any way to get access to another file (i.e. requisitions). Thanks I've never seen/heard of a successful customization along these lines, so I think "no". I've looked at it before and don't really see a way to do this. It might be easier to write a new letter from scratch as a Dexterity customization - but that probably wouldn't be easy either. But a...

Calculate Field In Query
I am trying to create a third calculated field that will add two values: Referral To Consult: Nz((DateDiff("d",[MO Referral],[C1S])),0) and Consult To Treat: Nz((DateDiff("d",[C1S],[MO Tx])),0) I do get values for the above two fields but when I attempt to add them using: Total Wait: [Referral To Consult]+[Consult To Treat] criteria <100 I get prompted to enter parameter values for [Referral to Consult] and [Consult To Treat]. Christine Unfortunately, you need to repeat the entire calculation for each item TotalWait: Nz((DateDiff("d",[MO Referral],[C1S]...

workday, networkday or something else?
I have two date and time fields, the Q column is the Ordered Date and the T colum is the Date Closed. We work 8 hours a day from 8-5. Im trying to figure out how many hours / minutes it took someone to do the work from the time it went ordered to the point it closed. Taking into account our work day, which function should i use one of the ones i listed or other ones? Hi, Try this. the formula 'assumes' that you won't take/close any orders outside of the workday. i.e. if you working days starts at 08:00 you won't take an order at 07:00 on that day. ...