General :: Odd-numbered Appearances Of Text Within Cell
Jan 9, 2014
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.
View Replies
ADVERTISEMENT
Jun 19, 2007
If I have the following value in a cell:
Joe <100,894> Doe
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?
Thanks,
Paul
View 4 Replies
View Related
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.
Thanks for help in advance.
-Siena
View 14 Replies
View Related
Dec 7, 2007
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?
View 6 Replies
View Related
Jan 4, 2013
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.
View 14 Replies
View Related
Jul 22, 2005
Putting the same text into every cell in a query column
Hi All,
I sometimes have to mark every record in a query with the same text in a field, when the field is either empty or contains whatever text.
At present I do this by copying the text, and then pasting it (ctrl-v cursor-down, a thousand times) into every cell in a column.
How can I do this in a more efficient way?
Thanks for your help.
Adrian
View 8 Replies
View Related
Oct 13, 2005
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?
thanks :confused:
View 1 Replies
View Related
Sep 20, 2012
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.
View 1 Replies
View Related
Nov 11, 2013
What's the best way of storing arrays of data within a cell? Or should I be creating fields to the size of the array.The size of the arrays are unknown at the moment.
View 7 Replies
View Related
Mar 17, 2014
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.
View 14 Replies
View Related
Oct 19, 2012
I have a table which holds the status of peoples orders. It holds information on order_id, order_status, order_desc, letter.
A letter is printed for each of the different statuses. However for one status i need two different letters to be printed.
How can i insert two values into the same cell.
So far i have done
insert into order_status (order_id,order_status,
order_desc, letter) values('025', 'Dispatched', 'Black tailored coat ','DPT notification');
This prints the DPT notification fine but i need another letter to print along with this.
View 11 Replies
View Related
Aug 18, 2012
The Format Painter seems not to work in Access 2010.The Format Painter button is not in Home / Text Formatting, where you might expect to find it.I create a New Group and add the Format Painter icon. I cannot then use it. It stays greyed out.I add Format Painter to the Quick Access menu. It stays greyed out.If I view a form in Layout mode, I can sometimes get the Format Painter icon to highlight - but I cannot select just a single cell whose format I wish to copy.
Where use of the Format Painter is documented..How to do a very simple thing: copy a format from one cell in a subform to another.
View 3 Replies
View Related
Jun 15, 2013
I am trying to insert a new line below the active cell ive tried several attempts but i am getting error 1004. Some attempts are commented out.
Code:
wks6.Activate
With wks6
.Select
' .Rows.AutoFit
' .Columns.AutoFit
.Range("A:AO").Select
[Code] .....
View 6 Replies
View Related
Feb 25, 2013
I have 2 gig of order data which has duplicates due to different invoice dates. How can I combine the dates into one cell so I only had one row of Order data.
i.e. order number, price, order date, invoice date
same order number, same price, same order date, different invoice date.
How do I combine the second line with the first so that I only have the data once.
View 10 Replies
View Related
Jul 9, 2013
I have subform (Datasheet) is there some code e.g. after update, which can adjust size cell to lenght of data?
View 11 Replies
View Related
Aug 18, 2015
I have imported an Excel table with a column which has several values in one cell separated by "". In my case names of institutions afiliated with a patent. Example of the format of a single cell: MASSACHUSETTS INSTITUTE OF TECHNOLOGY (US) RIVE TECHNOLOGY INC (US)
In order to normalize the Access database I would like to extract the institutions to a separate table (institutions) and that the patent is related to both institutions.
I am using Access 2007.
View 2 Replies
View Related
Dec 10, 2007
I have a table with quite a number of records.
In this table I have a field [Qnumber], which gives me a sequential number.
The field size is Long Integer. So my records are numbered 1, 2, 3, ....n
What I want to achieve is to select only the even numbered records in this table, i.e. Qnumber = 2, 4, 6, ...... etc.
I can't find a way to do this.
Would appreciate any help on this.
View 3 Replies
View Related
Aug 29, 2006
Hi,
I have a table with 400,000 (approx) records in the following format:
a(PK:AutoNum) - b(text)-c(text)-d(Num)-e(text) - f (text - unique/random)
A B C D E F
1 - 6767 - P2 - 1 - 24/992 - 34341212
2 - 6767 - P2 - 1 - 24/993 - 87657483
3 - 6767 - P2 - 1 - 24/995 - 98764536
4 - 6767 - P2 - 2 - 24/996 - 87543297
5 - 6767 - P2 - 2 - 24/998 - 98674635
6 - 6767 - P2 - 2 - 24/999 - 34546576
7 - 6767 - P2 - 2 - 25/001 - 98768547
8 - 6767 - P2 - 2 - 25/002 - 46576897
9 - 6767 - P2 - 2 - 25/004 - 62536475
I need to create a new field in a query that acts like an autonumber, that resets based on an increase in field (d).
ie:
1 - 6767 - P2 - 1 - 24/992 - 34341212 - 1
2 - 6767 - P2 - 1 - 24/993 - 87657483 - 2
3 - 6767 - P2 - 1 - 24/995 - 98764536 - 3
4 - 6767 - P2 - 2 - 24/996 - 87543297 - 1
5 - 6767 - P2 - 2 - 24/998 - 98674635 - 2
6 - 6767 - P2 - 2 - 24/999 - 34546576 - 3
7 - 6767 - P2 - 2 - 25/001 - 98768547 - 4
8 - 6767 - P2 - 2 - 25/002 - 46576897 - 5
9 - 6767 - P2 - 2 - 25/004 - 62536475 - 6
I initially thought I could make an IIF expression that basically says: if [d] in this row is the same as [d] in the previous row then add 1 to the previous record [new number], otherwise, enter 1 in this record [new number] (thereby resetting it)....
I have searched the forums for 'numbered sequences' (amongst other things), and it appears that either the Dcount or Dmax function appear to be the solution, but I'm having some trouble applying it to this problem. I was hoping someone can help me through it.
Cheers
View 1 Replies
View Related
Sep 21, 2004
I have a report listing all individuals within my database by their score. I need to select the top say 40 people from the list, instead of counting down 40 from the top how could I put a number beside each name? Any help would be greatly appreciated.
View 12 Replies
View Related
Dec 14, 2004
I have a inventory list that i export from another programe
it exports the lines like so:
ITEM DESCRIPTION PRICE QUANTITY (this line does not get exported)
1021 60BL+40PI+20SL 11$ 120
1022 50BU+30SL+10BZ 8$ 90
CR5415 50BZ+50BL+50SL 5$ 150
Now the program only calculates the quantitys when a order is submited into it.
it does not subtract the quantity on the color in description.(i am the one that mannualy added the text in description wiche is my pysical inventory)
I am able to export the lines from the orders to another table wich looks like so:
1021 05SL 11$ 5
1021 10PI+10SL 11$ 20
1021 02BL+5PI+5SL 11$ 12
Now the description is again me just mannualy typing the text into the order in description and then the quantity.
the program then just minuses the quantity from the inventory list.
now what i hope to acomplish is be able to take the invetory list, and substract the LineOrders Description from it,
and then just give me the new inventory list.
I figured it goes somthing like this but im not to sure.
If [InventoryItem]=[LineOrderItem]
Then
Compare
If __@@ from [InventoryList]is the same as __@@ from [LineOrder] then subtract ##__ from ##__
And i hope to get at the end my new inventory list in this case it would be
1021 58BL+25PI+05SL
I dont mind about the total quantity since the other program does that for me.
If anybody can help me it would be really apreciated.
I hope i have made sense, if i have not please tell me and i will try again.
thank you
Mark
View 14 Replies
View Related
Oct 24, 2013
I am trying to create a named hierarchy based off a table from an Oil & Gas Program ARIES. The code below is as far as I could get:
Code:
SELECT dbo_AC_ECONOMIC.PROPNUM
, dbo_AC_ECONOMIC.SEQUENCE
, dbo_AC_ECONOMIC.SECTION
, dbo_AC_ECONOMIC.QUALIFIER
, dbo_AC_ECONOMIC.KEYWORD
, dbo_AC_ECONOMIC.EXPRESSION
, (SELECT COUNT(*)
[Code] ....
The results are as such:
PROPNUM...SECTION...SEQUENCE...QUALIFIER...KEYWORD ...EXPRESSION...TEST
TEST1.......4................1.................TAG .............GTC/GAS.......03.................1
TEST1.......4................2.................TAG ............."..................22................ ..1
TEST1.......4................3.................TAG ............."..................22................ ..2
TEST1.......4................4.................TAG .............SHRINK.........1...................1
TEST1.......5................1.................TAG .............ATX...............5.................. .1
TEST1.......5................2.................TAG .............ATX...............5.................. .2
TEST2.......4................1.................TAG .............GTC/GAS.......03.................1
TEST2.......4................2.................TAG ............."..................22................ ..1
The desired Results:
PROPNUM...SECTION...SEQUENCE...QUALIFIER...KEYWORD ...EXPRESSION...TEST...KEYWORD2
TEST1.......4................1.................TAG .............GTC/GAS.......03.................1........GTC/GAS_1
TEST1.......4................2.................TAG ............."..................22................ ..2........GTC/GAS_2
TEST1.......4................3.................TAG ............."..................22................ ..3........GTC/GAS_3
TEST1.......4................4.................TAG .............SHRINK........1...................1.. .......SHRINK_1
TEST1.......5................1.................TAG .............ATX..............5................... 1........ATX_1
TEST1.......5................2.................TAG .............ATX..............5................... 1........ATX_1
TEST2.......4................1.................TAG .............GTC/GAS.......03.................1........GTC/GAS_1
TEST2.......4................2.................TAG ............."..................22................ ..2........GTC/GAS_2
First I will give background on the table and fields. Then I will explain the overall goal for creating of the TEST and KEYWORD 2 fields.
BACKGROUND
PROPNUM: UNIQUE ID
SECTION: Set of data responsible for a certain function EX: section 4 - expenses, section 5 - interest, etc.
SEQUENCE: The order of the sytanx in that propnum's section
QUALIFIER: Qualifies multiples set of syntax per section to differentiate other work (NOT REALLY IMPORTANT for the query)
KEYWORD: A specific word that the program recognizes and treates the expression according to the key word * the quotes keyword is a continuation line and represent the keyword above it*
EXPRESSION: are the variables that are treated by program according to the keyword
OVERALL GOAL: The main goal is to have a spreadsheet of variables used by the program to calculate it's end result. Which means KEYWORD & EXPRESSION by PROPNUM. THE PROBLEM is that the only way to tell that a quote keyword belongs is by having the sequence and section lined up. So my solution is to rename the quote keyword with the primary keyword and a number.
View 2 Replies
View Related
Jan 19, 2015
I have a union query with 2 fields: Order and Row_Heading_Full.
Code:
SELECT DummyClientType.Order, DummyClientType.Client_Type AS Row_Heading_Full
FROM DummyClientType
UNION SELECT ("25") AS [Order], ("Totals") AS Row_Heading_Full
FROM DummyClientType;
The purpose is to pull the Order and Row_Heading_Full fields from a table and add another entry that with "25" as the Order and "Totals" as the Row_Heading_Full at the end of the list.
I want it to be ordered sequentially based on the Order field. But instead it is ordered like this: 1, 10, 11, 12... 20, 21, 22... 3, 4, 5... In other words it is ordering only by the first digit and not by the number as a whole.
When I remove the union aspect it is ordered properly:
Code:
SELECT DummyClientType.Order, DummyClientType.Client_Type AS Row_Heading_Full
FROM DummyClientType;
But that defeats the purpose because I am not adding a final entry of "25" and "Totals".
View 14 Replies
View Related
Apr 4, 2013
Is it possible to have a value in an excel 2010 cell be used to update an access 2010 db?
For example, if in a spreadsheet "test" cell A2=3 then in a db "test" a column "value" is 3. However, if the value in the spreadsheet changes so does the db.
View 1 Replies
View Related
Oct 10, 2013
I have a table (tblContact) with an auto number key field that is numbered sequentially (1-8) there are no deletions, each new record is appended.I wrote a simple FindFirst line to locate a record that is the first record in the table. The FindFirst failed to find the record.So, I wrote a Do Until Loop that cycles through each record looking for the record that I want to find (the first record with key field 1).
Code:
rstContact.MoveFirst
Debug.Print "ContactID: " & rstContact.Fields("ContactID")
Debug.Print "CEmployerID: " & rstContact.Fields("CEmployerID") & vbCrLf
Do Until rstContact.EOF
Debug.Print "ContactID: " & rstContact.Fields("ContactID")
Debug.Print "CEmployerID: " & rstContact.Fields("CEmployerID") & vbCrLf
This works to find the first record... eventually, because it does not begin its search at the first record. The results in the immediate window are below.
Code:
ContactID: 4
CEmployerID: 2
ContactID: 4
CEmployerID: 2
ContactID: 5
CEmployerID: 4
[code]...
I believe the field CEmployerID is unrelated to the issue. I am also attaching screen shots of the table "tblContact" and code with immediate window. I have tried indexing and not indexing the CEmployerID field in the Contact table to no avail. Even though the Do Until Loop eventually finds my record,
View 10 Replies
View Related
Nov 21, 2006
i have a table of articles. A field in the table is ArticleSubject
the ArticleID is made up of 3 letters then 3 numbers. i want the 3 letters to be something according to the subject
for example i want the first 3 letters of the ArticleID to be MAT*** (* is a number) if the subject is Maths
or ENG*** if the subject is English
the subject is picked from a listbox in the same record
how would i do this in a table . i am reluctant to use append or update queries.
but will do so if its the only way.
View 4 Replies
View Related
Jun 23, 2015
I am using Access 2007 and 2010. I would like a text box (or label) on a report to autofil using fixed text from a label on a different (closed) form.
View 8 Replies
View Related
