Extract text from field

If you have a filed that contatins the following data:LastName, FirstNameWhere LastName has varing lengths. Can you run an update query to obtain just the LastName part of the field? If so, what would be the command?Many, many thanks in advance.
0
Mary
3/6/2007 7:09:45 PM
access 16762 articles. 3 followers. Follow

1 Replies
1277 Views

Similar Articles

[PageSpeed] 27

On Tue, 6 Mar 2007 14:09:45 -0500, "Mary M" <none@no_email.com> wrote:>If you have a filed that contatins the following data:>>LastName, FirstName>>Where LastName has varing lengths. Can you run an update query to obtain >just the LastName part of the field? If so, what would be the command?>>Many, many thanks in advance.>>Sure. Update LastName toLeft([fullname], InStr([fullname], ",") - 1)and FirstName toTrim(Mid([fullname], InStr([fullname], ",") + 1))             John W. Vinson [MVP]
0
John
3/6/2007 7:43:04 PM
Reply:

Similar Artilces:

populating an Access table from a text file
A text file has data in a format like the below: Title1 Cell 1 data Title 2 Cell 2 data Title 3 Cell 3 data .. .. .. Title 10 Cell 10 data Title 11 Cell 11 data .. .. .. Title 15 Cell 15 data .. .. .. Title N Cell N data I guess I can read this using some VBA code posted at http://www.mvps.org/access/modules/mdl0057.htm and http://www.granite.ab.ca/access/readtextfile.htm, add an Instr function to it so that when I read the text file, I know when to insert the data properly in the table. 1. Is there another way I can directly import the data into an Access 2...

Run a query based on user input to a text box
I am creating a database to manage Auction Bids for a live radio station phone auction weekend. I have a form where the user is entering names as the bidders call in. I want to be able to have entry into a text box cause a query to run and populate a list box so that if a caller has been entered before, the user can click on an existing name. So for example, if the first keystroke is A , then the list box will be populated with all names beginning with A. If the next keystroke is l to give Al then the list box will be repopulated with only names beginning with Al (e.g. Alice, Aliso...

Text is added as attachment after copy and paste
Hi I am using outlook 2000 and every time I want to copy and paste text from somewhere else (e.g. browser), it adds the content as an html-page-attachment to my new mail. I just want to have it added as simple text in the mail-body regards R. Save the text in notepad or something and then Insert the file - but check the button so it inserts it as text, not just as an attachment ;-) r@gnar.de wrote: > Hi > > I am using outlook 2000 and every time I want to copy and paste text > from somewhere else (e.g. browser), it adds the content as an > html-page-attachment to my new ...

Payee Matching using Memo Field?
Note sure if this is specific to BofA, or if I'm just missing something.I am using MS Money Deluxe 2004, but also had same prob with 2000 When I download ofx (problems with qif also) statements the Payee or <name> is truncated to 32 char which normally I wouldnt care since money only allows 32 char in the payee feild. Problem is my transactions in the OFX are like this: <NAME>INTERLINK NETWORK TRANS ****** O <MEMO>INTERLINK NETWORK TRANS ****** ON 07-03 CUSTOMER ******** PAYMENT TO ACME Is it possible to auto match Payees using...

Change Regarding Field
Quite often an e-mail thread has been started before a problem in promoted to a case. Because the regarding file is closed in existing e-mails I cannot link them to the case. Any way to change the regarding field or create a copy of the e-mail with an open regarding field? ...

Populate field on resolution of a case
I want to populate a field on the resolution of a case, however when I created a workflow on the update of status equals resolved, it errors saying "the record is marked as readonly" any ideas on how to do this before it is resolved? On 10 jul, 21:44, "Michael San Filippo" <Mich...@datacubesystems.com> wrote: > I want to populate a field on the resolution of a case, however when I > created a workflow on the update of status equals resolved, it errors saying > "the record is marked as readonly" > > any ideas on how to do this before it i...

To make fields invisible using Dexterity Utilities
Hello I know we could use Windows propoerties to make fields visible or invisible on a Window in a form. I understand we could use Dexterity Utilities to make the fields visible or invisible to user. Could anyone say how to do it Dexterity Utilities is not the correct tool to hide fields from a user. You can do this using Modifier, VBA, Field Level Security or Dexterity. Dexterity can either use triggers or you can create an alternate window. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer MBS Support - Asia Pacific Microsoft Business Solutions http://www.mi...

