Query Text in the Mid of a memo

  • Thread starter Thread starter AngieSD
  • Start date Start date
A

AngieSD

I am trying to query off a memo field [Description] the text that follows
"User Name: " until the first space the "User Name: " is not in the same
position nor is the text of the user name a fixed size.

I am working with an InStr function but I don't know how to stop at the " "
after the name.

Thanks so much for any help.
 
AngieSD said:
I am trying to query off a memo field [Description] the text that follows
"User Name: " until the first space the "User Name: " is not in the same
position nor is the text of the user name a fixed size.

I am working with an InStr function but I don't know how to stop at the " "
after the name.
Click to expand...


Assuming you have a form with a text box where you can enter
the text you eant to search for, try using criteria
something like this:

Like "*User Name: " & Forms!theform.textbox & "*"
 
I am sorry it is an imported field from a csv file all I want returned in my
query from this field is the name after user name.

Marshall Barton said:
AngieSD said:
I am trying to query off a memo field [Description] the text that follows
"User Name: " until the first space the "User Name: " is not in the same
position nor is the text of the user name a fixed size.

I am working with an InStr function but I don't know how to stop at the " "
after the name.
Click to expand...


Assuming you have a form with a text box where you can enter
the text you eant to search for, try using criteria
something like this:

Like "*User Name: " & Forms!theform.textbox & "*"
Click to expand...
 
I am afraid my description is confusing..

The field I am pulling from has sample data:

blahblahblah User Name: anyname blahblahblah
blahblah User Name: diffname blah
blahblahblahblahblah User Name: lastname blahblahblahblah

I want my query to return:

anyname
diffname
lastname

I hope that makes more sense.

Thank you again for the help!

Angie

Marshall Barton said:
AngieSD said:
I am trying to query off a memo field [Description] the text that follows
"User Name: " until the first space the "User Name: " is not in the same
position nor is the text of the user name a fixed size.

I am working with an InStr function but I don't know how to stop at the " "
after the name.
Click to expand...


Assuming you have a form with a text box where you can enter
the text you eant to search for, try using criteria
something like this:

Like "*User Name: " & Forms!theform.textbox & "*"
Click to expand...
 
I am afraid my description is confusing..

The field I am pulling from has sample data:

blahblahblah User Name: anyname blahblahblah
blahblah User Name: diffname blah
blahblahblahblahblah User Name: lastname blahblahblahblah

I want my query to return:

anyname
diffname
lastname
Click to expand...

You'll have to play around with Mid() and InStr() functions.

Try:

Username: MID([memofield], Instr([memofield], " User Name:") + 12,
InStr(Instr([memofield], " User Name:") + 12, [memofield], " ") -
(Instr([memofield], " User Name:") +12))

John W. Vinson [MVP]
 
That did it!! I was on that same track but you saved me a lot of trial and
error!!! Thank you

John W. Vinson said:
I am afraid my description is confusing..

The field I am pulling from has sample data:

blahblahblah User Name: anyname blahblahblah
blahblah User Name: diffname blah
blahblahblahblahblah User Name: lastname blahblahblahblah

I want my query to return:

anyname
diffname
lastname
Click to expand...

You'll have to play around with Mid() and InStr() functions.

Try:

Username: MID([memofield], Instr([memofield], " User Name:") + 12,
InStr(Instr([memofield], " User Name:") + 12, [memofield], " ") -
(Instr([memofield], " User Name:") +12))

John W. Vinson [MVP]
Click to expand...
 
Back
Top