asked on

get second value from string through query

i have a connected table (odbc), where tere are adresses stored, the layout is

debiteuren (space/hard return) address (space/hard return) postalcode city.
i need the address (postacode and city are in an other field as wel.

if i try right([adres];len([adres] -instrrev([adres;" ")) i get the value of the city
how can i extract the address, i think the problem is maybe because of the hard return and i'm selecting a space.

example

miss constance [] stattionstraat 5[] 1121ad amsterdam
the result is amsterdam and i want stationstraat 5

teebon

Hi timohorn,

Try the following:

right([adres],len([adres] -instrrev([adres,Chr(10)))

Open in new window

timohorn

ASKER

then i get the right part of the string i need the middle part, can you say 2nd chr10 from the right ?

mbizup

Give this a try -

1. Create a function in a module (name your module something like modGeneralFunctions):

Function GetAddress(strField)
      GetAddress = Split(strField, vbCRLF)(1)
End Function

Open in new window

2. Call that function from a query

SELECT GetAddress([adres]) AS YourAddress
FROM YourTable

timohorn

ASKER

i could do that but then i have to write a bunch of code, create a recordset, and if i don't have to do that, that would be nice....

mbizup

<< bunch of code>>

Assuming you are using an Access user interface (guessing from your question tag), that is all the code that is needed.

If you are not using Access, please describe a bit more about what you are using.

timohorn

ASKER

no i'm using access, but i have to make a form on wich i have to compare several fields from two different sources one access db and the other an sybase database, can i use this getadress as a field source ?
if so then i can put the code behind a form ?

mbizup

You can call it in a variety of ways around your database:

- From a query
- From code behind a form
- From control source properties to display in textboxes on your forms
.... etc.

timohorn

ASKER

Okay i ll try it

Thanxs.

mbizup

You can also try this for an SQL solution:

SELECT Mid(YourFieldName,instr(1,YourFieldName, Chr(10)), instrrev(YourFieldName, Chr(10))- instr(YourFieldName,Chr(10)))  AS MyAddress
FROM YourTable

Open in new window

Depending on how the carriage return/line feed was created (Access, Word, Excel, etc), you may need to use Chr(10) & Chr(13) instead of just Chr(10).

timohorn

ASKER

Hello mbizup,

Your last suggestion works fine exept that i get an unwanted char before the string [] can i remove the first char ?

mbizup

Try running this query against that field:

SELECT Asc(Left(Trim("" & YourFieldName),1))

Doing so should return a numeric code for that box-like character.

Using that numeric code instead of "123", try running the following SQL

SELECT Replace(Mid(YourFieldName,instr(1,YourFieldName, Chr(10)), instrrev(YourFieldName, Chr(10))- instr(YourFieldName,Chr(10))), 123,"")  AS MyAddress
FROM YourTable

Open in new window

(Replace the 123 in the second SQL statement with whatever character code is returned from running the first query)

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

timohorn

ASKER

created a query based on tour suggestions and did al right len -1 so i removed the leading char.

Thanxs

mbizup

Glad to help out - and good job with that nice simple approach to that stray character!