Deleting Certain Text Between Characters In A Cell
Jan 10, 2005
I have a table of 1216 records. The Title fields contain extra notes which I would like to remove. All of the notes have /'s around them. I would like to remove all of the text between the /'s and all of the /'s themselves.
Example:
ACCOUNTS OF CHEMICAL RESEARCH /ALL EXCEPT JAPAN/ /FOR INSTITUTIONS/
Should be:
ACCOUNTS OF CHEMICAL RESEARCH
I don't know how to do this, but I can do it in MS Excel or Access--whichever one is easier.
Is there a function in Access that will clear out the <100,894> leaving me with Joe Doe? To my understanding the Replace function only can replace certain characters. How can I delete everything in between the < > as well?
Hello! I need some serious help! After 25+ years of using Paradox where I work, we are going to Microsoft Office and Access is stumping me on one thing!!! In paradox you could hit Ctrl+D and it would enter the same information as in the cell directly above. I can't find anything that shows that Access has a feature like this. I have our inventory database and I will enter maybe 25 of the same items, but they have different property numbers and locations only. How do I get it to repeat what is in the cell/row above?
I have a memo cell in a table that I'd like users to be able to insert their name into (from the same form). Basically allowing a user to throw their name into the memo, then auto-clear the box. When they enter their name a date will automatically be added.
Obviously if it's bound it will update but then it can't be cleared, and it doesn't paste this to the bottom of my memo, it tries to place it as the first few characters of my memo.
I linked an Excel table and now, in both the table and report, the cell is limiting itself to 255 characters, even though the Excel cell has more. I checked the "data type" and it says "memo" but it keeps truncating to "text".
Where is this limit coming from and can I change the linked table so it won't truncate the text?
My friend extracted some text from a PDF file, pasting it into Excel, and needs to find certain pieces of information. Problem is, the text came out as a long string instead of being broken into cells.
So, what we need to do is find, within the text, the 1st, 3rd, 5th, 7th, 9th, 11th and 13th times the word "Principal" appears, and then report back the name that follows. Within the text, it would read something like
(1234 Principal John Doe)
The number in front of it will change or be in a different format, there are other parentheses in the text (varying number of times within the text), and the name changes, of course. We want to extract the name following the word "Principal"... but only the odd-numbered times they appear. Some cells will have as few as six "Principal" entries, others as many as fourteen.
I'm using Access to export the results of a query to Excel and within the same code I am opening up the spreadsheet to format it. Part of the formatting requires me to find the first empty cell in column A, and then insert the text 'Summary'.
Within Excel, the following code works:
Find empty cell:
Code: Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select Add term 'Summary' to cell:
Code: ActiveCell.FormulaR1C1 = "Summary"
I've tried a few different things based on some code I've found on the net from similar situations, but in just about all attempts I get an object defined error. I've tried dimming the piece of code as an object, but when it comes to VBA, I'm just fumbling through.
I have a simple form (frmAddPaper): txtPaper where user enters name of new newspaper, and cboCity, where user selects the newspapers town.
Also Close and Save buttons.I also have a table, tblCity. Columns as follow: CityID, City, Paper1, Paper2, Paper3, Paper4, Paper5, Paper 6.
Some cities have values (Newspaper names) in just Paper1 field. Others in Paper 1 & Paper 2, and some in Paper1, Paper2 and Paper3.I want to add the txtPaper value, to the first empty column, in the row where cboCity matches City column.
Wise one, I have looked for this throughout the forum but could not fiend an answer. Could you help? I have found a little script to display the number of characters entered into a text box so at to warn the user if they are getting close to the maximum number of characters allowed. See http://www.databasedev.co.uk/counting_characters.html I cannot get it to work. The problem is that I can only enter 1 character. It counts it but then highlights the character I just typed. I then have to click next to it to input the next character. Any Ideas? Cheers, Rene
i how i can get some help with this one, i have imported a txt file, once input into a table it has the fieldname inserted along with the data so for example record_id is the name of the field and in each column i have "record_id=###" the # respresent unique numbers. but what i am looking at doing is creating a "create table query and using an expressions like... "left([record_id],"=") but this won't work.
All the data in the columns has the same thing all have the field name with a = then the data i really need.
I Have Copied The Text Boxes So The Information Is Automatically Displayed To Create The Client Reference, But Rather Than Shortening The Copied Text Boxes To Show Only The First Few Characters Is There A Way Of Limiting The Number Of Characters Shown In The Copied Text Boxes??
I am taking a filing index of City Council Resolutions which is currently based in Excel and transferring the index to an Access database I am building. An issue I am having is that the Resolutions being indexed have a subject description that ranges from about 100 characters to +700. The new Access database must hold the complete contents of the Excel spreadsheet.
The direction I have taken is to divide up the subject field into three text fields as needed ([Subject1], [Subject2], [Subject3]) each with up to 255 characters. In reports I will simply combine these fields into one large text box so the user will see them as one large subject description. Memo fields are not an option because in the near future the database backend will be moved to SQL and I understand that SQL does not always play well with memo fields.
I have a text field on a form and I am trying to paste more than 50 characters (and space) into that field and I get the following message: "The text is too long to be edited.". Is ther a way to increase the paste capacity?
I have an unbound text box. I put a value, run an append query and append the value to the table. I want to know if I can delete the value in the text box automatically when the append query excutes. I am using a macro to run the append query.
I have a DB where I want text entry of the primary key to adhere to a certain format. I'm already using a mask of >LL000000 to force two capital letter and 6 numbers. Is there any way I can force extra restrictions, by making for example the first 3 characters to have to be AB1, thus making every entry follow format: AB1<number><number><number><number><number>
I have a table with a large text field in it, among other fields. What I am trying to do, in a query, is to show only the characters that are between brackets "[" and "]" for that field. And, if there is more than one pair of brackets, show only what lies between the last pair of brackets.
What I've tried so far is use InStr() functions to find these brackets and then use a Mid() function to show the enclosed text. But, it doesn't work well and it gives me a very complex query! In fact, I don't think it is a good idea to even use these functions in my query. That would probably slow it down a lot.
Can someone show me a function that I could use to do what I'm looking for? I need function names that could help me make better searches for more informations.
Hi I'm new to forum and can't find an answer to this problem. I am using Access 2002 and have a memo field in a form that I want people to be able to type in as much text as they want. This is then displayed in a report. The form accepts the text with no problems however when I view it in the report it has been cut to 255 characters as if it is a text box. I have set the properties to 'can grow' to no avail. I have searched the MS Knowledge base for a solution with no luck. I admit I'm not an expert in Access and would really appreciate some help as I have been trying to overcome this problem for days.
I need to import circa 900 .txt files into a dbase (yet to be built), they are all of a standard format/layout. I need to import the top line into about 9 columns. So not sure how, but it can be mapped to pull the same digits per column every time.
I have a table field defined as Plain Text. On a form I have a memo field set up also defined as Plain Text. When I enter data into the memo field on the form and save it, I see HTML characters in the field on the table.
I attached some screen shots of the table definition, form field properties and a look at the data saved to the table with the HTML characters.
I have a table with a huge master list (some hundred thousand) of unique bar code tags, which either have 8 or 12 digits. (Valid characters are "012 345 678 9ACEFHJKLMNPRYXW"). I created a form, which is automatically looking up valid records as the string is entered position by position from left to right. This is working fine and sufficient in 99% of the cases.
Sometimes characters are not readable on the original document, so I need a tool/form, were I can enter only the readable positions of the code and the tag list is then filtered accordingly. For that, I created a separate form with 12 text boxes for each possible position of the bar code and a listbox. This list box should be filtered as the readable characters are entered in their respective positions/textbox.
I have a form that has a textbox in it. I enter text into the text box by scanning a bar code. I then use openreport.cmd to pass the variable in the text box to the report I generate. The report then has a button that when clicked references the variable that I passed to it.
Essentially the problem I am having is that when I set the textbox back to blank, it also erases the contents of the new variable created. I want to either delete the value from the textbox upon scanning the bar code each time, or highlight the value in the textbox for it to be deleted upon the first key stroke. I had read about setting focus to the textbox, but I have been unable to get that to work. Here is my current code for what I am doing.
