Query memo field to only show

[coolkake]

Hey all

I've got to figure out a way to query a memo field to only show me the numbers after CLIENTELE CALL in the memo field

It would look like this in the field

CLIENTELE CALL 12345

When I try a query looking for CLIENTELE CALL it returns everything in the memo field and I can't figure out a way to only show what I'm looking for

Thanks for your help in advance

Ken

 

[HarleyQuinn]

I would look into using the functions INSTR() (or even INSTRREV()) and RIGHT().

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[dhookom]

To find any help on the string functions, you may need to open any module/code window in Access and then press the F1 key to get Help.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[coolkake]

Thanks but I don't have any experiance at all in the module/code window of access. When I've gone there in the past I have never gotten anything to work

Is there anyway to do this using SQL statement or from design view in Access?

Also if you know of a good online course for VBA for Access for beginners I'm all ears

 

[HarleyQuinn]

What Duane recommended is the easiest way to go, just go into the module/code window and type in INSTRREV (for example), put the cursor on it and then press F1 to get the help up. You don't have to do anything code wise in there, it's just easier to get the help from there. Once you've got the help up on the functions you need, you can then just use them in your select statement in the exact same way you would use any other function in SQL.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[coolkake]

I've read over the help files on INSTRREV and I'm just not getting it. There are no examples of what this does

Feeling stupid this Monday morning

 

[HarleyQuinn]

No worries, I'll show you some code and explain it

coolkake: Right([columnA],Len([columnA])-InStrRev([columnA]," ",-1))

What InStrRev does is it returns the position of an occurrence of one string within another, from the end of string. (you specify the character as the second parameter, in this case a space), so in the example you posted it would return position 6 (the second space). I think that makes sense

From there you take everything RIGHT of that character. You do that by using the RIGHT() function and in the length parameter you pass the length of the field minus the position you found the character using INSTRREV()

I've posted the code that will work for you but please (and I only ask because I'd prefer to help than provide solutions, though both are applicable here I suppose), read up on the functions I've used in the example. Search the 'net or search Tek-Tips and you'll generally find something useful.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[coolkake]

I totally understand your thoughts Harley and I don't want someone to make me the solution; that's what consutants are for I want to figure out this one particular problem and using what I've learned from this I will be able to address some others as well.

Having said that I think I need a bit more help getting this to work. Where do I put this code you gave me?

I've got a query open in Access and here's my SQL statement

SELECT RMA_Emails.Contents
FROM RMA_Emails;

I did try to paste this into Module1 in the Visual Basic Editor but I got a compile error and I'm not sure if this is where I need to enter this.

Again thansk for the help and I'm really hoping this exercise will at least give me enough of an understading to be able to make some advanced queries.

 

[lespaul]

SELECT Contents, Right([Contents],Len([Contents])-InStrRev([Contents]," ",-1)) As NewField
FROM RMA_Emails;

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[coolkake]

Leslie

This works but the results have nothing to do with CLIENTELE CALL in the memo feild. Is there a way I can search for that and report back "CLIENTELE CALL ?????" with the ? being the number of each call?

 

[dhookom]

Try:

SELECT Contents, Right([Contents],Len([Contents])-InStrRev([Contents]," ",-1)) As NewField
FROM RMA_Emails
WHERE Contents LIKE "*CLIENTELE CALL*;

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[coolkake]

Didn't change anything in the results

Here's my SQL now

SELECT RMA_Emails.Contents, Right([Contents],Len([Contents])-InStrRev([Contents]," ",-1)) AS NewField
FROM RMA_Emails
WHERE Contents LIKE "*CLIENTELE CALL*";

 

[dhookom]

coolkake,
Please provide a few complete values of the Contents field as well as what you want to see in the results.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[coolkake]

I had to edit some of the information but not the structure. The lines (-----) at the top are included in every field but not always at the very top like this one shows. It does look like after the ----- it does have CLIENTELE CALL on the next line though.



----------------------------------------------------
CLIENTELE CALL 65769

Caller: -
Phone:
Summary: DVR02 - A82CNG9E: Replace the disk immediately. RMA

24/12/2007 10:36:22 AM DROY
RMA Required Info
· Defective Part number - 15837
· Defective Serial number - A82GL02E
· Replacement Part number -
· Fault Code -
· Special instructions -
· Contact Name -
· Phone # -
· MAPM email address -
· Logistics email address :
· Call assigned to -

A82CNG9E: Replace the disk immediately. Before returning for warranty (if applicable), quarantine the disk until the data is no longer required.
Error Disk Failed December 22, 2007

 

[dhookom]

as well as what you want to see in the results

Your specs have not been clear regarding what exactly you want to display. Are the numbers always 5 characters long? Is there always a space before and after the number?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[coolkake]

Sorry about that.

Yes the numbers are always 5 characters long and there is always a space before and after a number.

All I want back is

CLIENTELE CALL #####

 

[HarleyQuinn]

Seems that's massively different than what I thought was in the field, it looks like you're going to have to use InStr() to find the "CLIENTELE CALL", and also what I think Duane is driving at is that if they are you can hard code numbers of characters etc into the query rather than having to use more functions to get what you want.

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[dhookom]

Try use Mid() to extract a group of characters from within a text string and Instr() to find the matching characters.

SELECT RMA_Emails.Contents, 
Mid([Contents],Instr([Contents],"CLIENTELE CALL"), Len("CLIENTELE CALL 12345")) as NewField
FROM RMA_Emails
WHERE Contents LIKE "*CLIENTELE CALL*";

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[HarleyQuinn]

Try something using Mid() then:

Mid([Contents], InStr([Contents], "CLIENTELE CALL "), 20))

Is that any closer to what you're after?

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Just beat me there Duane!

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.