Queries :: Replace First Occurrence Of A String
Jan 12, 2015
Here are the 2 tables that I am working with:
Account
Account
0041980041
0051670051
0052330052
0053490053
Conversion
Account Conversion
0041 10000151
0051 10000152
0052 10000153
0053 100001524
I need to update the Account table using the Conversion table by replacing the first 4 digits in the Account table with the Conversion value. For example, the first record should be updated from 0041980041 to 10000151980041.
View Replies
ADVERTISEMENT
Dec 8, 2014
I'm familiar with writing a replace query to replace characters or strings but what I'm trying to do this time is a bit unique to me. I have a string of numbers that will either be 8 or 9 digits in length. The first 1 or 2 digits will be the State code (1-50 hence the discrepancy in number of total digits), then a 2 digit agency code, then the last 5 digits are a producer code. What I need to do is change the 2 digits for the agency part both to 0. So basically characters 6 and 7 if you're counting from the right. To me it feels like I'm trying to do it backwards so I'm having a hard time writing it.
View 12 Replies
View Related
Feb 12, 2013
I have about 150k rows that look like this
SHORT_DESCRIPTION
unable to view
unable to send out some stuff
what is going on
Is it possible for access to query through each field, and tell me the total number of occurence for each word? In this small example, the output would be:
Unable 2
to 2
view 1
send 1
out 1
some 1
stuff 1
what 1
is 1
going 1
on 1
Is this possible?
View 6 Replies
View Related
Apr 25, 2014
I have a list of consumables;
Syringe 50ml
Syringe 20ml
Syringe 5ml
Syringe Cap
White Needle
I want to remove only the number and the ml part from the list, so I would end up with;
Syringe
Syringe
Syringe
Syringe Cap
White Needle
If I use
PHP Code:
Replace([DrugNameVial],"50ml","")Â
I get the desired result for the 50ml syringe size.
I have tried every possible combination of "**ml", "##ml", "Like [0-9]ml all with no success.
How this can be resolved without having to individual enter each syringe size "5ml", "20ml" etc
I can't even just take the text from the right till the first space as this would lead to problems with other consumables in the list.
View 5 Replies
View Related
May 6, 2014
I have this working query:
Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Left([dbo_BACKUP_ACESSOS.LOGIN],255) AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>"ACTIVE DIRECTORY") AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
But Iwant to be able to use a set of data to be used in the Replace Statement, so I create a table to add each string I would like to have replaced by "nothing", and trying to make the replace query to look there in order to find what to replace.I also created a table where I will list the systems that I dont want in the select, so I removed the "ACTIVE DIRECTORY" and replaced by the colum that have the list of system I dont want listed.This is the result:
Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Replace((Left([dbo_BACKUP_ACESSOS.LOGIN],255)),[PREFIXOS_E_SUFIXOS]![Valor],"") AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>[SISTEMAS_EXCLUIDOS]![Sistema]) AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
The thin is that this keeps asking me to enter the parameter value for "PREFIXOS_E_SUFIXOS!Valor" and for "SISTEMAS_EXCLUIDOS!Sistema"
View 6 Replies
View Related
Aug 8, 2007
Hi, can anyone help me do this...
In my products table I have fields such as Bulletpoint1, Bulletpoint2, MainDescription etc.
Each holds part of the product description
In a seperate table I have my description templates with placeholders for the bulletpoints and maindescription. for example....
<ul><li><!-- Bulletpoint1 --></li><li><!-- Bulletpoint2 --><li></ul>
Now, for each record I want to grab the template and replace the placeholders with the real text.
I've got as far as putting this in a query...
Expr1: Replace([DescTemplates]![TemplateData], "<!-- Bulletpoint1 -->", [Products]![Bulletpoint1])
That does the job nicely or the first bulletpoint but where from there? There must be a neater way than having a load of query columns each updating the last. ie.
Expr2: Replace([Expr1], "<--Bulletpoint2 -->", ...
Expr3: Replace([Expr2],...
Or is that the simplest way of doing it?
Cheers!
View 1 Replies
View Related
Apr 28, 2014
I have a very simple query to determine the gender ratio of an associations membership. My SQL code neatly calculates the number of females, viz
SELECT [Mail List].[GENDER], Count([Mail List].[GENDER]) AS TOTAL
FROM [Mail List]
WHERE ((([Mail List].[GENDER])="F"))
GROUP BY [Mail List].GENDER;
However, I wish to present this result as a percentage of total membership.
My main Table has a column titled [Member Name] so my requirement is to produce a calculation of the form "Females"/"Member Name Total" all multipliied by 100.
View 2 Replies
View Related
Feb 2, 2015
I sometimes get a last name with two names not connected by a dash (i.e. Smith-Jones), instead it is entered as "Smith Jones". When I get it from Excel into Access is there a way to replace the space with an underscore in Access? I know I can do it in Excel, but I need that Excel file the way it is for other purposes.
View 2 Replies
View Related
Sep 11, 2012
I have the following in Column A
url.com?valueA
url.com?valueA&valueB
url.com?valueC
I need a function that
If string "ends with" valueA or ValueC, then replace it with ValueD
View 2 Replies
View Related
Mar 25, 2014
I need to loop through text files and replace the new lines following the from, to, copy and subject fields with tabs and place the associated text on the same line as the from, to, copy and subject fields.
I start out with this. See below.
From:
ABC COrp@abc.com
To:
XYZ Inc.@xyz.com
Copy:
Me@abc.com; Myself@abc.com; Irene@xyz.com
Subject:
NOthing much
I need it to be like this.
From:ABC COrp@abc.com
To:XYZ Inc.@xyz.com
Copy:Me@abc.com; Myself@abc.com; Irene@xyz.com
Subject:NOthing much
How can I do this in access?
View 14 Replies
View Related
Mar 8, 2008
As my screen name implies, I am a new to Access and I’m seeking some input to resolve an issue. I have tried numerous queries, read MSDN forums and Access help w/o success. I hope someone would have some ideas.
See ATTACHMENT for screen shot of the following scenario:
Two tables in a one-to-many relationship. The parent table (tblAdmByWard) contains the autonumber primary key (PatientID) and additional fields that will not change while the child table (tblContStayEval) has a corresponding field (i.e. PatientID) to secure referential integrity PLUS additional fields as shown that are evaluated and updated daily. During the update the current date is entered (DateOfReview) along with additional info thus creating a new daily record in the child table.
I would like to query the child table (tblContStayEval) to find and return ONLY the first occurrence (DateOfReview) that the criteria (Variance) was selected…i.e.2/24/2008 as illustrated in the screen shot. This needs to occur for all (PatientID) records but exclude any subsequent and/or similar selections. By “similar” I mean the date will be different for each record in the child table while the data in the other associated fields may or may not be.
I hope I explained this okay as I’m not sure how to go about this. Any ideas or thoughts would be appreciated.
View 2 Replies
View Related
Aug 13, 2013
In the following simplified query, in some months(MonthOf) there were no warranties so I have a null field for AcceptedWarranties:
SELECT qryWarranty.MonthOf, qryWarranty.AcceptedWarranties
FROM qryWarranty;
In order to make my Warranty Trends graph work I need 0s. Other postings show the following statement should get the results I need:
SELECT ISNULL(AcceptedWarranties, 0 ) FROM qryWarranty
But no matter how I try to work this into the original code, the compiler finds reason to reject it.
View 2 Replies
View Related
Jul 31, 2013
I have always relied on Design View to create my queries, usually with great success, but in so doing, I have not become familiar with SQL view, so can't solve this problem:
I have a very simple query whose only function is to sum the payment amounts received for each contract number:
SELECT Payments.[Contract Number], Sum(Payments.[Payment Amount]) AS [SumOfPayment Amount]
FROM Payments
GROUP BY Payments.[Contract Number];
It is then used in subsequent queries to create an accounts payable report. It works great as long as at least one payment has been received on the contract, but if NO payments have ever been received, the Sum is actually null, so that record doesn't get included. I need a way for this query to return zero or currency zero 0.00 for all contracts with no payments yet, so those contracts will be included and the next query in line will see a zero.
In my search, it seems I need to make use of the IsNull function in my SQL (the check expression would be the sum of payments, the replacement value would be zero) OR I need to include a WHEN or WHERE in my SELECT statement above? I just don't know enough about SQL to make this happen .
View 8 Replies
View Related
May 31, 2014
I need to remove 1 final - from my string. Here is my sql.
Quote:
SELECT tblmaintenance.sernu, Right([sernu],10) AS m10digit, InStr([m10digit],"-") AS [Space]
FROM tblmaintenance;
View 3 Replies
View Related
Sep 3, 2005
Hi, i have a query which looks at a table named "attendance" the query includes the fields: "employee number", "absent code" (a 3 letter code), "absent reason" (list of different reasons) "shiftdate" (weekly shift dates in format: DD/MM/YYYY). If possible i would like to be able to promt the user to enter a employee number, then a absent reason (ideally from a list box) and show all occurences (with all the shiftdates) only of that particular absent reason within the last 12 months, and ideally calculates in another field the number of times that employee has been absent for that particular reason for the last 12 months.
Does this make sence?, i know how to promt the user to enter a employee number but not sure how to go about the rest, any help with what seems to be quite a challenging task to me would be excellent.
View 4 Replies
View Related
Dec 13, 2013
I am making a database that has to do with Preventive maintenance. Everything is going good and I came to a problem. There are recurring events. Some are weekly monthly quarterly etc all the way to 5 years. When I put in the task and a start date and the occurrence. So Say I put in a date of a task for today and its set to monthly I want the next record to be 1/13/14 for the task and go all the way up to infinity until a box is checked making it inactive.
View 14 Replies
View Related
Aug 27, 2013
Right now, I'm working with an SQL code of
Code:
"UPDATE Individuals SET [ShareholderOf] = " & Me.CompanyNo & " WHERE [Name] = '" & PerName & "';"
However, doing so will obviously change the "Shareholder of" field into what the user inputs (Me.CompanyNo). What should I use if I want it to ADD the user input rather than REPLACING the old [shareholderof] value?
View 6 Replies
View Related
Mar 15, 2013
I have a seating chart with 33 tables. Under each table there is a number that represents seats taken. I want to do a query that changes the number when it is a certain value. For example, when the value is null I want it to say "Empty", when the value is 10 I want it to say "Full" and when the value is over 10 I want it to say "Over". If the value is 1-9 I want to leave it as is.I know how to accomplish one at a time but not all three together. I've been useing:
Nz([Table#],"Empty")
IIf([Table#]=10,"Full",[Table#])
IIf([Table#]>10,"OVER",[Table#])
[code]....
View 6 Replies
View Related
Apr 24, 2015
I have a table of sales information. Part of my products are kits that are a combination of individual products. (Think of it as a value meal at a fast food restaurant. You can order a hamburger, a soda, and a small french fry separately. But if you get the Value Meal #1 you get all three at a discounted price.)
What I would like to do is make a new table/query that extracts out the kits out of the original sales table and replaces the kit record with records that show the components.
I have attached a file to show as an example of how the records current look and how I would like it to look.
View 8 Replies
View Related
Feb 14, 2014
I am running a query for an apparel manufacturing facility. In my query I have a table called 'OrderForm' which is where the orders are put in. There are more than one type of fabric that can be a part of an apparel item, so as a result, there are multiple fields pulling from the 'FabricType' table. In order to get this to work in my query I created 'SubTables' for the different fabric fields. For example, I have tblFrontfab, tblbackfab, tblsleevefab, and tblcollarfab which are just extra copies of the 'FabricType' table. The actual question is that when I want to replace characters like ,./& in the fabric field but I can't use the replace function. It says that it is too complex to calculate.
I used this notation
Frontfab: Replace([tblFrontfab].[fabric],".","")
and the error was that it is to complex to evaluate.
View 1 Replies
View Related
Apr 21, 2015
My database contains a table that has two fields named 'occurance1' and 'occurance2', both containing a range of different dates. In a form I have in that database, I would like to have a field displaying the total counts for both 'occurance1' and 'occurance2', but only the count for both occurances that have 'Today' as a date. Preferably without having to run yet another query, so if possible I would like that as soon as an occurance that has 'today' as a date is entered in the table, the total count shows correctly in the form.
View 11 Replies
View Related
Apr 15, 2014
I've made a simple form to Login/Logout with radio buttons but the buttons only allow me to push a number as a value, in my case 1 or 2 for Login or Logout.
How would I make an update query to change those numbers to the equivalent text? Or is that not possible in the same field because that is 2 different data types?
View 5 Replies
View Related
Apr 8, 2013
Is it possible for an update query to simply replace all the data in a table's field with the results from a query?
For example, I have Table A with the field Years. This table performs various calculations within based on the field Years.
I would like an update query that simply replaces the Years field data in Table A with a new set of years based on a query's search criteria.
When I run the below query, I get an error saying the query must be updateable- but Im not trying to update the query, rather the Table
I've attached a photo ......
View 1 Replies
View Related
Apr 2, 2014
The company I work for is consolidating regional server space onto a single server. I have relinked all of my tables, but my ~180 queries are still pointing to the old server.
My question: Is there a script or other process that can be executed that will search through all my queries within the database to find the string "dbo_tbl" and replace with "dbo_vwtbl"? I would very much like to avoid taking each individual query to a notepad...
View 3 Replies
View Related
Jul 1, 2015
I have a Table1 served by Form1..It is a list of: UnqID, process, quantity, totaltime(in seconds).I want to click on a record to bring up a filtered Form2 with the chosen record on it.What I want to be able to do is to now split the quantity (and the time) and put these new records back into Table1 and delete the original record
EG
ID1,10,write a report,2400
I want to delete this and replace it with two (or three/four etc) replacements, but still adding up to 10 quantity and 2400 seconds so that the new data could be:
ID2,5,write a report,1200
ID3,5,write a report,1200
My initial thoughts are to create a holding table to:Append filtered data on Form2 to a holding Table1hld (i don't know how to do this) delete data in Table1.then enter the new quantities into a holding Table2 (that I will input myself) and then append (through a series of queries back into Table1).The first problem is how to append (and subsequently delete) the filtered record from Form2 to Table1hld.
View 1 Replies
View Related
Aug 2, 2013
I have a field that contains 12 numbers. I need to replace the middle four numbers with a character so that the entire number is not readable. How do I do that?
View 14 Replies
View Related
