Replace New Lines After Search String With Corresponding Text With A Tab

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 Replies

VBA Code To Search And Replace When String Ends With A Value

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

Replace Space With Underscore In A Text String

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

Queries :: Removing Text From String Using Replace Function And Wildcards

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

Search Through All Access Queries For Text In SQL String

Feb 2, 2012

I need to find a way to search through all of the queries in my Access 2007 database to change the names of more than a few variables. There are close to 900 queries I need to search through many of which may use the variables as criteria, so if I miss even 1 instance I might screw myself completely.

I found this post and it tells me exactly what I need to do. Only problem is the post was written in 2002, and I've followed the below instructions only to have it not work.

As a contractor working predominantly on databases I haven't developed, it can be a daunting task trying to find references to tables, fields or functions within a list of several hundred queries.

The following code is attached to a command button on a simple form listing all queries in the database. The record source for the form is

SELECT DateCreate, Name FROM MSysobjects WHERE Type=5 ORDER BY DateCreate DESC

There is a text box where the user enters the text to search for. The code builds a table containing the SQL string of every query, then searches for the required text, and displays those that match.

Your form also needs a procedure the set the record source back to the default.

There are off-the-shelf applications such as Speed Ferret which perform this sort of function, however some employers are too cheap to purchase them!

Code:
sub cmdFilter_Click()
Dim db As Database
Dim rs As Recordset
Dim rsFilter As Recordset
Dim tdf As TableDef
Dim strSQL As String
Dim strQdf As String

On Error GoTo ErrorHandler

[Code] ....

View 11 Replies View Related

