Query parts of a field? (hopefully without Regular Expressions)

[debauch]

Hi,

Is is possible to query 'part' of a field so that you only return that 'part'? What I am doing, is trying to automate alerts/notications through outlook. Currently, I am downloading a folder from outlook into access and I want to send those emails out to specific email addresses based on another table. Problem is, what I need to join the two table together by is embeded in the "body" field.

Example, lets say the data in the "body" fields looks as such:

Type: HIGH
user: USERA
Result: EMAIL-22


Is there a way to query the field "body" to only return the word "userA" (or whatever user shows in the field).

I tried thing similar to - select * from TABLEA where Body like *USERA* but it just brings back the whole field. Is my only option regular expressions? Im not very good at these :dunno

Sugguestions, ideas? What is the best way to tackle this? I also thought about using things left right, right mid, but placement varies slightly for some of the emails.

[OBP]

What you need is a VBA function to find the User name that is "called" from the query.
I would search for the word "user:", when you have found that use search for the character that denotes the end of the "user" data.
When you have those 2 positions all of the data in between would be the User name.

[debauch]

Do you know where I might be able to find a similar sample to what you mean? A 'search' function of a field would be awesome. Even a tip on 'calling' a function in access, I would be grateful. I am pretty good w/ SQL Server, but Access syntax is a little different.

[OBP]

This database has the Query calling a VBA Function Module (Expr1 in Query 2)
and doing the same thing with a Query Expression (Expr1 in Query 3).
This particular Module is looking for a " " whereas yours will need to look first of all for a ":" and then the character that denotes the end of the User field which forces the new line.
So instead of
MyR = InStr(Data, " ")
you would use
MyR = InStr(Data, ":")
but you need to find the character that splits up the fields, can you copy and paste one of the Body Fields so that I can try and find what characte they use?

[debauch]

Sounds promising! I will take a look and get back to you , thanks!

[debauch]

Is there supposed to be more than one query?

[OBP]

Sorry that was an old version.

[debauch]

The queries in the attached file don't do anything?

This might help....

Let's say the field text loooks like this:


Type: HIGH
user: USERA
Result: EMAIL-22


Then I apply the following:

Expr1: left([body],InStr(1,[body],"user:")-1)


My results now appear like this :


Type: HIGH
user: USERA


So, it's chopped everything off up to the word I want by itself. How can I modify the query to only return USERA?? (below)


USERA


I managed to chop out the right most data, now I just need to chop everything left of the username off.

Thanks for all your help so far.

[CreganTur]

Try:
Right([body],InStr(1,[body],"user:")-7)

[OBP]

I don't know why you think the Query isn't doing anything.
Look at this version which is complete

[debauch]

Try:
[vba]Right([body],InStr(1,[body],"user:")-7)/vba]

Is there anyway to make this dynamic rather than "-7"? Almost a combination of left and right... ? Reason I ask, it the length of the user name vary, but "user:" and "result:" will always exist in the same spot.

[debauch]

I don't know why you think the Query isn't doing anything.
Look at this version which is complete
Sorry, I still don't get it.

Query 1 returns nothing
Query 2 expression 1 is erroneous numbers
Query 3 is the same as query 2

[OBP]

Ok to make sure that you have the correct copy, (I have a few) I have renumbered this one and removed the other queries.

[Demosthine]

Good Afternoon All.

Debauch: All of the Queries are working correctly, but I'll break them down for you and explain what each one is doing so that you will have a better understanding...


Query1
SELECT Table1.Data1 FROM Table1 WHERE (((myr([data1]))=myr([data2])));

Part 01:
SELECT Table1.Data1 FROM Table1 WHERE

The first portion of this SQL Statement is self-explanatory. You are selecting the Table1.Data1 Field from Table1.


Part 02:
(((myr([data1]))=myr([data2])));

Example Data:
Data1 Data2
S A D D A S

The second portion is calling a User-Defined Function that is stored in Module1. From your Objects Window in MsAccess, click on the button that says "Modules." Now Module1 will be shown on the right-hand section of that window. If you double click Module1, it will open up the standard VBE and you'll see the Function. This Function is returning the position of a space within the argument passed.

To understand this portion, you have to think about what it is doing. The left-hand operand is passing the the value of Table1.Data1 to the function and gets a return value. Using the example data above ("S A D"), it would return an Integer 2. The right hand operand is passing the value of Table1.Data2 and gets a return value. Again, based on the example data ("D A S"), you would receive a return Integer 2. The last step to MsAccess evaluating this is to compare the two return values:
2 = 2
What this Query essentially does is returns all Rows from Table1 where there is a space located in the exact same location in the Fields Data1 and Data2.


Query1
SELECT Table1.Data1, myr([data1]) AS Expr1, Left([Data1],[Expr1]-1) AS Expr2, Right([Data1],Len([Data1])-[Expr1]) AS Expr3
FROM Table1;


Part 01:
SELECT Table1.Data1

