By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

Find and replace in acces query

P: 19
I have a table with a single column that contains various data.
Among that data, certain rows contain characters that need to be replaced.
ex: order number: 8651124563357 (the numbers are different for every row)
I need to find all the rows that contain "order number: "and replace 6 characters starting with the 19th:

ex: order number: 86511******57

How do i build that expression?

Thank you
Jul 24 '12 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,315
You can use the Replace function. Or you can use a combination of the Left and Right functions and concatenate it.
Jul 24 '12 #2

P: 19
the data in the table's column looks like this:

ColumnHeader
00:39:43 <- END
08:21:19 OPERATOR DOOR OPENED
08:21:30 OPERATOR DOOR CLOSED
08:21:43 OPERATOR DOOR OPENED
08:21:45 OPERATOR DOOR CLOSED
08:21:58 -> START
08:21:58 order number: 8651124563357

i need the query to check every row and if the row contains "order number :" in it, concatenate only part of the number (first six and last 2).

I don't know how to define the search to use wild cards. I thought of using a iif function with the left and right nested functions, but like i said i don't know how to write the wild cards in the search string and i'm thinking that the replace function would be more suitable for the operation.
Jul 25 '12 #3

twinnyfo
Expert Mod 2.5K+
P: 3,037
Kan09,

Is this a one time query or something that will have to run frequently?

Either way, one option would be to build a VBA sub to create a recordset, then cycle through the records, check the contents with Like ("*order number*"). If it meets the criteria, update the record with a concatenation of the order number with the masking. One key would b that all of the order numbers would have to be he same number of characters.

It is an interesting and unorthodox table that you have, in terms of the typees of data you have in it, which also begs the question why the masking of the order number after the fact? You should either mask it when it goes in, or only maskit when it is on a form.

Perhaps more explanation is required before we can provide any further guidance.
Jul 25 '12 #4

100+
P: 332
This should get you started:
Expand|Select|Wrap|Line Numbers
  1. SELECT tmp_tbl.columnHeader, InStr([columnHeader],"r:") AS loc, 
  2. Left([columnheader],[loc]+2) & Mid([columnheader],
  3. [loc]+3,6) & "******" & Right([columnheader],2) AS target
  4. FROM tmp_tbl
  5. WHERE (((tmp_tbl.columnHeader) ALike "%Order number%"));
  6.  
You may need to replace the % character with * in the WHERE clause.
Jul 25 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.