I'm currently migrating a lot of Excel processing to Access and really enjoying the transition. I am, however, having big problems with the Replace function in Access.....
In Excel I use a macro to relace anything in a cell contents that follows a space with nothing......i.e. replace " *" with ""
The fields that i need to do this on do vary in length- they are product descriptors.....e.g
abcdefg 123456
xzy 987
The replace feature (ctrl + h) in table view will do this correctly if "any part of cell" is specified in "match" selection but I cannot seem to replicate this in a query.
I'd be grateful for any suggestions here. I thought about exporting the fields in the columns to Excel and doing the replace there, but I can't get it to work. Similarly I tried writing some VBA for this, but again no joy!
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!
I'm trying to help someone with some text functions in Access, and I have used the Replace function to strip out spaces in a postcode. I created a dummy database in Access 2003 but in 2000 format since she is still on Access 2000. However, she is getting the above message. Incidentally, I don't get the message when I run it in Access 2000.
She has checked her references and has no missing ones. She has:
Visual Basic for Applications Microsoft Access 9.0 Object Library OLE Automation Microsoft DAO 3.6 Object Library Microsoft ActiveX Data Objects 2.5 Library
I have attached the DB - I'd be really grateful if someone could try opening it in Access 2000 to see if they get the same error.
I am trying to use the string value from a form control as the criteria for a query. I first need to replace the "," in the string with "AND".
So far I have a module using the Replace function, but it doesn't seem to be working. I am not sure I can reference the string inside the form control directly....if I can, I might have a syntax error.
Here is my code so far:
Dim result As String result = Replace("Forms!Processing!Dataset_Acreage_Query", ",", "AND")
I am not experienced and having trouble finding the search terms to answer this question.
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.
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...
Is there a copy and replace existing records function in access?
I.e. I would want to copy records from one table to another (with same structure) and replace similar records with in original table in the new records (which have minor ammendments made).
My company recently upgraded our MS Office from 2007 to 2010 (except for Access).
Previously, when I had Access 2007 and Outlook 2007, I had a process that generated 50+ dynamic emails from an Outlook template file (.oft).
The code would loop through a listbox and replace the template's default text to a string of text specific to the selection in the listbox by utilizing the Replace() function on the MailItem .HTMLBody.
Since the upgrade to Outlook 2010, the code is able to run, however, the Replace() function is no longer working; Instead, each email that is generated maintains the template's default text.
The only thing that is not working is the Replace() function, all other aspects of the code work fine.
I've provided a simplified version of the code below:
Dim myOlApp As Outlook.Application Dim objMailMessage As MailItem Dim stBody As String Set myOlApp = Outlook.Application Set objMailMessage = myOlApp.CreateItemFromTemplate("C:UsersDesktop emplate.oft")
[Code] .....
I've recreated the template file in Outlook 2010, thinking that the template created with Outlook 2007 would be the culprit, but to no avail.
What could have changed from Outlook 2007 to Outlook 2010 that would render my previously valid code ineffective?
Are there certain references I need to enable in both Access and Outlook to allow VBA in Access modify the content in an Outlook email?
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"
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total ----------------------- 2 | 1234 | 80.00 ------------------------ 1 | 43526 | 20.00 ------------------------ > | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer) Dim SPSelect As String SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE" SPSelect = SPSelect & " ItemID = '" & ItemID SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
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
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
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?
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?
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?