= Text formula problem
Hi Everyone, I am using an excel spreadsheet as a linked table in an access database for an update query. I had it working very nicely for a period of time. I am not sure if some Microsoft update caused a problem or what. The field I use t o update my database didn't work until I used this formula to make a new row. I have a column of numbers which was retrieved from a data miner program it's called Acct. For some reason Access doesn't like the formatting so I insert a blank column to the right and call that PtNum. In the first cell of this column which is C2, I type ...

Resource Assignments View Filtered on Booked Type Field
I have a project that has been cancelled part way through. To deal with this, I've set the Booked Type field to be proposed for all resources in this project. This works great in that the tasks from this project no longer appear in the resources My Tasks view but they do still appear in the Resource Assignments views I have and I don't seem to be able to filter Resource Assignnments on Booked Type. Is that possible or is there another way I should be going about what I want to achieve? Which is to keep a copy of the cancelled projects plan in Project Server for refere...

Writing rules: Looking for text string in HTML Body
I am trying to fight SPAM by deleting messages that contain certain text strings in the body. It seems certain SPAM messages run in streaks and I can get hundreds of the same message but I am unable to block them with my SPAM software so I want to write a rule to delete them. Writing a "normal" rule and selecting: "with '<TARGET TEXT>' in the body" may not work on an HTML message if the sender has disguised that text as follows: Displayed text: a new home purchase HTML Code for same: a n<AJZDE>ew home purcha</LAHIOJ>se How can I fil...

How do I save Visio help text boxes as html
I have a Visio process flow containing help text boxes (mouse overs) and hyperlinks that I want to save as a web document (html). When saved, the help text boxes are no longer available. How do I save as html so that they are available? ...

how do i show text in the data field of a pivot table?
If you put a text field into the data area of a pivot table, it will summarize the field to show a count, instead of showing the text. You can place the text fields in the Row, Column or Page areas, where their text will show. pum75 wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Changing field sze of labels and text boxes independently
I am using Access 2007. I created a report with the wizard. Now I can't change the size of labels or text boxes without all the fields/labels changing in concert. What do I do to permit changing these things individually? Must be simple, but the help function in Acess 2007 I find not very helpful. On the arrange tab, look for the button for Remove. Highlight the controls and click remove. This will ungroup the controls. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "gg" <gg@discussions.microsoft.com> wrote in message news:92DDB6...

Extract Final Row Mulitple Excel Files into Combined Workbook
Hi, Not sure if anyone can help on this, but I am completely struggling with VBA and have been looking through threads to try and find a solution. I'm trying to open multiple excel files by looking at a specific directiry file and then locate the final row and extract the data into one spreadsheet. I think I've managed to produce a VBA to look at multiple files but the rest.......help ! Can anyone help? Regards, Paul Holmes range("A65536").end(xlup).entirerow.copy destination:= activeworkbook.close get the next workbook "londonpaul" <paul.holmes@scottwilso...

Adding field in Scrolling window
Hello: I was wondering how can I add a field in scrolling window if it is not mapped in DB? Thanks candidcarper Use a local field, and assign a value to it in the Fill script on the scrolling window. "ammad_u@yahoo.com" wrote: > Hello: > > I was wondering how can I add a field in scrolling window if it is not > mapped in DB? > > Thanks > > candidcarper > > ...

Find field in CRM and Case search in CRM
hey: I have a ton of cases with emails attached/regarding each case individually. How can we search these cases for all cases that have a common element, for example: I want to search all my cases for the word "disconnect" to see if it appears ANYWHERE in the case, email, title, or part of this case. Anyone know how I can do this? FYI: Building a specialized advanced find won't work because I need the ability to search ANY word -- today I want Disconnect tomorrow, latency, etc... Actually, an Advanced Find might be a good way to go, because you can save your Advanced F...

how to set value of text bounded on combo box?
Hi, I have a combo box with row sours of a table, and there is a text box. What I want to do is once the user selects a value from the combo box, the text box should be filled by a specific data. For example, the combo box has list of names, and once the user select the name; the text box shows the phone number of the selected name automatically. Note: both the name and phone number are in the same table. How can I do that? Make sure that the query for the combobox returns both the name and phone number. The Phone number does not have to be visible in the combobox. In the combobox A...