The first portion of this SQL Statement is self-explanatory. You are selecting the Table1.Data1 Field from Table1.


Part 02:
, myr([data1]) AS Expr1

Example Data:
Data1 Data2
S A D D A S

Although you may not have understood it, Expr1 does not return erroneous numbers. In fact, it is calling that function we discussed in the previous Query and assigning it's Return Value to Expr1. This is displayed in a temporary Field named Expr1. So in our example data, Expr1 would display an Integer 2: the first location of a space.


Part 03:
, Left([Data1],[Expr1]-1) AS Expr2

As you are probably aware, the format of the Left Function is
Left(string, length). In this part of the Query, we are evaluating the Left portion of Data1, using the value of Expr1 that MsAccess evaluated in Part 02 as the Length. Converting the code to its literal equivalent based on the example data, we have: Left("S A D", 2 - 1) which returns String "S" and displays it in a temporary Field named Expr2.


Part 04:
, Right([Data1],Len([Data1])-[Expr1]) as Expr3

This part is very similar to Part 03, except that it is returning the right portion of Data1 based on the value of Expr1. In order to return the proper amount of characters, we have to do some simple math. Since we want only the letters after the first space, we take the Length of Data1 and subtract the location of the space. Using the example string, you have the literal equivalent:Right("S A D", 5 - 2) which returns string "A D" and displays it in a temporary Field named Expr3.


Requested Query:
Now that you should have a much better understanding of what the previous Queries are doing, let's break your problem down and come up with a solution for your specific request.

For this example, I've created a sample database with a single Table named Messages. This Table contains five Fields:
MessageID AutoNumber PrimaryKey
Received Date/Time
Sender Text
Subject Text
Body Memo
I am using the DataType Memo for the Body because it stores more information, generally up to 65,535 characters and the body of an email can get quite lengthy.

For sample purposes, I have a single Row of Data:
MessageID Received Sender Subject Body
1 10/10/2008 12:00 Jolly_Roger@Sea.Com User Account Type: High
user: USERA
Result: EMAIL-22

Ultimately, we are going to use the Mid Function to extract the Username from the Body Field. The format for the Mid Function is Mid(string, start, length).

Part 01:
The first thing you want to do is to is find the location of the phrase "user: " within the Field Body. To do that, we use InStr. For now, I am assigning the equation to a variable and then we'll combine everything later.

intUser = InStr([Body], "user: ")

Since we are wanting to start after the above phrase, we are going to add the length of our search string to the location where it was found. This will give us the starting location of the actual Username.

intStartPos = intUser + Len("user: ")
or
intStartPos = InStr([Body], "user: ") + Len("user: ")

Part 02:
Now that we have the starting position of the Username, we need to determine how many characters we should extract. That requires a good deal more math, but it basically comes down to:
Length of the Body - Starting Position of the Username - Starting Position of the Text after the Username - 2 Characters for the Line Return

Believe me, that looks harder than it really is. First, we'll take the Length of the Body. Using the same method as determining the length of the text "user: " from above, we have:

intBodyLen = Len([Body])

Pretty simple, right? Good. Next, we subtract the Starting Position of the Username. But we already have that formula above in intStartPos, so we won't reiterate that.

Then we subtract the Position of the Text after the Username. In your initial post, that means finding the string "Result: ". Using the philosophy we had for finding "user: "

intResultPos = InStr([Body], "Result: ")

And don't forget to subtract the two characters for the Line Return (Cr and Lf).

If we put all of this together, we get:

intLength = intBodyLen - intStartPos - intResultsPos - 2
or
intLength = Len([Body]) - InStr([Body], "user: ") + Len("user: ") - InStr([Body], "Result: ") - 2


Part 03:
Now that we have all of the pieces of the Mid Function, we are going to combine them and write the actual statement.

SELET Mid( Messages.Body, intStartPos, intLength)
or
SELECT Mid(
Messages.Body,
InStr([Body], "user: ") + Len("user: "),
Len([Body]) - InStr([Body], "user: ") + Len("user: ") - InStr([Body], "Result: ") - 2
)
FROM Messages;

That should definitely get you going and answer your question. Happy Coding.
Scott

[Demosthine]

Oops. I forgot to attach the copy of the Database. That much typing and some things just slip right on by...

Scott

[debauch]

Wow .... that's an awful lot to chew on. You hit the nail on the head though, I just didn't understand exactly how the mid/instr/left/right etc was working.

The above appears to provide the desired output and I will certainly need some time to go through the explaination but thank you very much for the time to help. I will provide an update on the status of the project once I get it working - thanks!!

[Demosthine]

Definitely read through the explanations as it gives you a lot of information. If I'm to guess correctly, there will be several other instances where you'll want to do the same type of manipulation in your project.

SQL is a very strong language once you learn it and the number of tasks you can accomplish are virtually endless. If you're doing this very often, I'd definitely recommend either a course or finding a good book.

Scott

[OBP]

Scott, very nice analysis. :beerchug: