Access-o-Mania

Hallo,
habe ein problem mit einer SQL Abfrage

me.Straße.RowSource = "SELECT distinct(Straße) FROM tbl_Kontakt WHERE Straße LIKE '" & Straße.text & "*'"

ich möchte dass Straßennamen ohne Hausnummer ausgegeben werden.
in der Tabelle Steht
z.B. Straße: Hauptstraße 12

der Kombinationsfeld soll aber Ohne Hausnummer gefüllt werden.

kann mir dabei jemand Helfen?

Hausnummern sind ein generelles Problem.

Beispiele für Hausnummern:
14b
2/1
2b/403
42-46
14/b

Je nachdem wie die Hausnummern eingegeben werden, kann es sein, dass auch noch Leerzeichen dazwischen sind.
Man könnte in SQL den hintersten Teil (bezüglich des Leerzeichens) abschneiden, man kann das auch wiederholen bis man etwas erwischt, was nicht mit einer Ziffer begint (dann hat man recht wahrscheinlich die Hausnummer).

Jedoch ergibt sich häufig eine weitere Problematik: Manche Datenerfasser schreiben hinter die Hausnummer so etwas wie z.B. "Zimmer xy" oder "4. O.G.". Das geht dann garantiert schief.
Oder man hat im Strassenfeld "Postfach 22 33 44" stehen, das ist dann auch fraglich, ob das Resultat dann das gewünschte ist.

Ich habe mit solch einer SQL-Query in Access (in mySQL ist es etwas einfacher) eine recht hohe Trefferquote erreicht:

SELECT Left([strasse],InStr([strasse]," 1")) &  Left([strasse],InStr([strasse]," 2")) &  
Left([strasse],InStr([strasse]," 3")) & Left([strasse],InStr([strasse]," 4")) &
Left([strasse],InStr([strasse]," 5")) & Left([strasse],InStr([strasse]," 6")) &
Left([strasse],InStr([strasse]," 7")) & Left([strasse],InStr([strasse]," 8")) &
Left([strasse],InStr([strasse]," 9")) AS Ausdr1 FROM tblAdressen

UND JA: Das ist nicht optimal, man könnte noch nz() für die Strasse verwenden um Fehler auszuschliessen und man könnte weitere Probleme abfangen (die z.B. durch das einfache "&" hier entstehen), das ist mir schon klar... Der SQL-Ausdruck wird halt sehr länglich...

Hallo,
hier mal eine Lösung von mir, eben getestet, die für alle Hausnummern gehen sollte.

SELECT tbl_Kontakt.* From tbl_Kontakt WHERE (((Mid([Straße], Len(Left([Straße], InStr([Straße] & ' ',' ')-1))+1))=' '));

Der Trick ist Straße (du solltest das Feld Strasse benennen!) um ein Leerzeichen zu erweitern weil sonst InStr(Straße, ' ')-1 negativ wird.

Gruß Andreas

Funktioniert aber nicht wenn der Strassennamen selbst Leerzeichen enthält und die Hausnummer fehlt.

Andreas

ow...so wie ich es sehe, hätte ich von anfang an ein separates Feld für Hausnummer nehmen sollen. :-\

Hast du die Abfrage von mir mal getestet? Funktioniert diese?
Um wie viele Datensätze handelt es sich denn?

Ich denke dass bei meiner Query die Erfolgsquote wesentlich höher liegen sollte als bei Hondo's Methode.

Hier nochmal die angepasste Version:

SELECT Distinct(Left([Straße],InStr([Straße]," 1")) &  Left([Straße],InStr([Straße]," 2")) & 
Left([Straße],InStr([Straße]," 3")) & Left([Straße],InStr([Straße]," 4")) &
Left([Straße],InStr([Straße]," 5")) & Left([Straße],InStr([Straße]," 6")) &
Left([Straße],InStr([Straße]," 7")) & Left([Straße],InStr([Straße]," 8")) &
Left([Straße],InStr([Straße]," 9"))) AS Strasse FROM tbl_Kontakt WHERE PLZ LIKE '" & Straße.text & "*'"

Ich habe mit dieser Abfrage selbst einmal Strasse in Strasse und Hausnummer gesplittet (weil ich die Felder selbst auch irgendwann mal trennen musste).
Ich habe darmals 8000 Kundedatensätze durch die Mangel gedreht und hatte nur 73 Datensätze die ich dann nachträglich korrigieren musste.
Gib mir gerne mal Feedback, wie hoch die Trefferquote bei Dir war.

Zitatow...so wie ich es sehe, hätte ich von anfang an ein separates Feld für Hausnummer nehmen sollen.

Jup denke ich auch, wäre einfacher. Weil Hausnummern sind ja nicht immer eine zahl sonden können auch Von Bis mit oder Ohne zusätzlichen Buchstaben sein, und wenn dann noch die Schreibweise unterschiedlich eingegben wird (12a oder 12 a) dann haste aber richtig spass.

nicht alle auf ein mal  ;D

also, ich teste das gerade und mache nebenbei was noch..


HONDO
ja, habe ich aber, anschenend habe ich die falsch angewendet.
ich habe  LIKE '**' noch drangehängt aber ich bekomme nix zu sehen, die Abfrage wird durchgefürt aber ohne Ergebnisse

imp666
wenn ich das so in mein me.Straße.RowSource =  einfüge dann kommt er mit Einfürungszeichen nicht klar.

Jo hast recht muss man escapen:

"SELECT Distinct(Left([Straße],InStr([Straße],"" 1"")) &  Left([Straße],InStr([Straße],"" 2"")) & 
Left([Straße],InStr([Straße],"" 3"")) & Left([Straße],InStr([Straße],"" 4"")) &
Left([Straße],InStr([Straße],"" 5"")) & Left([Straße],InStr([Straße],"" 6"")) &
Left([Straße],InStr([Straße],"" 7"")) & Left([Straße],InStr([Straße],"" 8"")) &
Left([Straße],InStr([Straße],"" 9""))) AS Strasse FROM tbl_Kontakt WHERE PLZ LIKE '" & Straße.text & "*'"

Hallo,
meine Abfrage kannst du so direkt in den Abfrageeditor in der SQL Ansicht eingeben, nicht im VBA-Code.

@imp666: sorry, aber was hat die plz darin zu suchen?
Die Abfrage würde außerdem nciht funktionieren wenn Zahlen im Straßennamen vorkommen, und die gibts sicherlich.
Und außerdem, wie soll diese Abfrage - die eigentlich keine ist - Strassen ohne Hausnummer anzeigen?

Andreas

@Hondo:

Jaja, cut'n'paste Fehler sorry, PLZ soll hier ja wohl "[Straße]" heissen

Und dass es in einigen Fällen schief geht, da hast du recht, da hab ich glaub ich ganz am Anfang des Threads ausführliche Erläuterungen darüber geschrieben.

Dennoch habe ich mich längere Zeit (als ich mal eine Datenbank umschreiben musste, siehe dazu meine Bemerkungen oben) mit dem Splitten von Strasse + Hausnummer auseinandergesetzt und habe diverse Querys getestet und diese kommt schon relativ gut an ein sinnvolles Ergebnis heran.

Deine Abfrage geht schon bei einfachsten (häufig vorkommenden) Strassennamen schief (Hans Thoma Strasse 17). Leider ist weder das erste noch das letzte Leerzeichen ein hinreichendes Split-Kennzeichen.

Hallo,
das hatte ich ja bereits gesagt.
Es geht aber nur dann schief wenn Leerstellen im Strassennamen + Fehlender Hausnummer.

Andreas

Hallo,
ich habe für die Straße und Hausnummer getrennte Felder genommen, aber ich muss cirka 18000-20000 Datensätzen wo Str und HsNr in einem Feld sind in Access DB importieren.
imp666
SELECT Distinct(Left([Straße],InStr([Straße]," 1")) &  Left([Straße],InStr([Straße]," 2")) &  
Left([Straße],InStr([Straße]," 3")) & Left([Straße],InStr([Straße]," 4")) &
Left([Straße],InStr([Straße]," 5")) & Left([Straße],InStr([Straße]," 6")) &
Left([Straße],InStr([Straße]," 7")) & Left([Straße],InStr([Straße]," 8")) &
Left([Straße],InStr([Straße]," 9"))) AS Strasse FROM tbl_Kontakt WHERE PLZ LIKE '" & Straße.text & "*'"

hast du da wirklich "Strasse" und nicht  "Straße" gemeint? weil irgend wie komme ich nicht klar mit der query.

Hallo,

naja, kannst Du nicht selber da "Straße" hinschreiben ?


Zurück zur Ausgangsfrage:

Zitatich möchte dass Straßennamen ohne Hausnummer ausgegeben werden.
in der Tabelle Steht
z.B. Straße: Hauptstraße 12

Was soll diese SQL bedeuten?  Ist "Straße" ein Listenfeld oder ein Textfeld?

Wenn Straße ein Textfeld ist (nach dem Kriterium zu urteilen, dann hat das Textfeld keine Rowsource-Eigenschaft. Umgekehrt hat ein Listenfeld keine TEXT-Eigenschaft.

Zitatme!           Straße    .RowSource = "SELECT distinct (Straße) FROM tbl_Kontakt WHERE Straße LIKE '" &                  Straße               .text & "*'"

Vorschlag:

Kopier die folgenden Funktion in ein Standard-Modul:

Public Function fktStrassenSplit(Strasse) As String

Dim pos As Long, i As Long
If IsNull(Strasse) Then Exit Function

For i = 1 To Len(Strasse)
If IsNumeric(Mid(Strasse, i, 1)) Then
pos = i
Exit For
End If
Next
If pos = 0 Then
fktStrassenSplit = Strasse
Else
fktStrassenSplit = Trim(Left(Strasse, pos - 1))
End If
End Function


und eine Ereignisprozedur:


Sub txtStrasse_Change()     ' txtStrasse ist ein Form-Textfeld, in das ein Strassenname (teilweise) eingegeben wird
Me!Straße.Rowsource = "SELECT distinct fktStrassenSplit([Straße]) FROM tbl_Kontakt WHERE [Straße] LIKE '" &  Me!txtStraße.Text & "*'"
End Sub

Hallo DF6GL,

Zitatnaja, kannst Du nicht selber da "Straße" hinschreiben ?

habe doch gemacht....geht nicht..

ZitatWas soll diese SQL bedeuten?  Ist "Straße" ein Listenfeld oder ein Textfeld?....

keins von beiden..."Sie habe aber noch Telefonjoker, wen wollen Sie anrufen ?"  ;D ist ein Kombifeld mit .RowSource und. Text Eigenschaft

Dein Vorschlag funktioniert bei mir. Danke.
so wie ich das  verstehe..sobald eine Zahl vorkommt wird es abgetrennt.