Query Text in the Mid of a memo

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.
0
Utf
1/28/2008 8:24:10 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
981 Views

Similar Articles

[PageSpeed] 27

AngieSD wrote:

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


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 & "*"

-- 
Marsh
MVP [MS Access]
0
Marshall
1/28/2008 10:09:40 PM
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" wrote:

> AngieSD wrote:
> 
> >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.
> 
> 
> 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 & "*"
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
1/28/2008 10:40:02 PM
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" wrote:

> AngieSD wrote:
> 
> >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.
> 
> 
> 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 & "*"
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
1/28/2008 10:57:01 PM
On Mon, 28 Jan 2008 14:57:01 -0800, AngieSD
<AngieSD@discussions.microsoft.com> wrote:

>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

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]
0
John
1/29/2008 12:00:00 AM
That did it!!  I was on that same track but you saved me a lot of trial and 
error!!!  Thank you

"John W. Vinson" wrote:

> On Mon, 28 Jan 2008 14:57:01 -0800, AngieSD
> <AngieSD@discussions.microsoft.com> wrote:
> 
> >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
> 
> 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]
> 
0
Utf
1/29/2008 12:11:00 AM
Reply:

Similar Artilces:

Exchange rich-text format
What are the results from the client side if I change my Exchange 2003 server to "Alwayes Use" Exchange Rich-Text format from "Determined by individual user settings"? On Thu, 22 Jun 2006 08:31:01 -0700, CK <CK@discussions.microsoft.com> wrote: >What are the results from the client side if I change my Exchange 2003 server >to "Alwayes Use" Exchange Rich-Text format from "Determined by individual >user settings"? Depends on what your users are sending messages as. Most will be using HTML or RTF anyway unless you have some policy in p...

Query Problems
I have a table of ecards that is populated from a website and includes receiver, sender, and Team leader. The problem I am having is names can be entered into the table like this "Tom Thumb", but Team leader is a dropdown menu that is constant and lists name like this: "Tom S. Thumb". When I make a query to find all the members of Tom S. Thumb's team, I get everyone except Tom Thumb, who is member too. Tom Thumb has his own higher team leader, so I can't change that. How do I get Tom Thumb to be included when I enter "Tom S. Thumb" as Team Leader? ...

Excel web query returns no data
When I enter a web addreess into IE it shows the page When I set up a web query in Excel it allows me to select the relevant tables, but then returns no data The same Excel query worked on previuous versions of Excel Web address for query http://www.bmreports.com/servlet/com.logica.neta.bwp_PanBM DataServlet?param1=T_CNQPS- 4&param2=&param3=&param4=&param5=2001-04-01&param6=* Any help appreciated ...

How to remove the alternative text box from the publisher email?
Hi, I have created a publisher email and have email it to myself for testing view. I find there is a small alternative text box appearing whenever my mouse cursor station at any point of the email. How to remove this alternative text box from the publisher email? Help... -- Thank you, Cpviv Sounds like all the text has was converted to an image. Try to select the text and you will see it is an image. Go to tools > Options > Web tab and under Email options uncheck the option to send as an image. If that doesn't fix the problem, reference: Tips and troubleshooting for ...

Query to Mail Merge issue
I am running Access2003 on WinXP SP2. I send out dues letters for members of a retiree group. When I try to do a mail merge to my Dues Query all goes fine except the telephone number in the query which is (555)555-1234 comes out 5555551234. If I copy the query into an excel spreadsheet and use that as the datasource for the mail merge it works fine. Is there something I need to do to format the query so it merges with the right format. Thanks Billa In the query format the field to include the parentheses and dash. In query design view, instead of the field name put an expression alo...

Change from MS Outlook Rich Text to HTML
I use Outlook 98 and I want to change my message format to HTML so that I can have nice backgrounds etc on my mail. For some reason this facility doesn't seem to be available to me on the 'Mail Format' Tab. It is blacked out and I cannot change it from Rich Text to HTML. Is there anything I can do to sort this out? Could it be because I am on a network at work and they have disabled it? If so, how can I enable it? Cheers! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www...

Export query to Excel with formated heading
When export from query to Excel, I'd like to turn some columns into heading row on top of Excel sheets how to turn this: LastName FirstName SID Section Instructor Doe John 1234 0545 Su Doe Jane 2345 0545 Su into this? Class Roster Instructor: Su Section: 0545 LastName FirstName SID Doe John 1234 Doe Jane 2345 I think one way of achieving this would be to create a report in Access where you would group under instructor with the students in the detail section. From the report you can then use the analyze with Ex...

If statement with formatted text
Hi, Is there any way to have an if formula such as: If (A1="Active", "KAE",KPE") where the two letters after the K are formatted as subscript? The best I've done is to paste a picture over the cell. The picture's formula refers to named formula that selects one of two cells, the one with correct string. One cell contains KAE and the other KPE with the subscript. However, it means that I'm using a picture and it would be much nicer if I could just do it in an If formula. I hope that makes some kind of sense, and thanks in advance for your help. D...

Trouble with hyhens within text when using LOOKUP
I have two columns, each containing a list of part numbers. Some of the part numbers contain hyphens. I am using LOOKUP and/or VLOOKUP to determine if the value in one column exists in the other. This works great on non-hyphenated part numbers. However, it will not find or return the hyphenated part numbers from the specified arrays. As a test, I did a quick if statement to compare the instances of identical hyphenated values that exist in both columns. Those statements did not have a problem with the hyphens. Can anyone offer any help? If hyphens cannot be used in conjunction with the ...

instructions disppear when users begin type (text field)
Hi all, I need to customize the outlook contact form and I want to add one text field to allow users to add details info and instruct users how to add. Instructions shows in the field and the instructions disappear when users click and begin to type. How should I do this? exchange 2003/outlook2003 Thank you. It's hard for me to visualize exactly what you're expecting to happen. If you want the instructions to stay on the screen, you could display them in a label control. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumps...

Saving html message as Draft changes text formatting...
WIN XP HE, OL 2002 Hi, I have recently noticed that whenever I write an email (using Word as editor) in html format, and instead of sending it, save it (to the drafts folder), the text itself changes format from my default to another one. It seems to change in the paragraph style which then changes the text format. The only change I recently made was to edit my signatures in html, rtf and plain text format. When I write a new email, it opens up with the signature already in it and perhaps there are format/style conflicts..? Tx for shedding some light into this. S As an added information, t...

can lookup return cell reference istead of "text" for sumif?
I am trying to use a lookup-function to determine a different sum range for several criteria. Like so: =Sumif($A$7:$A$1447;"<"&X3;vlookup(e3;AT3:AU11;2;false)-Sumif($A$7:$A $1447;"<"&y3;vlookup(e3;AT3:AU11;2;false) The problem is that the vlookup returns text and not the cell reference. Is there a way to get the answer from the lookup expressed as cell reference instead of text, since sumif can't use text, just the cell reference? I use it to calculate the number of hours the staff should be paid, so it's different from weekdays to saturdays, holidays...

Using cell text in a formula
I am trying to use derived cell references in a VLOOKUP formula to matc data in several tables. For example, A1 contains the cell reference fo the top left of my array (A3) whilst cell A2 contains the cel reference for the bottom right of my array (D14). The array I' checking against starts in column E3. However, when I use the formula =VLOOKUP(E3,A1:A2,4,FALSE) I get a #N/ error. I need to use the cell references in each VLOOKUP as the arra sizes may vary in each case. (PS, I've used =INDIRECT(ADDRESS(A1,A2) to derive the cell references. Ji -- Message posted from http://www.Excel...

cannot view all of text in large cell, even though I have it to w.
I have cell format to wrap text and it works fine to a p[oint then no more text is displayed....casn increase the size of the cell, but still only so much will display....rest of the cell show blank. Hi +the limit is 1024 characters. You can extend this with manually inserting linebrekas using aLT+ENTER -- Regards Frank Kabel Frankfurt, Germany sydme wrote: > I have cell format to wrap text and it works fine to a p[oint then no > more text is displayed....casn increase the size of the cell, but > still only so much will display....rest of the cell show blank. ...

Query Criteria issue
Access 2003: Trying to create a demographics report that will allow the following. Gender example On the form "frmStudentDemographics" is a drop down field named "cboGender" When the user makes a selection "Male" a report is generated for only Males. But in some cases the user will want to run a report for both (all) genders. So I added a check box next to the drop down named "ckAllGender" My idea is that if that box is checked then the report will show information on Both (all) genders. So in the query that generates the report, in the c...

Text box jumps to left of page
Word 2004 (I am relatively new to Word and am delighted to find a forum specifically for the Mac version. There are a number of unresolved, niggling issues I can live however they slow the workflow. I am eager to learn.) In the recent past, I manually converted 12,000+ recipes from WordPerfect 7 to Word. Since Word 2004 does not have a filter for the old files, the conversion was done on the Windows side of my Mac in Word2003. Those files _usually_ open without protest also in 2004. One annoyance regards text boxes. When text was highlighted and a text box was requested for it in...

Spontaneous 'Compile Error' In Queries
I've got a half-dozen queries that contain this expression: IssuerName: IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer Unknown]",[tblIssuer].[IssuerName]) Haven't touched any of them, but suddenly they were all throwing "Compile error. in query expression 'IssuerName: IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer Unknown]",[tblIssuer].[IssuerName])'." tblIssuer.IssuerID is still there in a link. My kneejerk was to somehow force recompiles of all the queries, so I did a Compact/Repair. After the compact/repair, all was well: the errors went away. C...

How did you add text into publisher, without using boxes?
how do you add text to publisher without using text boxes I suppose you could create your text as an image and insert the image into your publisher file. -- Don Vancouver, USA "Calvin Scott" <Calvin Scott@discussions.microsoft.com> wrote in message news:64D23D52-138D-47B4-B265-4A41BF14BF55@microsoft.com... > how do you add text to publisher without using text boxes Calvin Scott <Calvin Scott@discussions.microsoft.com> was very recently heard to utter: > how do you add text to publisher without using text boxes You don't. Text in Publisher has to e...

Create your personal SMS homepage, let friends send FREE text message to your mobile phone by web
Create your personal SMS homepage, let friends send FREE text message to your mobile phone by web Support over 147 countries and ALL languages (unicode encoding) MSG.to provides a unique personal SMS URL for you. It is easy for friends sending text message to you by web. For example, if your name is Lisa, you can register: http://MSG.to/Lisa (you can use this link to see demo) You can also register your mobile phone number as SMS URL like: http://MSG.to/+886913123456 (you can use this link to see demo) Please visit http://msg.to/ for more information. Related keywords: Short Message Serv...

imbed excel cell text in a shape
I need to insert a number into a shape that corresponds to a cell in excel. Kinda like the exploded parts view in a car repair manual. A callout shape with a text value "123" that is linked to cell A1 in file ***.xls. It would be nice to be able to auto generate the callouts from the excel file. shape 123 is cell A1, shape 124 is cell A2 and so on. Thanks what version of visio are you running? al "mystified" <mystified@discussions.microsoft.com> wrote in message news:80B4E390-190A-41D9-AC4A-F96C1DA6FB6E@microsoft.com... >I need to insert a number into a s...

Do you need MS Access to query on an Access table?
I'm asking these questions because I have looked at a lot of stuff in the discussion groups and still confused. I am fairly good at Excel programming but haven't been able to get Excel and Access to talk. First, I have Office 2003 Professional at work and Office 2003 without Access at home. I want to work on developing Excel programming at home which will get data from Access to Excel with either MS Query or with programming. Is it possible to just have the .mdb files on the home computer for Excel to work with, or do I need the Access program too? The info I want ...

Text box unwanted border
For some reason I know not why, that even though I enter 'No line' in both Line/Border style and Dash style I still have a visible bold dash surrounding the text box. Any help would be appreciated. Did you happen to draw a rectangle around the text box? Try highlighting it, and move the bottom text box adjustment up. If the dashed line didn't move, click on it and delete. Or, Copy the text, and paste it and if the dashed line didn't go with the copy, delete the original. -- Don Vancouver, USA "Meridklt" <Meridklt@discussions.microsoft.com> wrote in m...

Combining two types of functions. Select Query and DLookup
I would like to write a code that performs the following action: 1. If the value in a comboBox Project Number is 19912 2. Perform a select query on comboBox Task Number and comboBox National Site ID 3. If the value in a comboBox Project Number is not 19912 4. Perform a select query on comboBox Task Number and a DLookup () on comboBox National Site ID I tried the code below but it's not working. Any ideas on how to do this? If Me.cmbProjectNumber.Value="19912" then cmbNationalSiteID.RowSource = "SELECT DISTINCT [National Site ID] FROM [InScope Table] WHERE [Proje...

Time to text conversion
I have Mac's version of Office (Office 2004), but it shouldn't matter.. The problem: I have a column in a table containing times (for some events). Data found in the cells looks like this: 1) "20:00 2) "20:00/22:00" (starting/ending time) Excel has automatically formated type 1 cells as "time", and type 2 cells as "text". I need all the cells to be formatted as "text". How? If i change the formatting of a cell, it displays something like "0.833333" (20.00 divided by 24). The only solution I came out with is copying the whole co...

hierarchical data
Now that Jet is being developed in-house by the Access dev team can we expect to see some movement on its querying capabilities. I for one would love to see some functionality to better aid in representing hierarchical data. SQL Server 2005 introduced "Recursive CTE's" will Access follow suit with a similar concept? If I could request only one future feature this would be it! ...