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

Editing question: locating a charter within a string!

P: n/a
Hi:

Hi:

I have a linked query, which includes a specially constructed Key from a data base, in the
form: 25-1-1, although the fields may be any number of digits. For purposes of discussion,
call this field KEY

I need to select a field from another record and substitute the value of that field for
the first n digits of the KEY above. Call that field Newfield.

I was planning on doing a: "newfield&right(KEY,find(KEY,"-"))" which would, in theory
strip out the first n digits of the KEY field and substitute the new field. I just cant
locate a command in the macro set for Access that returns the number of characters that a
specific value is into the string, which is what I need.

I want the following result:
Newfield = 2343
Key = 25-1-1
Result should be 2343-1-1

Believe me, this isn't the way I would like to deal with it but i am maintaining a system
with massive spaghetti code, and have no time to rewrite it!!

Help appreciated

Best

John Baker
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
John Baker wrote:
Hi:

Hi:

I have a linked query, which includes a specially constructed Key from a data base, in the
form: 25-1-1, although the fields may be any number of digits. For purposes of discussion,
call this field KEY

I need to select a field from another record and substitute the value of that field for
the first n digits of the KEY above. Call that field Newfield.

I was planning on doing a: "newfield&right(KEY,find(KEY,"-"))" which would, in theory
strip out the first n digits of the KEY field and substitute the new field. I just cant
locate a command in the macro set for Access that returns the number of characters that a
specific value is into the string, which is what I need.

I want the following result:
Newfield = 2343
Key = 25-1-1
Result should be 2343-1-1

Believe me, this isn't the way I would like to deal with it but i am maintaining a system
with massive spaghetti code, and have no time to rewrite it!!

Help appreciated

Best

John Baker


Look up Instr in Help.

Nov 13 '05 #2

P: n/a
[newfield] & Right([TheKey],Len([TheKey])-(InStr(1,[TheKey],"-")-1))

- Jim

On Mon, 05 Jul 2004 16:51:10 GMT, John Baker <Ba******@Verizon.net>
wrote:
Hi:

Hi:

I have a linked query, which includes a specially constructed Key from a data base, in the
form: 25-1-1, although the fields may be any number of digits. For purposes of discussion,
call this field KEY

I need to select a field from another record and substitute the value of that field for
the first n digits of the KEY above. Call that field Newfield.

I was planning on doing a: "newfield&right(KEY,find(KEY,"-"))" which would, in theory
strip out the first n digits of the KEY field and substitute the new field. I just cant
locate a command in the macro set for Access that returns the number of characters that a
specific value is into the string, which is what I need.

I want the following result:
Newfield = 2343
Key = 25-1-1
Result should be 2343-1-1

Believe me, this isn't the way I would like to deal with it but i am maintaining a system
with massive spaghetti code, and have no time to rewrite it!!

Help appreciated

Best

John Baker


Nov 13 '05 #3

P: n/a
Bruce:

Thanks.

I had looked it up before I wrote the note, and found no joy in the help file at all! The
fact that the examples were based on oriental characters didn't help to clarify the
situation at all. All I need is the command and the parameters. ONce I have that I should
(fingers crossed) be ok.

Best

John

Bruce Dodds <br********@comcast.net> wrote:
John Baker wrote:
Hi:

Hi:

I have a linked query, which includes a specially constructed Key from a data base, in the
form: 25-1-1, although the fields may be any number of digits. For purposes of discussion,
call this field KEY

I need to select a field from another record and substitute the value of that field for
the first n digits of the KEY above. Call that field Newfield.

I was planning on doing a: "newfield&right(KEY,find(KEY,"-"))" which would, in theory
strip out the first n digits of the KEY field and substitute the new field. I just cant
locate a command in the macro set for Access that returns the number of characters that a
specific value is into the string, which is what I need.

I want the following result:
Newfield = 2343
Key = 25-1-1
Result should be 2343-1-1

Believe me, this isn't the way I would like to deal with it but i am maintaining a system
with massive spaghetti code, and have no time to rewrite it!!

Help appreciated

Best

John Baker


Look up Instr in Help.


Nov 13 '05 #4

P: n/a
Jim:

Thanks a lot.

John

Ji****@NOTdatacentricsolutions.com (Jim Allensworth) wrote:
[newfield] & Right([TheKey],Len([TheKey])-(InStr(1,[TheKey],"-")-1))

- Jim

On Mon, 05 Jul 2004 16:51:10 GMT, John Baker <Ba******@Verizon.net>
wrote:
Hi:

Hi:

I have a linked query, which includes a specially constructed Key from a data base, in the
form: 25-1-1, although the fields may be any number of digits. For purposes of discussion,
call this field KEY

I need to select a field from another record and substitute the value of that field for
the first n digits of the KEY above. Call that field Newfield.

I was planning on doing a: "newfield&right(KEY,find(KEY,"-"))" which would, in theory
strip out the first n digits of the KEY field and substitute the new field. I just cant
locate a command in the macro set for Access that returns the number of characters that a
specific value is into the string, which is what I need.

I want the following result:
Newfield = 2343
Key = 25-1-1
Result should be 2343-1-1

Believe me, this isn't the way I would like to deal with it but i am maintaining a system
with massive spaghetti code, and have no time to rewrite it!!

Help appreciated

Best

John Baker


Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.