convert month text (MAR) to month number (3)
I am trying to work with a data set that unfortunately has spit out all the dates in a text format - i.e. 03/01/2009 is MAR 3 2009. Is there a way to convert that text date to an actual date format? Any advice or suggestions are greatly appreciated. Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1 Debug.Print CDate("MAR 3 2009") = 3/3/2009 One problem with the CDate function is that it will bomb out on things that can't be evaluated as a date. Therefore you may want to use the IsDate fun...

Text after non-breaking space truncated: Publisher 2003 WinXP
Hello All, Watch out folks! If you are using non-breaking spaces in your Pub docs and edit your story in Work (Edit-->Edit Story in Word), beware that the character after the non-breaking space is truncated. Also, when you close the Word edit window and return to Pub the characters are truncated in the original Pub doc. Kinda scary. If some one has time, can you test it and see if you get the same behavior. Thanks in advance ...

Writing to a text file from different SUBrouteens
I open a file using "job" as my file handler, but no other 'sub' can see the file handler. The debugger stops with the error "object required" Public Sub make_job_file() ' create GEO_FILE Dim fso, f1, job Const ForWriting = 2 Set fso = CreateObject("Scripting.FileSystemObject") fso.CreateTextFile ("geo_job.job") Set f1 = fso.GetFile("geo_job.job") Set job = f1.OpenAsTextStream(ForWriting, True) End Sub Sub jobname() job_name = InputBox("input Job Name", "Job Name", job_name, 100, 1) job.WriteLine "...

Angled borders/text and filling in empty cells
Hi, Two quick questions: 1. If you've got a cell formatted with "angled" text (say the orientation is 45 degrees) and you put a side border on that cell, the border also angles. If you want the cell above it, which may or may not have text in it, to have/match the "angled" border like the one below it, how do you do that? 2. If you have a cell range, say A1:G10 with some empty cells and some with data in them, is there a way to fill just the empty cells of that range with a * (or anything for that matter) easily? Thanks! Christina, 1. You can use the ...

Format criteria for text field
I am writing an text box expression in a report as follows: =DLookUp("[BILLINGDATE]","[DATATABLE]","[InDate] =" & [Forms]![Invoices Query]![InDate]) The field INDATE is a text field. BILLINGDATE is a text field also. What delimiter do I need to add to the statement to make it work on a text field? I am getting garbage out. Must I change it to a numeric or date field to make it work? jayC, One has to ask why you are storing dates in a Text field instead of a Date/Time field. If you must keep the dates in a Text field, the format of the dates nee...

Clear all pivot table fields with a Macro
I have a two things I would like to do, ideally with a macro 1. Clear all fields from a pivot table 2. If any of the fields have been filtered - remove the filters I am hoping there is a simple command or two that I have not yet discovered. To put things into perspective: I have multiple pivot tables (all copies of the original table, and each on their own spreadsheet). My plan is to have each table set up for particular queries (that is with different fields and filters). I will then examine the data on each, and sometimes alter the fields as needed. But when I am done I want to press a b...

Custom date field and Fill
I have a customer that is using a date/time fill that isn't calculating the right time. It can be duplicated on a new spreadsheet. Start with "A1" and enter in "1/29/2004 7:30:50 AM" and format the column with this custom type: m/d/yyyy h:mm:ss AM/PM. the fill series should decrease by 20 seconds every cell. So I manually put in the first 5 cells as follows: 1/29/2004 7:30:50 AM 1/29/2004 7:30:30 AM 1/29/2004 7:30:10 AM 1/29/2004 7:29:50 AM 1/29/2004 7:29:30 AM Highlight the series and drag it out past A110. In cell A103 I have lost a second. the value should ...

Automatic deleting of Text ?
I receive regular and a substantial number of (acceptable) messages which contain, at the foot of the message, a "PRIVACY & CONFIDENTIALTY NOTICE". This message is added to the original message automatically, but is clearly not required, nor it it required on replies, which then generate another version of the notice. Is it possible to create a VB script that could do this, perhaps from a toolbar command. Although the actual place of the message will naturally vary (from the start of the message) depending on its length, but it will always be the (around 20) last lines of the me...