Replace In Query
Dec 14, 2006
Hi Guys,
Need a little bit of help, I have a report I pull out of an inhouse system with sales, shortages etc. I want to use a query to alter the formatting slightly and append it in to a different table. Most of this i can do fine, except for one thing.
Two of the sales fields in my report I'm uploading come out in the following text format:
19.0PC
27.0PC
45.0PC
etc...
I need to change this before appending as its going in to a number field, all i need to do is lose the PC, e.g.
19.0
27.0
45.0
etc...
I can do this manually, it's a simple find and replace in excel before uploading, gowever I want to automate this as much as possible. Looking around I'm pretty sure it can be done in a query using the REPLACE function but I'm unsure on the formats to use.
The only other thing is once its converted even though it will visually be a numbe (e.g. 45.0) it will still technically be a text field, will this prove a problem on the appending?
Many Thanks
Paul
View Replies
ADVERTISEMENT
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 14, 2007
Hi, i have a table of data, with a user name, and a task.
Each user is assigned many tasks, i and i would like to create a query that replaces all instances of a certain name with another name. How would i do that?
Thanks
Sean
View 1 Replies
View Related
Jan 19, 2008
Can the replace function be used in a query?
I have used this
=Replace(Format([SITELATSEC],"00.0"),".",",") in a report and it works fine.
I have the following expression in a query that is output to a report.
LAT: "Latitude: " & [SITELATDEG] & "º " & [SITELATMIN] & "' " & [SITELATSEC] & """ "
I need to be able to display [SITELATSEC] with a comma instead of a decimal point. I tried
LAT: "Latitude: " & [SITELATDEG] & "º " & [SITELATMIN] & "' " & =Replace(Format([SITELATSEC]00.0"),".",",") & """ "
and got an error message about too many commas.
On a more general note: Has anyone ever actually finished a database - ie got to the point where no changes needed to be made? 18 months after this started, and I'm still tryng to accomodate what my manager wants!
View 3 Replies
View Related
Jul 6, 2005
Is there a way to edit/replace tables in a query after you've copied the query? Basically, I have created a query and would like to copy the quesry. However, I would like to easily change some of the tables within the query. Is there a way to edit/replace the tables?
Thanks
View 2 Replies
View Related
Jan 9, 2006
Hello.
I have a simple crosstab query, which is designed to give a monthly total (numeric) and a yearly total for the value of orders placed by a number of customers.
The query is based on another query which simply extracts order records for a particular year.
For some customers, certain months will be blank as there were no orders placed.
I would like to use the crosstab query on a report, and I would like to replace the blanks with zeros.
I have searched for examples of query expressions using the nz function and attempted to apply some. I have, however, had no success in converting the blank monthly totals to zeros.
Can anyone help me achieve this?
The query is as follows :
TRANSFORM Sum(AmountQueryLastYear.SumOfAmount) AS SumOfSumOfAmount
SELECT AmountQueryLastYear.DeliveryName, Sum(AmountQueryLastYear.SumOfAmount) AS [Total Of SumOfAmount]
FROM AmountQueryLastYear
GROUP BY AmountQueryLastYear.DeliveryName
PIVOT Format([ShipmentDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
An example of expressions I have tried is :
Expr1: NZ([SumOfAmount],0)
In it's own column in the query design grid.
View 7 Replies
View Related
Dec 3, 2007
ne1 help me with a find and replace query or macro. What I'm trying to do is shorten the path to external PDF files after I inport them into field TxtPDFPath.
Example; after inport of files path reads
c:w2kdatapdffiles2005somepdf.pdf
c:w2kdatapdffiles2005januarysomeother.pdf
c:w2kdatapdffiles2006another.pdf
I would like to shorten all imported paths to
pdffileswhatever
Example;
pdffiles2005somepdf.pdf
pdffiles2005januarysomeother.pdf
pdffiles2006another.pdf
and so on
View 13 Replies
View Related
Dec 6, 2013
I would like to replace missing TRP information from Query LF with average TRP information coming from Query LF_Average.
Somehow it doesn't work.
Code:
Public Sub Replace()
Dim strSQL7 As String
Dim qdf7 As QueryDef
strSQL7 = "SELECT LF_QUERY.*, LF_AVERAGE.Average_TRP_EUR " & _
[Code] .....
View 14 Replies
View Related
May 16, 2012
How do you use a query for the find and replace function???
View 7 Replies
View Related
Oct 31, 2004
I'm having problems with an assignment in an online course I am taking.
The assignment is to create one Update Query to find & remove typos in a field. Specifically, the typos are multiple f's & g's embedded in a field.
I created an Update Query that finds the typos & removes just the f's. I'm having trouble with the syntax to also remove the g's.
This is what I have so far that works great.
Update: Replace([Field2],"fff","")
Criteria: Like “*fff*”
or: Like “*ggg*”
The Query finds the records that contain both the "fff" & the "ggg" typos but I'm having trouble with Syntax for removing both.
Does anyone know what I have to change in the "Update:" line to include removing the g's?
I know I could easily create 2 Querys to remove the f's then the g's but the assignment requires only 1 Query to remove both.
Thanks,
Yaani-Mai
View 5 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
Feb 27, 2015
We have a field of 7m records of varying length, some of which are numbers, some just letters, some alphanumeric, and most which include a dash, space or some sort of punctuation mark.
We need to replace all letters with "L", then replace all digits with "@".
We can replace characters individually using this update query:
Replace([Ref Digit Or Letter],"A","L")
But would like to use wildcard searches to resolve this, something like (these don't work!)...
Replace([Ref Digit Or Letter],"A-Z","L")
Replace([Ref Digit Or Letter],"LIKE [*A-Z*]","L")
View 13 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
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
Oct 23, 2007
I have a query that run from Two joined tables. All the selected data are correct. However, there is some fields that are blank. I'd like to have my query to run in a way that if it blank, it will replace with 0 (zero). Can someone help? is this possible..?
View 7 Replies
View Related
Dec 5, 2005
How do I replace vbcrlf in a string with "NN"
I have tried putting vbcrlf and "NN" as parameters into this but it doesn't work (maybe it can't see vbcrlf as a separate word!)
Function ReplaceWord(strText As String, _
strFind As String, _
strReplace As String) As String
' This function searches a string for a word and replaces it.
' You can use a wildcard mask to specify the search string.
Dim astrText() As String
Dim lngCount As Long
' Split the string at specified delimiter.
astrText = Split(strText)
' Loop through array, performing comparison
' against wildcard mask.
For lngCount = LBound(astrText) To UBound(astrText)
If astrText(lngCount) Like strFind Then
' If array element satisfies wildcard search,
' replace it.
astrText(lngCount) = strReplace
End If
Next
' Join string, using same delimiter.
ReplaceWord = Join(astrText)
End Function
View 1 Replies
View Related
Aug 21, 2007
I have some database, I need to replace empty cells one column with some data.
But Access does not do it.
Any suggestions?
View 1 Replies
View Related
Jul 17, 2007
is there any simple way to find and replace wildcard character- I have a large data table with text as sample below- I would like to remove |00
2901-0206|00
Thanks
View 1 Replies
View Related
Jun 23, 2005
i have a table that has a column containing a letter reference a / and then another reference ( EG. AAA/A56457A, ght/6577654ghf, etc ) and i want to replace the / with a _
whats the easest and quickest way to do this as an expression
best regards
View 1 Replies
View Related
May 18, 2007
Hi,
I am currently working on a project where i need to try and compare a list of organisation names held by my organisation and another organisation (around 20000 records each).
To take into account differences in how the organisation names may have been entered I'm wanting to strip out things like ltd, limited, company, co, &, and , and so on. I've tried using IIf and replace which works ok if the organisation name only contains one of the substrings I want to replace but not if contains more than one.
How do i go about doing multiple replaces in the same string?
View 4 Replies
View Related
Oct 23, 2007
I am trying to use an update query to modify some text in a specific field using the REPLACE function without much luck!!!
I have a field called "Option" and I want to replace the word "Metallic" with "Paint" and the word "Electric" with "Windows".
Could someone please point me in the right direction.
Thanks in anticipation.
View 7 Replies
View Related
May 1, 2008
Hi, i am looking to replace characters in a field using a query, I can use the replace to replace them all but that aint what I want e.g.
I want this: ~~test~test~~~~~test~~
To become this: test~test~test
I can use replace and get this testtesttest, but I need the ~ placed back between each word.
Any help appreciated, thanks:confused:
View 7 Replies
View Related
Jan 22, 2005
We do alot of find and replace in our main form (CTRL+F). The glitch is that once the pop-up box for "Find and Replace" appears, we almost always have to change the search setting from "Whole Field" to "Any Part of Field". Is there a way to set the default to "Any Part of Field" instead so that we don't have to change the setting every time?
View 3 Replies
View Related
May 27, 2005
Is it possible to change the background color on the Find and Replace dialog box that access provides?
I open it with this code:
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
View 1 Replies
View Related
Feb 20, 2006
I have a query where I make a calculation. In some cases this calculation results in a division by zero. The field will then show #Fout (#Error in english?). I want to replace this message with something else, like a dash for example.
Can someone tell me how?
Thanks.
View 4 Replies
View Related
