MID(INSTR()) Function

[kbreiss]

I've attached a sample database to attempt to explain better what I'm trying to do.
I'm trying to find the right combination of using the Mid and Instr function to extract each
series of numbers between dashes. With the sample query I am able to extact the first and seconds
series of numbers between dashes, but I can not get to go any farther than the second position.

I think for the third and beyond series it needs to be somthing like.........
Select numbers, numbers, Mid([numbers], Instr([numbers], Instr([numbers], "-"), "-"))
From Table1;
But I'm getting an "#error" on this query.


Hopefully this helps me better explain what I'm trying to do.

Thanks in advance.

Kacy
________
MOTORCYCLE TIRES

 

[Nouba]

I used a custom function (Split_Striing) which works similar like the Split function in A2K.

 

[pdx_man]

Your Second field needs to be:
Second: Mid([Numbers],InStr([Numbers],"-")+1,InStr(Right([numbers],Len([numbers])-InStr([numbers],"-")),"-")-1)

(Try changing the length of the first to a size different from the second with your string and then try it with mine)

Which then would make your Third field be:
Third: Mid(Right([numbers],Len([numbers])-InStr([numbers],"-")),InStr(Right([numbers],Len([numbers])-InStr([numbers],"-")),"-")+1,InStr(Right(Right([numbers],Len([numbers])-InStr([numbers],"-")),Len(Right([numbers],Len([numbers])-InStr([numbers],"-")))-InStr(Right([numbers],Len([numbers])-InStr([numbers],"-")),"-")),"-")-1)

So, it is only going to get uglier from here to parse the rest of the string. So, a function like Nouba's to parse the string is the way to go.

 

[kbreiss]

This is great guys.........thank you very much. Have one more question.....if in those string of numbers and dashes I have a number in between [] can I just say for it to search for this in my query?

Here's my example....
99-569-78[00]

Thanks again.....exactly what I've been looking for!

Kacy
________
MARIJUANA VAPORIZERS

 

[pdx_man]

Do you mean substituting the '-' for '[' in the first part and '-' for ']' in the second? Uhh ... sure, but I don't think that is what you are asking ... is it?

 

[kbreiss]

That's what I'm trying to ask anyways. So for example, my number in the above post,

99-569-78[00]

I would like to have 99 in one field, 569 in one filed, 78 in one field and the 00 in one field.

I was hoping in the query giving to me I could substitute some how the "-" for a "[" to extract the number in brackets.

Hopefully this explains better what I'm trying to do.

Thanks,
Kacy
________
CHEAP UTG AIRSOFT PISTOL BLACK SLIDE

 

[pdx_man]

Are you using Nouba's solution? With her function, you pass the delimeter. So, you would have to use something other than [ and ]. It would have to be something consistant, like a + sign or something.

If you're not, re-write the Second string using the brackets.

Brackets: Mid([Numbers],InStr([Numbers],"[")+1,InStr(Right([numbers],Len([numbers])-InStr([numbers],"[")),"]")-1)

 

[Nouba]

with the Replace function (I put one in the db) you can substitute the pair of brackets before giving the result to fctSplit as an argument.

 

[kbreiss]

Thanks guys.......on Nouba's db example I keep getting a compile error and can't figure out why. Any ideas?


Kacy
________
THE APPRENTICE ADVICE

 

[Nouba]

You could copy the code over to your own db and use the functions like in the query which you could hopefully see. What happens on manually compiling the db?

 

[kbreiss]

I'm still getting a compile error. I can live without this part however. I don't want you to have to spend anymore time on this. Thank you very much for your help.

Kacy
________
Kitchen Measures

 

[pdx_man]

On the line:

Public Function Replace(ByRef Text As String, _
                        ByRef sOld As String, ByRef sNew As String, _
                        Optional ByVal Start As Long = 1, _
                        Optional ByVal Count As Long = 2147483647, _
                        Optional ByVal Compare As _
                        VbCompareMethod = vbBinaryCompare) As String

