• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

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
0
timohorn
Asked:
timohorn
  • 7
  • 6
1 Solution
 
teebonCommented:
Hi timohorn,

Try the following:

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

Open in new window

0
 
timohornAuthor Commented:
then i get the right part of the string i need the middle part, can you say 2nd chr10 from the right ?
0
 
mbizupCommented:
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
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
timohornAuthor Commented:
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....
0
 
mbizupCommented:
<< 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.
0
 
timohornAuthor Commented:
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 ?
0
 
mbizupCommented:
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.
0
 
timohornAuthor Commented:
Okay i ll try it

Thanxs.
0
 
mbizupCommented:
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).
0
 
timohornAuthor Commented:
Hello mbizup,

Your last suggestion works fine exept that i get an unwanted char before the string [] can i remove the first char ?
0
 
mbizupCommented:
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)
0
 
mbizupCommented:
Before doing that though, see if this returns the correct results:

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

Open in new window

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


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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now