find and replace

  • Thread starter Thread starter Edward
  • Start date Start date
E

Edward

Hello. Newbie. I want to search a 15 character column for a number pattern
in the first 6 characters of the field and replace those characters with a
new pattern. Can someone help me out with the proper syntax?

Thanks, Edward
 
About using wildcard characters
You use wildcard characters as placeholders for other characters when you
are specifying a value you want to find and you:

a.. Know only part of the value.
b.. Want to find values that start with a specific letter or match a
certain pattern.
Wildcard characters are meant to be used with fields that have the Text data
type. You can sometimes use them successfully with other data types, such as
dates, if you don't use the Microsoft Windows Control Panel to change the
regional settings for these data types.

If you are using Microsoft Jet database engine SQL in a Microsoft Access
database, you can use the following wildcard characters in queries and
expressions to find such things as field values, records, or file names. You
can also use these characters in the Find and Replace dialog boxes in an
Access database or a Microsoft Access project.

Character Description Example
* Matches any number of characters. It can be used as the first or
last character in the character string. wh* finds what, white, and why
? Matches any single alphabetic character. B?ll finds ball, bell, and
bill
[ ] Matches any single character within the brackets. B[ae]ll finds
ball and bell but not bill
! Matches any character not in the brackets. b[!ae]ll finds bill and
bull but not ball or bell
- Matches any one of a range of characters. You must specify the range
in ascending order (A to Z, not Z to A). b[a-c]d finds bad, bbd, and bcd
# Matches any single numeric character. 1#3 finds 103, 113, 123

To find such things as field values, records, or file names in a Microsoft
Access project, or in a Microsoft Access database that uses Microsoft SQL
Server-compatible syntax, use the following ANSI-92 wildcards.

Note If you are using the Find and Replace dialog boxes in an Access
project or in an Access database that uses Microsoft SQL Server-compatible
syntax, you must use the Microsoft Jet SQL wildcards.

Character Description Example
% Matches any number of characters. It can be used as the first or
last character in the character string. wh% finds what, white, and why
_ Matches any single alphabetic character. B_ll finds ball, bell, and
bill
[ ] Matches any single character within the brackets. B[ae]ll finds
ball and bell but not bill
^ Matches any character not in the brackets. b[^ae]ll finds bill and
bull but not ball or bell
- Matches any one of a range of characters. You must specify the range
in ascending order (A to Z, not Z to A). b[a-c]d finds bad, bbd, and bcd

Notes

a.. When using wildcard characters to search for other wildcard characters
such as an asterisk (*), question mark (?), number sign (#), opening bracket
([), or hyphen (-), you must enclose the item you're searching for in
brackets. If you're searching for an exclamation point (!) or a closing
bracket (]), you don't need to enclose it in brackets.
For example, to search for a question mark, type [?] in the Find dialog
box. If you're searching for a hyphen and other characters simultaneously,
place the hyphen before or after all the other characters inside the
brackets. (However, if you have an exclamation point (!) after the opening
bracket, place the hyphen after the exclamation point.)

b.. You can't search for the opening and closing brackets ([ ]) together
because Microsoft Access interprets this combination as a zero-length
string. You must enclose the opening and closing brackets in brackets
([[ ]]).
 
Example With one table
UPDATE Movimientos SET Movimientos.Debe = "write the new value here"
WHERE (((Movimientos.Debe) Like "WRITE WHAT ARE YOU LOKIN FOR"));
 
Thanks for this and your previous post.

I will test your suggestion out but the query below looks like it would
have the same result as using the "Edit, Find, Find and Replace" feature.

ie: if I have a string of numbers 123451234512345
and I want to replace only the first 3 characters (123) with "abc" then
it looks like to me the query below and the "find and replace" above
would find all instances of the pattern 123 and replace them with abc...

I need to find only the first x number of characters that match a pattern
and update (replace) that pattern with a new pattern..

Thanks, Edward
 
Hello. Newbie. I want to search a 15 character column for a number pattern
in the first 6 characters of the field and replace those characters with a
new pattern. Can someone help me out with the proper syntax?

Thanks, Edward
Click to expand...

The need to do this suggests that this field violates first normal
form: fields should be atomic!

The simplest way to do this is to use the Replace() function, but it
is only available in some versions of Access. For AccessXP and later
use a criterion on the field of

LIKE "123456*"

and update to

Replace([fieldname], "123456", "654321")

(or whatever).

In A2000 the Replace() function doesn't work in queries; you'll need
to write a dumb little wrapper function in a Module:

Public Function QReplace(strIn As String, strOld As String, _
strNew As String) As String
QReplace = Replace(strIn, strOld, strNew)
End Function

and use QReplace as above.

In older versions... post back, there is no Replace but it can be done
with a bit more work.
 
Ok i understand now, you must to create the way tha i made it is write code
in visual basic.

Create a comand button and in the event click you must to write code like
this.
Dim Strtexttoupdate,Strupdatedtext as string
Dim SrtSql1, STrsql2 as string
Create 2 Recordsets 1 to get the text that you want to replace and other
with a update SQl query that made all you want more or less is:
Dim RstText1 as adodb.recordset
dime rsttext2 as adodb.recordset

set rsttext1 = new adodb.recordset
set rsttext2 = new adodb.recordset

Well then open the recordset 1
rsttext1.open (strsql),currentproject.connection,

etc... this is the way that i made it i don't know other way.
 
Back
Top