I get:

Compile Error:
Automation type not supported in Visual Basic

 

[Nouba]

I developed the the sample with Access XP and have no chance to test it with A97 at home.

Can you check, if there are any missing references in the sample. What happens after removing the MISSING selections and recompling the db.

 

[pdx_man]

You know, that was the first thing I checked, but it showed nothing MISSING.

Beats me ...

 

[TIMdotM]

SOLVED: Please see next page of thread for details.


Good day all,

First and foremost thank you in advance for your time and thoughts. I am posting here as this thread seemed relevant to my inquiry, but I apologize if was more appropriate placed elsewhere.


Within my database I have a field [Contents] containing a data scrape from an external outlook folder as data source. This is pulling in content from email request messages that look as below with ascii characters revealed:

From the imported field [Contents] I am attempting to parse each line into a new field within the database using calculated fields and the MID query function. I have been successful for all parse actions except "Location:"

Below is my code intended to initiate the MID function at the end of the "Location:" +10, and then parse the length of the string through the appearance of the carriage return + line feed at the end of the line:

Expr1: Mid([Contents],InStr(1,[Contents],"Location:")+10,Len(InStr(InStr(1,[Contents],"Location:"),[Contents],Chr(13) & Chr(10))))

As a result I am getting the correct starting position, but the MID function is returning only three characters "Los" and not "Los Angeles - Customer Service"

On a side note, as this is a function within a calculated filed it is my understanding that I can not use and call a more advanced VBA module to conduct the string parse, but feel free to correct me as I am a beginner with access.

 

[pbaldy]

I personally would not use calculated fields, I'd parse that data into individual fields as I imported this (probably using Split() function). You are correct that you can't use a user-created function in a calculated field. If you wish to stick with the calculated field, couldn't you use InStr() on "Additional" and subtract for the length?

 

[TIMdotM]

Hi Paul, thanks for the prompt reply and your thoughts on InStr() on “Additional” with length subtraction; however, I have taken your advice and moved away from calculated fields and handled parsing via post-import processing:

SOLVED: using Nouba's database attached on page 1 of the thread containing the basReplace and basSplit functions and following code:

SELECT T.Contents,
fctSplit(Replace(Replace([Contents],Chr(13) & Chr(10),Chr(27)),"]",""),Chr(27),0) AS TicHeader,
Mid(fctSplit(Replace(Replace([Contents],Chr(13) & Chr(10),Chr(27)),"]",""),Chr(27),1),8,255) AS TicEmail,
Mid(fctSplit(Replace(Replace([Contents],Chr(13) & Chr(10),Chr(27)),"]",""),Chr(27),2),17,255) AS TicSupervisor,
Mid(fctSplit(Replace(Replace([Contents],Chr(13) & Chr(10),Chr(27)),"]",""),Chr(27),3),11,255) AS TicPhone,
Mid(fctSplit(Replace(Replace([Contents],Chr(13) & Chr(10),Chr(27)),"]",""),Chr(27),4),10,255) AS TicDepartment,
Mid(fctSplit(Replace(Replace([Contents],Chr(13) & Chr(10),Chr(27)),"]",""),Chr(27),5),8,255) AS TicSystem,
Mid(fctSplit(Replace(Replace([Contents],Chr(13) & Chr(10),Chr(27)),"]",""),Chr(27),6),21,65535) AS TicComments
FROM olimport AS T;

 

[pbaldy]

My thinking was along the lines of:

if the starting position of "Additional" is 50 and the starting position of "Location" is 40, I can subtract and get the desired length of 10

Adjusting of course for the extra characters. If you're still stuck, post some sample data and I'll play with it. That said, I'd still parse it out during import. I've imported data from emails with data structured like that.

 

[pbaldy]

Ah, looks like you were editing while I was typing. Glad you got it sorted.