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

Modules & VBA :: Find And Replace Special Characters

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

Converting Alphanumeric Characters (and Spaces) To Numbers

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

Replace Characters

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

Queries :: Replace 2 Characters In String With 0s

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

Modules & VBA :: Renaming Tables With Spaces In Name

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

Modules & VBA :: Remove Spaces From Middle Of A Field

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

Modules & VBA :: Shell Command Not Working With Spaces In File Name

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

Queries :: Make Replace Query To Look In Order To Find What To Replace

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

Modules & VBA :: Replace Null Value

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

Modules & VBA :: Replace Blank Fields With 0

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

Modules & VBA :: How To Replace Some Values From Another Query

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

Modules & VBA :: Edit Replace Via Script Several Changes?

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

Modules & VBA :: Replace Values By DSUM

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

Modules & VBA :: Replace Front End DB With Another Database

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

Modules & VBA :: Replace Batch Number From All Tables

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

Modules & VBA :: How To Cancel Carriage Return And Replace With Tab

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

Modules & VBA :: Replace Embedded Logo Images?

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

Modules & VBA :: Replace Function Using Form Control Value

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

Modules & VBA :: Code To Replace Special Character With Space

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

Modules & VBA :: Replace Hard Code Directory Path With Textbox

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

Modules & VBA :: Cut Off First 6 And Last 2 Characters Of String

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

Modules & VBA :: Replace Line Of Code During Split Form Front End Deployment?

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

Modules & VBA :: Getting Extra Characters In Column

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

Modules & VBA :: Remove Special Characters

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

Modules & VBA :: Blank Out Password Characters?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved