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 Replies


ADVERTISEMENT

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

Queries :: Possible To Query A Field For Special Characters In A String?

Oct 11, 2013

Is it possible to query a field for special characters in a string? I want to find any records that have characters other than A-Z, 0-9

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

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 9 Replies View Related

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

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

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

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

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 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 14 Replies View Related

Strip Last 21 Characters From A String

Sep 21, 2005

I need to be able to strip the last 21 characters from the following strings using a command in a query. Any ideas? I know I need to Instr command but not sure how to do it.

Greg
Impulse Manufacturng

96803-386868-386869-163241
INSP-F96191B-384761-389552-162206
INSP-F96191A-384758-389551-162205
F96191A-384758-389551-162205
F96191B-384761-389552-162206
INSP-97407-389462-389463-164428

View 5 Replies View Related

Want To Know How To Chop The Last 3 Characters Of A String

Nov 24, 2005

I am new to VB and want to know how to chop the last three characters off a string. Am writing dynamic SQL and have to take the AND off the last WHERE clause if needed.

Any help greatly appreciated

View 5 Replies View Related

Removing Characters From A String

Jan 5, 2005

is there a way to do this in VBA in Access I looked at trim but it does not help much

Dim MyString, TrimString
MyString = " <-Trim-> " ' Initialize string.
TrimString = LTrim(MyString) ' TrimString = "<-Trim-> ".
TrimString = RTrim(MyString) ' TrimString = " <-Trim->".
TrimString = LTrim(RTrim(MyString)) ' TrimString = "<-Trim->".
' Using the Trim function alone achieves the same result.
TrimString = Trim(MyString) ' TrimString = "<-Trim->".

I have a select statment setting the data that will be in a combo box
I would like to trim this data...

for example the select statment returns something like
category>subcatgory>field>subfield

so I want to trim it down to be
field>subfield

View 3 Replies View Related

Trimming Off First 7 Characters In A String

Oct 21, 2005

My DB is on SQL Server with an Access adp front end.

SQL uses the Windows NT user name and password for security and also pulls in the user name into a field in every record so we know who has added and modified it.

For one table we are actually using the user name field on the form and in a report for reasons other than security.

The user name is filled in with first the name of the network group, then a slash, then the user name itself. So there are 7 characters I don't really care about.

Is there a way in VBA to remove those first seven characters and only show what comes afterwards? Thanks for the help!

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

Characters Before Searched String?

Dec 9, 2011

Is there a function, similar to mid, except that it will return all characters prior to the searched string instead of after?

View 4 Replies View Related

Parsing The Left And Right Characters Of A String

Aug 19, 2005

I have the following string: NE1220904
I need to remove the left two characters and the right four characters of a string in a query. I have found how to remove one side or the other, but not from both sides. Thanks in advance for the help!

View 6 Replies View Related

Left Three Characters From String Using Query

Sep 8, 2005

In MySQL I can use the LEFT(3, field_name) command, but I essentially want access to pull all records from a table where the the first three digits of a number field match those pre-selected by a form drop down.

This database is in Access 97. Is there a way to compare the first x number of digits using SQL only, or do I need to run each line through code first, and then check it (I really don't want to do that)?


example numbe rin field:

123456789

SQL checks to see if 123 matches value selected on a form.

View 1 Replies View Related

Strip A Filename String Of Any Disallowed Characters

May 8, 2006

I am writing some search results to an excelsheet for reports using the time and date functions to build a filename. I downloaded a StripSpecialCharacters() module, but it doesn't take them all out (only takes ascii above 127).

Does anybody know of another function to do it. searched the archive to no avail.

Thanks in advance

View 5 Replies View Related

Modules & VBA :: Check If String Contains Numeric Characters?

Nov 7, 2013

Is there an easy way in VBA to loop through a recordset and determine if the text string in a text field includes numeric characters?

I'm trying to do something like this:

Code:
Do Until rst.EOF
rst.Edit
If Left(rst!FldText,10) contains any numeric characters Then
rst!FldType = "Mixed"
ElseIF Left(rst!FldText,10) contains "PO" Then
rst!FldType = "PO"
Else
rst!FldType = "Std"
End If
rst.Update

View 2 Replies View Related

Queries :: Replace Nulls With Zeros?

Aug 13, 2013

In the following simplified query, in some months(MonthOf) there were no warranties so I have a null field for AcceptedWarranties:

SELECT qryWarranty.MonthOf, qryWarranty.AcceptedWarranties
FROM qryWarranty;

In order to make my Warranty Trends graph work I need 0s. Other postings show the following statement should get the results I need:

SELECT ISNULL(AcceptedWarranties, 0 ) FROM qryWarranty

But no matter how I try to work this into the original code, the compiler finds reason to reject it.

View 2 Replies View Related

Queries :: Replace Null With Currency Zero?

Jul 31, 2013

I have always relied on Design View to create my queries, usually with great success, but in so doing, I have not become familiar with SQL view, so can't solve this problem:

I have a very simple query whose only function is to sum the payment amounts received for each contract number:

SELECT Payments.[Contract Number], Sum(Payments.[Payment Amount]) AS [SumOfPayment Amount]
FROM Payments
GROUP BY Payments.[Contract Number];

It is then used in subsequent queries to create an accounts payable report. It works great as long as at least one payment has been received on the contract, but if NO payments have ever been received, the Sum is actually null, so that record doesn't get included. I need a way for this query to return zero or currency zero 0.00 for all contracts with no payments yet, so those contracts will be included and the next query in line will see a zero.

In my search, it seems I need to make use of the IsNull function in my SQL (the check expression would be the sum of payments, the replacement value would be zero) OR I need to include a WHEN or WHERE in my SELECT statement above? I just don't know enough about SQL to make this happen .

View 8 Replies View Related

Queries :: Trim And Replace Function?

May 31, 2014

I need to remove 1 final - from my string. Here is my sql.

Quote:

SELECT tblmaintenance.sernu, Right([sernu],10) AS m10digit, InStr([m10digit],"-") AS [Space]
FROM tblmaintenance;

View 3 Replies View Related

Queries :: SQL UPDATE That Adds But Doesn't Replace?

Aug 27, 2013

Right now, I'm working with an SQL code of

Code:
"UPDATE Individuals SET [ShareholderOf] = " & Me.CompanyNo & " WHERE [Name] = '" & PerName & "';"

However, doing so will obviously change the "Shareholder of" field into what the user inputs (Me.CompanyNo). What should I use if I want it to ADD the user input rather than REPLACING the old [shareholderof] value?

View 6 Replies View Related







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