Replace Multiple Parts Of A String?

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></li><li><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], "", [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

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 4 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

Reports :: Find And Replace Text Across Many Text Boxes In Report Design

Jan 30, 2015

Is it possible to use find and replace to modify text in report design - or else is there something else I can do to get the same effect? I have a really useful report and I want to modify it for use with a different dataset. To do that I will need to change the text in over 150 text boxes. I have seven different fields which each occur over 20 times in the control source formulas in the text boxes, because they are used in different ways in different calculations. If this was excel I could do a simple find and replace to change e.g. every reference to fieldname OLD to fieldname NEW, but I cannot see how to do that within access report so am haveing to click on each text box in turn, go to properties and edit the text box .

I am trying to switch to a more general naming system in the modified report so then I will be able to assign data with the required fieldnames for the report within a query. But the report I am starting with has field names based on years 2013, 2014, 2015 etc.I want a quick way to change each reference to those field names to my more generic new field names.

View 2 Replies View Related

Global Search And Replace

Dec 13, 2006

Hi,
My company has renamed a product. I want to find all the records in all the tables in my database that might have the old name, and replace with the new name. What's the easiest way to do this?

I don't really want to have to manually open each table and do a find.

I can write code to go through all the tabledefs and do a find on each one, but the only way I know to do that is by telling it the field name, and field names where the product name might exist vary.

I suppose I can tell it something like
for each TD in tabledefs
for each F in td.fields
{search and replace}
next f
next t

Is this really the best way? I have a program called SpeedFerret, which I thought would do this, but it apparently only searches the table names, not the actual records in the table.

View 4 Replies View Related

Find And Replace Search Giving Either Ascending Or Descending Results

Dec 21, 2014

I have a DB and I have a button that triggers the find and replace. this will search over 100K entries and filter a specific client. The clients record can be 100's of entries.

This all works fine, but its always ascending and I have to scroll to the bottom for the latest record.

I can't set it to show Descending permanently as some employees can't cope with change. so...

I have a checkbox on my form for either Ascending or descending results. Is there any way I can get the find / replace to reference it, Or can I re-sort them after the search.

I've tried putting re-sort code on the 'find' button as lostfocus and mouseup but it doesn't work.

View 6 Replies View Related

Multiple Lines In Text Box

Dec 14, 2004

i'm no noob, but i'm also not the expert, what i'm trying to do is this,
i'm taking multiple string out of an recordset and putting them in one big text box
but the problem is that after every record there has to be an enter in the text box
So for example:

record 1 =
-bla bla bla
-tja tja tja

record 2 =
-waa waa

my result in the text box must be:
-bla bla bla
-tja tja tja
-waa waa

and not (like it is now):
-bla bla bla
-tja tja tja -waa waa

who has a clue? chr(11) & chr(13) do not work, i activated "on enter go to next line"

thx in advance for helping out

View 2 Replies View Related

Importing Multiple Lines Of Text

Aug 27, 2007

Good morning. I am new to coding VBA and need some help if possible. I need to import a Comma Delimited Text file into a MS Access table. The records vary in length and may take up multiple lines of text before the next record. The first field contains the type of record and are all prefixed 1###, with the ### being variable. (1001 - 1100). If the record goes over one line the 2nd line with start with 1000. Here is an example: The records with 1001 continue to the next line with 1000 so you know it continues. The other records are all single lines starting with 1100, 1003, 1004, 1006...1017.

1001,00000000,00000000,00000000,00108888,00537906
1000,00100,"CAJUN MEAT ","CHUB "-0020380
1001,00000000,00000000,00000000,00020492,00130534
1000,00108,"RED BEANS ","CHUB ",-0221510
1001,00000000,00000000,00000000,00222572,00796807
1100,0000000000,0009605419
1003,01,000101,263558519,"QQ380427",5,08,19,107,16,07,58
1003,02,000202,263774367,"QQ380428",4,08,19,107,22,22,12
1004,1," "
1004,2," "
1004,3," "
1004,4," "
1006,00000000,00000000,00000000,00000000
1007,"$ OFF ",0000000000,0000000000,0000000000
1007,"FREE ",0000000000,0000000000,0000000000
1007,"SENIOR 10% ",0000000000,0000000000,0000000000
1007,"50% Police ",0000000000,0000000100,0000000001
1007,"CREW 50% ",0000000000,0000000310,0000000003
1007,"CREW 100% ",0000000000,0000001976,0000000009
1007,"MANAGER ",0000000000,0000000260,0000000003
1007,"BONUS ITEMS ",0000000000,0000000000,0000000000
1008,00107,00001,00065,06074,00347,00290
1009,01,"BREAKFAST SALES ",0000114476,02940,000091
1009,02," SALAD SALES ",0000009093,00233,000007
1009,03,"DRIVE-THRU SALES ",0000142716,03666,000106
1010,0000002646,0000009093,0000000000
1011,000,0000000000,003,0000005208
1015,"NET SALES ","+",0000389258
1015,"TAX ","+",0000023434
1015,"GROSS SALES ","=",0000412692
1015,"RESTAURANT BANK ","+",0000000000
1015,"ADJUSTED GROSS CASH ","=",0000412692
1015,"SEPLINE----------------------"," ",0000000000
1015,"AMOUNT DEPOSITED ","+",0000365609
1015,"GIFT REDEEMED ","+",0000000000
1015,"CREDIT CARD ","+",0000044844
1015,"MISC. INCOME ","-",0000000000
1015,"PETTY CASH ","+",0000000000
1015,"NET SALES ","-",0000389258
1015,"TAX ","-",0000023434
1015,"CASH OVER/SHORT ","=",-000002239
1015,"CASHIER OVER/SHORT ","-",-000001839
1015,"OVER/SHORT RECONCILIATION ","=",-000000400
1015,"SEPLINE----------------------"," ",0000000000
1015,"NET SALES ","+",0000389258
1015,"GIFT CERT. SOLD ","-",0000000000
1015,"OTHER NON-FOOD ","-",0000000000
1015,"NET FOOD SALES ","=",0000389258
1016,1,00000000,00000000,00000000
1016,2,00006103,00036415,00118800
1017,0004339338

I will need to do this weekly for many different files. I appreciate all the help that anyone can provide.

View 4 Replies View Related

General :: Indenting Second And Subsequent Lines In Text Box

May 12, 2013

In a report, I have a text box with variable length text. Can Grow is set to Yes. If the box grows, I would like all the subsequent lines to be indented. Is that possible?

View 2 Replies View Related

Modules & VBA :: Count Number Of Lines In A Text Box

Oct 17, 2014

I have a memo field that feeds to a textbox control on one of my forms. I would like to re-format the textbox using the .Height value based on how many lines appear in it (i.e. it will fit only 3 lines if there are 3, but 5 lines if there are 5, etc.).

Is there any way to count the number of lines that appear in the textbox?

View 3 Replies View Related

Find And Replace Text

Aug 3, 2006

Hi,

I have my main table and one column is a lookup field to another table.

One of the options that was in the dropdown needed to be changed. So I changed the dropdown, but now I need to change all the old entries to reflect this change.

I opened the main table, and thought I could do a find/replace. But it doesn't work. If I do just a 'find' it finds the old text just fine. But if I do the Find/Replace option, it says the 'look for' text was not found. Even tho, I just found it using the find option. If I click on the tab that says find, it will find it again. If I click on the find/replace it can't find it.

any ideas on a way to quickly replace my text in these fileds, without having to go 1 by 1 through each record.

Thanks

View 1 Replies View Related

Search For A String Within A String

Oct 27, 2006

I have a column called CPU_S within a table called workstation that contains sample text like P111 933

I want to use the update command to search the CPU_S column for entries that contain this in there string then add P3 to a column called CPU_N

So far I have the code below but I don't know how to search a column entry for a specific string within a string. Can this be done and how?

UPDATE workstations SET CPU_N = "P4"
WHERE CPU_S = ;

View 3 Replies View Related

Reports :: Replace Text Case Sensitive?

Mar 20, 2014

I'm trying to open a report and highlight certain text. So I have a textbox that takes the original field and replaces certain text (based on user input) in bold red. Here is my formula:

Code:
=Replace([Language],[txtKeyword],"<font color = ""red""><b>" & Upper([txtKeyword]) & "</b></font>")

It works fine, but the only problem is, I don't know how to make it case sensitive. So let's say the user input (txtKeyword) is "contract." If it's the beginning of a sentence, I want the C to remain capitalized, but if the C is not capitalized, I don't want it to show capitalized. Is there a way to do this without making the formula really long?

View 7 Replies View Related

Show Text Box In A Form Based On Character Within A String In Another Text Box

Dec 21, 2012

I have a form where I want a textbox [txtMaxOrdLimit] to be visible only if another text box on the same form [PaNumber] contains the letter D in the string. This is the code I have on the forms On Current property but I'm missing something because textbox [txtMaxOrdLimit] doesn't show on the form at all.

If Me.PaNumber = "*D" Then
Me.txtMaxOrdLimit.Visible = True
Else
Me.txtMaxOrdLimit.Visible = False
End If

View 3 Replies View Related

Search A String...

Mar 25, 2006

I'm trying to create a query that searches the string in a field, for a substring that the user specifies when the query is run. This is the SQL code:

SELECT tblStatement.Date, tblStatement.[Payment Type], tblStatement.Details, tblStatement.[Paid Out], tblStatement.[Paid In]
FROM tblStatement
WHERE (((tblStatement.Details) Like "**"))
ORDER BY tblStatement.Date;

between those two asterixes on the WHERE line, I'd like a string that the user has been prompted for. I've tried this:

WHERE (((tblStatement.Details) Like "*[Enter a String]*"))

But that fails to work, I guess because its in quotations. If I simply had Like [Enter a String], then I get the prompt, but I'll need to enter a string which exactly matches what I am looking for. In other words, I need a query which will search say, "abcdefg" for the user defined input: "abc", which will return that result. I just dont know how to get this to work :s

View 1 Replies View Related

Import Text File / Multiple Lines And Varying Delimiters / Size To One Record

Aug 22, 2012

Using MS 2007, I have a 200 text files exported each day from another application that has two different types of lines (see below). I would like to import each text file in to a database as a single record.

Text file example (text.txt):

R111 WC 8/21/2012 7:00 Doe, John doej 10110110

First Question? Y
Second Question? N
Third Question? Y
...
Seventeenth Question? 10

As you see, I have the first row with multiple fields, but the next rows I have a question and an answer.

I would like to have this data imported as shown in the attachment. Example.zip

Most answers I see are for either multiple lines (same data and sizing). I am not sure how to handle several different lines with that vary in size and delimiters.

View 5 Replies View Related

Queries :: Update Query To Replace Number With Text

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

String Search And Filter

Sep 11, 2006

I have been hunting around to find exactly what I need but cant seem to find it...

what I need is a way to do a string search within a particular field (surname) and then filter by that string?????

Any ideas or point me to the right thread will be much appreciated.

View 1 Replies View Related

Search String For Space

Apr 11, 2006

I have a table containing names. Unfortunately, the first and middle name are in the same field, and not all have middle names. I need to separate them out in a report. Is it possible to search the field and break at the first space?

Any help would be greatly appreciated!
Thanks

View 1 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

File Search To Return Path String

Apr 25, 2007

Hi, I have a command button that opens a pdf file. However i will be distributing the database as a runtime package and need to account for people having different versions of adobe reader so need to search for the filepath string where Acord32.exe is found. This is the code I have but I am stuck! Ay help aprpeciated!

Private Sub Command4_Click()

Dim stAppName As String
Dim stPathName As String
Dim fs As Object

Set fs = Application.FileSearch
With fs
.LookIn = "C:Program Files"
.SearchSubFolders = True
.FileName = "AcroRd32.exe"
.Execute
????? stAppName = .FoundFiles
Set fs = Nothing
End With

'specify path name for version of adobe acrord32.exe
'stAppName = "C:Program FilesAdobeReader 8.0ReaderAcroRd32.exe "
stPathName = GetIniSetting("C:WINDOWSSSI_DL3_PROGRS.ini", "DIR", "REPORT_FILELOCATION") & "HOOF.pdf"
Shell stAppName & stPathName, vbMaximizedFocus

End Sub

View 1 Replies View Related