Parsing Data

[TheEnigma]

Hey there!

I found another thread 701-797928 that talks about parsing data and how to get the information before a character, and a seperate one for how to get the data after a certain character, but would I would like to do is do both from the same field.

I have a field in a table where there is always a * vendor name - I would like to be able to pull the vendor name out of each column and put it into a new column in a query. The vendor name varies from row to row.

I tried putting both of these pieces of code together but all I ended up with was a zero in the new column.

Both bits of code I am using from that thread are:

Before the -

=Left([Vendor],InStr([Vendor],"-")-1)

After the *

=Mid([Vendor],InStr([Vendor],",")+1)

 

[KenReay]

Hi

To begin

=Mid([Vendor],InStr([Vendor],",")+1)

is searching for a comma ","

tp search for an * you need

=Mid([Vendor],InStr([Vendor],"*")+1)

It is not clear where you have this code

A suggestion why not read help on Mid(), Left() and Instr(), that may help you understand what the code does

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TheEnigma]

Sorry I have this in a query. I actually have the - in the query, I just copied and pasted the original piece of code and forgot to change the , to a -

I have a basic understanding of what this code is doing, but I guess I am not sure how to join both parts of the code together so that I am getting the information from before the - and after the *.

I will have a look at help and see if that explains that!

Thanks

 

[PHV]

And what about this ?
Mid(Left([Vendor],InStr([Vendor],"-")-1),InStr([Vendor],"*")+1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheEnigma]

That is perfect, thank you!