Modules & VBA :: How To Replace Characters With Spaces
Jun 26, 2014
I'm working on an eBay uploader access program for Ebay's File Manager
Our description contains line feeds and carriage returns. I have very little knowledge of Vba, and I can t find any tutorials on how to replace the characters with spaces on how to program this.
The table is called tblCustomer
The field is called *Description
I'm using access 2007
View Replies
ADVERTISEMENT
Dec 18, 2013
I have a table called: "tb_special_characters" with a field name [character]. In this table are values that I would like to remove from fields in another table ("tb_data", field name [Title]
The values in the "tb_special_characters" are thinks like
.
@
}
I'd like to run a query on tb_data.title that would replace any matching characters in tb_special_characters.character with nothing (I remove and close any spaces).
View 2 Replies
View Related
Jun 21, 2007
In working with a database, I need to convert 39,000 text fields (i.e.: 6002.2 , 2723 , 6004.55 (Notice the spaces)(commas denote next line)) to number fields. The number of spaces is random, so I can't say "cut off the last n number of characters." There may also be characters that I'm not seeing. I'm not too keen on going through it by hand, and I have access to OpenOffice.org Calc (for those who don't know, it's basically a free version of Excel) so I can pull it into there and convert it, but it still won't get rid of the spaces though. Any ideas?
View 13 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
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
Sep 30, 2013
I am developing code to trawl through the tables in a large number of databases that I am working on for a client and rename tables that have spaces in the name.
I tried using the following but get a 7874 error when the new table cannot be found.
DoCmd.Rename strExistingName, acTable, strNewName
I then tried copying the table using the following but get the same error message.
DoCmd.CopyObject, strExistingName, acTable, strNewName
What I need to do to rename a table with spaces in it?
View 3 Replies
View Related
Jun 9, 2015
I'm trying to improve the dialer that i place on microsoft access forms. basically there's a command button that dials the number that is in a text box, but we have a new job that's just come in that requires numbers to be copied and pasted into the text box. these numbers have a space between the dialing code and the telephone number and i need to make the dialer check for a space and remove it before dialing.
Here's the code for the dialing buttons:-
Private Sub cmd_Dial_Click()
On Error GoTo Err_cmd_dial_Click
CT.MakeCall "8" & txt_telephone.Value & "#", "", False, "", "", False
[code]...
View 5 Replies
View Related
Oct 21, 2013
I am in trouble with the shell ocmmand,i have a text box "ExcelPath Location" in which there is a path of excel file i am using below code to open the excel file but it gives an error:
Code:
Private Sub Command11_Click()
Dim str As String
str = ExcelPathLocation.Value
strPath = Dir(str)
Shell "excel.exe" & """" & strPath & """", vbNormalFocus
End Sub
the value of text box is
O:QA FilesQC ReportingPending ReviewB329129)419479_BoxPort_RAMANDEEP BRAR_(10192013.xlsm
it gives run time error : 53 file not found.
View 3 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
Nov 10, 2014
Is there a code that will replace the empty fields by shifting the bottom fields up for access? I am planning to write a code that will delete unnecessary items in my database, however, that will cause the database to have "holes" in it.
View 4 Replies
View Related
Mar 24, 2014
I try to go throgh all columns and replace all blank fields with 0.Somehow this doesn't work:
Code:
Sub TEST()
Dim str As String
Dim rs As DAO.Recordset
Dim fld As DAO.Field
[code]...
View 7 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
Sep 21, 2014
Within my table 'Contacts' I have the field 'City' which as you would expect contains the city.
The data in the City field which is sourced from the web can often merge two regional municipalities into 1 and I know through digging in behind the data what the correct answer should be.
I am looking to accomplish a list of edit replaces that takes 30 + edits and automates.
Change "ClevelandAkron" to "Cleveland"
Change "Dublin 2" to "Dublin"
Change "Greensboro/Winston-Salem" to "Greensboro"
etc. etc.
There are 10,000 plus entries in the table of which many are correct.
View 2 Replies
View Related
Dec 16, 2013
I want to replace some values in a TNS_QUERY with DSUM. The error message tells me I have to use an updateable table.
How can I avoid to create a query and replace the values directly in the original table TNS? I just group on the original table.
Code:
Public Sub TNS_QUERY()
Dim strSQL As String
Dim x As Double
Dim qdf As QueryDef
strSQL = "SELECT TNS.[Division],TNS.[Customer_Split],SUM([TOTAL_NET_SALES])
[Code] ....
View 2 Replies
View Related
Sep 23, 2014
I have a split database. When the front end db is updated, i am attempting to delete the end users copy and replace with the updated version.
I have gotten as far as being able to open Database A, Open Database B with A and Close Database A, but it will not let me delete Database A from B because the procedure originated from Database A.
View 11 Replies
View Related
Jan 23, 2015
I have 4 tables with a field that holds a "batch number" there can be many batch numbers in these fields.I want if possible to replace a batch number from all my tables that contains that data and replace it with another batch number in one go. is a macro the only way to do this.I would like a macro that asks me the batch number to replace and then asks me the number to replace it.
View 14 Replies
View Related
Jul 30, 2015
How i can cancel the a carriage return and replace with a tab or stop it moving down my subform and make it move the the next field instead of going to a new line.
I am using the Keydown but don't know how to cancel it, it just bring an message but it stops it moving to a new record,
Code:
Private Sub Barcode_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
MsgBox "rtn pressed"
End If
End Sub
End If
View 5 Replies
View Related
Feb 13, 2014
I need to replace the embedded image on a large number of reports and forms. I'm looking for a way to loop through all of the controls in my forms and reports and if the control is an image, replace the embedded image with a new one. Can this be done using vba?
View 5 Replies
View Related
Jun 11, 2014
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.
View 1 Replies
View Related
Jun 12, 2015
We have a spreadsheet that i upload onto a table, i use on of the fields to build a file path but if there is a special character it causes problems,i need to replace the below with spaces if they are in my field called path
< (less than)
> (greater than)
: (colon)
" (double quote)
/ (forward slash)
(backslash)
| (vertical bar or pipe)
? (question mark)
* (asterisk)
. (full stop)
, (commer)
how i can do this in some sort of update query.
View 1 Replies
View Related
Jun 17, 2013
How do I direct the ZipFilePath and ZipFolderPath to use a directory I define in a textbox in a form? Instead of hard code like it is now?
Option Compare Database
Option Explicit
Public Sub NewZip(ZipFilePath)
On Error GoTo Err_NewZip
Dim lngFile As Long
lngFile = FreeFile
[Code] ......
View 2 Replies
View Related
Mar 24, 2014
I would like to cut off the first 6 and last 2 Charaters in an after update event but not sure how, I cannot use mid as the length of the string may change but never the first 6 or last 2, can some one show me how it's done ...
View 4 Replies
View Related
Aug 11, 2015
I have a database that I've split and have deployed to 7 persons.
The thing is, I have three (3) forms:
Form 1
Form 2
Form 3
...that require changing a portion of a line in each form's respective VBA codes. I need to change the directories to where an excel workbook is stored after exporting, and another is where same excel is moved to after being uploaded.
At the moment, during deployment I have to manually change the directories.
I was thinking of maybe a pop-up form with four (4) fields that allows the user to select his/her name at the start-up of their frontend app, and paste the directories in the other text boxes provided.
This information will then be used to replace the directories in the VBA.
If I were to make changes and have to redeploy, I would have to do everything manually again.
View 6 Replies
View Related
Aug 16, 2014
Copying a column from one record to another. Code is:
Code:
Set fld = Records.Fields("violationLocalOrdinanceNumber")
If Len(fld & "") > 0 Then
rst!LOR_NB = Nz(Records!violationLocalOrdinanceNumber, "")
Debug.Print ("LOR NB:" & rst!LOR_NB & "." & " len:" & Len(Nz(Records!violationLocalOrdinanceNumber, "")) & " len new:" & Len(rst!LOR_NB))
End If
Some of the output I'm getting is:
LOR NB:8.08(5) . len:7 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:7.08(5)(A) . len:10 len new:25
LOR NB:7.08(5)(A) . len:10 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:8.08(5) . len:7 len new:25
No matter what the original string length is, something is adding extra spaces and forcing it to 25 in the new record. This is the only field I've been able to identify with this issue. Column definition is char(25), no indexes or anything special that I can tell.
Tried adding a left() function call after the assignment but that didn't work either.
View 4 Replies
View Related
Feb 25, 2015
I have a Memo field that is used to be a description of a document. I don't know what the user will put in (could be anything), but it is eventually passed in a string to a query, etc.I discovered during testing that if an apostrophe is entered (Ex: This document explains how to deal with Joe's pleasant disposition.), it breaks my code. I then realized that all reserved words and characters would cause this problem.
I can get around this one just by using Replace(str, "'", "''"). However, I know there are others that will cause problems, and I don't want to end up with a string of replace statements just to fix them.
View 2 Replies
View Related
Oct 28, 2013
I am currently using
Code:
Private Sub Form_Open(Cancel As Integer)
Const cstrPassWord As String = "open"
Dim strPassWord As String
strPassWord = InputBox("Password:")
If Not strPassWord = cstrPassWord Then
Cancel = True
End If
End Sub
to password protect a form within my database. it is working great however i was wondering if there is any way to make the characters appear as **** when entering the password?
View 9 Replies
View Related