Hallo Zusammmen,
ich habe das ca. 400.000 Datensätze habe und enthalten in einer Spalte Postleitzahlen. Diese Postleitzahlen möchte in bestimmte Regionen unterteilen. Ich möchte also eine Abfrage starten bei der zum Beispiel die Postleitzahlen zwichen 14000 und 16000 die Region 1 zugewiesen bekommt usw.
Ich habe das ganze schon einmal versucht in VBA zu schreiben. Wenn ich diese Funktion dann in die Abfrage einbinde, gibt diese kein Wert zurück. Anbei mein Code. Ich habe das ganze nur eingegrenzt, dass alle PLZ über 16000 die Region 2 und alle unter 16000 die Region 1 sind.
Vielleicht kann mir jemand helfen. Vielen Dank :)
Public Function PlzZuweisen(EmpfaengerPLZ As Variant) As Variant
If IsNull(EmpfaengerPLZ) Then Exit Function
Select Case EmpfaengerPLZ
Case Is < 16000 = 1
Case Is > 16000 = 2
End Select
End Function
Hallo,
Du musst natürlich einen Rückgabewert zuweisen:
Public Function PlzZuweisen(EmpfaengerPLZ As String) As Integer
If IsNull(EmpfaengerPLZ) Then Exit Function
Select Case EmpfaengerPLZ
Case Is < 16000
PlzZuweisen = 1
Case Is > 16000
PlzZuweisen = 2
End Select
End Function
Außerdem würde ich Übergabewert und Rückgabewert andere Datentypen zuweisen.
Mit > und < schließt Du aber die 16000 direkt aus. Du musst also noch eine Zuweisung ändern, entweder <= oder >=
Hallo,
kleine Hinweise:
Wenn "EmpfaengerPLZ" den Datentyp String erhält, dann greift die ISNull()-Funktion nicht mehr...
Wenn die PLZ in der Tabelle vom Datentyp String (TEXT) ist, dann sollte im Select-Block auch mit einem String verglichen werden.
wenn nur Bereiche betroffen sind (im Beispiel allerdings nicht der Fall) , also nicht alle möglichen PLZ durch eine Case-Bedingung abgedeckt werden, dann sollte Case Else zur Sicherheit/Klarheit eingebaut werden.
Public Function PlzZuweisen(EmpfaengerPLZ As String) As Long '(möglichst Integer vermeiden)
If IsNull(EmpfaengerPLZ) Then Exit Function
Select Case EmpfaengerPLZ
Case Is < "16000"
PlzZuweisen = 1
Case Is > "16000"
PlzZuweisen = 2
Case Else
PlzZuweisen = 3
End Select
End Function
Hallo,
danke für die Korrektur, hätte doch nicht so schnell antworten sollen. ;D
Integer würde ich hier aber bedenkenlos verwenden, es wird ja wohl kaum mehr als 32.767 Gruppen geben.
Aber auf die 2 Byte für Long kommt es natürlich auch nicht an.
Es klappt. Ich habe zwei Bücher über Access Programmierung durchgelesen bzw. nachgeschlagen und ich habe nichts gefunden. Ich war total verzweifelt. Vielen Dank für Eure Hilfe. :)
Habe gleich noch zwei Fragen dazu. Kann ich auch Zwischenwerte abrufen. Und zwar will ich dass alle Postleitzahlen zwischen 16000 und 18000 den Wert Region =2 erhalten. Kann man das so definieren: case is =>16000 <=18000?
Meine zweite Fragen lautet. Kann man in dieser Funktion einen zweiten Angeben so dass alle in der Region 2 den Wert A-Kunde oder einen bestimmten Eurobetrag erhalten?
Hallo,
Case "16000" to "18000"
Die 2. Frage versteh ich nicht.
@Klaus:
Bei "Long" ist mein "Hintergedanke" nicht in erster Linie der des Wertebereiches, eher der internen Speicherverwaltung von Windows und des VBA-Kompilers wegen deren Reservierung eines Ganzwortes auch bei "kleineren" Zahlentypen. Wie auch immer, ansonsten sollte man nie nie sagen ;)
Schnell übernimmt man unbedacht die Integer-Deklaration bei anderen Variablen, die den Wertebereich überschreiten können und schon wächst die Beule.... ;D
Also die Funktion soll die Postleitzahlen abfragen und dann die Regionen zuweisen. Das kann Sie jetzt ja. Dann soll sie für die Region 1 rechnen (wenn region =1 dann rechne 15% * 25 Euro) und für Region 2 10% und Region 3 5%. Wie könnte der VCod dafür aussehen oder muss man dafür eine neue Funktion erstellen? Danke schonmal vorab.
Hallo pahiti78,
ZitatDann soll sie für die Region 1 rechnen (wenn region =1 dann rechne 15% * 25 Euro) und für Region 2 10% und Region 3 5%. Wie könnte der VCod dafür aussehen oder muss man dafür eine neue Funktion erstellen? Danke schonmal vorab.
Das musst Du in einem zusätzlichen, berechneten Feld in Deiner Abfrage machen; - diese Function gibt Dir ja nur einen Wert für das Feld "Region" zurück. Schreibst Du also eine zweite Function, der Du den eben berechneten Wert übergibst, und die Dir dann wieder genau einen Wert zurück gibt.
hth
gruss ekkehard
Hallo, ich mal wieder. Wollte jetzt die PLZ einbinden. Es haut allerings bei den Zahlenbereichen nicht hin. Die Abfrage gibt lediglich den Wert 1 zurück. Danke schonmal vorab.
Public Function Gebiet(EmpfaengerPLZ As String) As Long '(möglichst Integer vermeiden)
If IsNull(EmpfaengerPLZ) Then Exit Function
Select Case EmpfaengerPLZ 'Wähle EmpfaengerPLZ'
Case "1000 to 3253" 'alle Postleitzahlen zwischen 1000 und 3253 entsprechen dem Gebiet 49'
Gebiet = 49
Case "4100 to 4200"
Gebiet = 9 'alle PLZ zwischen 4100 und 4200 entsprechen den Gebiet 09'
Case Else
Gebiet = 1
End Select
End Function
Vergesst es. Ich habe selbst schon bemerkt, dass lediglich die Anführungszeichen fehlen. Sorry 8)
Hallo Zusammen,
ich muss doch noch etwas loswerden. Bei den Gebieten, welche den Postleitzahlen zugeordent werden sollen, handelt es sich um mehrere hundert stück. Diese sind bereits in einer Exceltabelle erfasst wurden. Ist es möglich diese Daten in einer Accesstabelle zu integrieren. Zum Beispiel Tabelle A mit den Spalten PLZvon, PLZbis, Gebiet. Die Abfarge sollte dann die einzelnen PLZ diesen Gebieten zuordnen. Zum Beispiel lautet dann der erste Datensatz in Tabelle A: Plzvon =1000, PLZbis=3265, Gebiet 1. Die Abfrage muss dann meine PLZ (3263) dem Gebiet 1 zuordnen. Ist soetwas möglich und wenn ja wie könnte so einen Funktion aussehen?
Hallo,
Select b.plz, a.PLZVon, a.PLZBis, a.Gebiet
From tblPLZGebiete as a
Inner join tblPLZ as b
On ( b.Plz between a.Plzvon and a.Plzbis)
Müssen diese Anweisungen dann in die Case Funktion eingebaut werden?
Hallo,
nein, das ist die SQL einer Abfrage, kein VBA-Code....
Öffne den Abfrageentwurf und kopiere dort unter Ansicht/SQL-Ansicht diesen SQL-String hinein. Mach noch die Namens-Anpassungen an Deine Tabellen/Felder und führ die Abfrage aus.
Soory, aber es klappt nicht. Ich habe eine Tabelle (tblPLZ)in welcher die Postleitzahlen stehen und diese sind den entsprechenden Gebieten zugeordnet. Also PLZvon, PLZbis, Gebiet.
In einer zweiten tabelle (tblKundendaten) sind die Kundendaten. Dort ist eine Spalte, welche die EmpfaengerPLZ ausgibt. Meine SQL Anweisung sieht jetzt so aus. Ich weiß allerings nicht, welchen Wert ich dem Parameter b.PLZ zuordnen soll.
Select b.plz, tblplz.PLZVon, tblplz.PLZBis, tblplz.Gebiet
From tblPLZ as a
Inner join tblkundendaten as b
On ( b.Plz between plz.Plzvon and plz.Plzbis)
Ich möchte mich erstmal bei allen bedanken. Bin in diversen Sachverhalten schon viel weiter als noch vor 5 Tagen. Meine SQLAnweisung haut dennoch nicht so richtig hin.
Habe jetzt schonmal werte, welche die Abfrage zurückgibt. Allerdings gibt diese pro PLZ zwei Gebiete zurück. Zum Beispiel wird die PLZ 6449 dem PLZBereich zwischen 6350 und 6509 und dem Bereich 63872 und 64589 zugeordnet. Also in der Tablle Kundendaten sind einzelne Postleitzahlen und in der Tabelle PLZ mit den Spalten (PLZvon, PLZbis, Gebiet) ist die Gebietsaufteilung.
Meine SQL Anweisung sieht folgendermaßen aus: Vielleicht hat noch jemand einen Tipp für die Anweisung. Übrigens sind beide PLZ-Felder in beiden Bereichen als string definiert.
SELECT tblkundendaten.plz, tblplz.plzvon, tblplz.plzbis, tblplz.gebiet
FROM tblplz INNER JOIN tblkundendaten ON tblkundendaten.plz between tblplz.plzvon and tblplz.plzbis;
Hallo,
versuch mal folgendermaßen:
SELECT tblkundendaten.plz, tblplz.plzvon, tblplz.plzbis, tblplz.gebiet
FROM tblplz INNER JOIN tblkundendaten ON (tblkundendaten.plz between tblplz.plzvon and tblplz.plzbis)
WHERE len(tblkundendaten.plz) = len(tblplz.plzvon)
HTH
Hallo,
mhmm, das passiert tatsächlich.. und konnte das nachvollziehen.
Allerdings ist mit der Hintergrund dafür jetzt nicht ganz klar. Wahrscheinlich vergleicht der Between/And-Operator die Ziffern der PLZ Zeichen für Zeichen miteinander von links nach rechts durchlaufend und ignoriert dadurch die Tatsache der unterschiedlichen Ziffernanzahl.
Mit der Umwandlung von Text nach Zahl klappt es jedoch (auch):
SELECT tblkundendaten.plz, tblplz.plzvon, tblplz.plzbis, tblplz.gebiet
FROM tblplz INNER JOIN tblkundendaten ON ( val(tblkundendaten.plz) between val(tblplz.plzvon) and val(tblplz.plzbis));
Wobei aber die Frage bleibt, weshalb überhaupt 4-stellige PLZ auftauchen, sofern es sich um deutsches Gebiet handelt.
Guten Morgen, ...
der Längenvergleich wie in meiner Antwort angeführt beseitigt das Problem ebenfalls - auf Textbasis.
Nachdem die Postleitzahlen in Textfeldern (Standard) gespeichert sind könnte irgendwann mal bei der Konvertierung ein Fehler entstehen.
Stelle mir grad vor wenn dann D-85764 als PLZ eingetragen werden würde - die Tabellenfelder auf Long umzustellen bringts auch nicht da dies ebenfalls zu Fehlern führen kann
Wenn auf Formularebene nicht festgelegt ist, dass nur Ziffern und diese nur in einem bestimmten Umfang (5) eingetragen werden, dürfen krachts sowieso früher oder später.
Am Rande bemerkt ... würde ich die Tabellen umstellen und bei den Kundendaten den PK der tblPLZ erfassen
Hallo,
"der Längenvergleich wie in meiner Antwort angeführt beseitigt das Problem ebenfalls - auf Textbasis."
genau das wollte ich mit dem geklammerten "auch" sagen :D ;)
Mich würde aber weiterhin interessieren, wieso es 4- und 5-stellige PLZ geben kann (natürlich länderbezogen.. 8) )
Hallo Franz, guten Morgen,
wollte mit meinem ersten Satz eigentlich nicht deine Antwort nachkommentieren sondern den Rest meiner Antwort einleiten ... ;) :D ;D
Zitat...wieso es 4- und 5-stellige PLZ geben kann
Sieht nach einem Unternehmen in der Transport- oder Zustellbranche aus :-\
Also es haut jetzt auch bei mir hin. Nochmals Dankeschön. Bei den viertstelligen PLZ handelt es sich um jene, welche mit einer Null beginnen. Allerdings werden die Daten aus einem anderen System bereitgestellt und die NUll wird nicht mit übertragen. Man kann das Abfrageformat auch nicht ändern.
Ich habe da noch ein Problem. Und zwar habe ich in einer Tabelle Länderkennzeichen für Absneder und Empfänager vogegeben und meine Case-Funktion sollte mir ausgeben, bei welchen Aktionen es sich um Exporte, um Nationale Aktionen und Importe handelt. Problem ist weiterhin, dass bei manchen Absendern das LKZ nicht vorgegeben ist. Dann sollte die Funktion diese Aktionen automatisch als "D" wie Deutschland betrachten.Anbei noch diese Funktion. Vielleicht seht meinen Fehler. (ALKZ=AbsenderLKZ und ELKZ =EmpfaengerLänderkennzeichen)
Public Function Verkehrstraeger(ALKZ As String, ELKZ As String) As String
Select Case ALKZ & ELKZ 'Wähle Absender Länderkennzeichen und Empfänger Länderkennzeichen"'
Case Is = ALKZ = "D", ELKZ = "D" 'Wenn ALKZ und ELKZ = Deutschland dann National'
Verkehrstraeger = "National"
Case Is = ALKZ = "D", ELKZ <> "D" 'Wenn ALKZ = Deutschland und ELKZ nicht Deutschland dann Export'
Verkehrstraeger = "Export"
Case Is = ALKZ <> "D", ELKZ = "D" 'Wenn ALKZ nicht Deutschland und ELKZ =Deutschland dann Import'
Verkehrstraeger = "Import"
Case Is = Empty ' Wenn kein LKZ angeben immer National widergeben'
Verkehrstraeger = "National"
End Select
End Function
Hallo,
Public Function Verkehrstraeger(ALKZ As String, ELKZ As String) As String
Select Case ALKZ 'Wähle Absender Länderkennzeichen
Case "D"
If ELKZ = "D" Then 'Wenn ALKZ und ELKZ = Deutschland dann National
Verkehrstraeger = "National"
Else 'Wenn ALKZ = D und ELKZ nicht D
Verkehrstraeger = "Export"
End If
Case Else 'Wenn ALKZ nicht D
If ELKZ = "D" Then 'Wenn ALKZ nicht D und ELKZ = D
Verkehrstraeger = "Import"
Else
If ALKZ = "" And ELKZ = "" Then 'Wenn ALKZ = leer und ELKZ = leer
Verkehrstraeger = "National"
End If
End If
End Select
End Function
Sollte das gewünschte Ergebnis liefern können ...
Super, danke für die schnelle Antwort. Ich habe mit der SQl Abfrage doch ein Problem. Und zwar sind in meiner Abfrage schon diverse SQLAnweisungen gelaufen. Wenn ich die neue einbinden möchte, meckert Access rum, da Zeichen nach Ende der SQL Anweisung gefunden und markiert dann den Select Befehl. (Ganz oben in meiner Anweisung steht schonmal Select da Access sämtliche Tabellen bereits in meiner Abfrage formuliert hat) Muss ich eine seperate Abfrage starten oder ist mittels eines "Kombinierungsbefehls oder Ähnlichen" möglich diese SQL Abfrage einzubinden.
Hallo,
"..sind in meiner Abfrage schon diverse SQLAnweisungen gelaufen"
was meinst Du damit? In einer Abfrage darf nur ein einziges Statement stehen, als nur z. B. ein "Select" oder "Insert" (Mehrere "Select" können allerdings als Unterabfrage auftauchen.....)
Mehrere Abfragen können nur nacheinander ausgeführt werden.
Ausnahme bildet da eine Union-Abfrage, in der mehrere Abfragen ("Select * from Tabelle") mit gleicher Stuktur , d. h. mehrere Abfrageergebnisse hintereinander dargestellt werden können.
Hallo,
Es geht doch darum, die obige Function als Feld in der Abfrage zu verwenden ... in der Form VT:Verkehrstraeger(ALKZ, ELKZ)
Somit sollte dann das erwartete Ergebnis der Abfrage ein Feld namens 'VT' enthalten in dem dann 'Import', 'Export' oder 'National' auftauchen
sofern 'ALKZ und 'ELKZ' als Felder mit diesem Namen in der Abfrage vorhanden sind.
Zitat... Ganz oben in meiner Anweisung steht schonmal Select da Access ...
??? ??? ???
Kannst du bitte mal die SQL deiner angemeckerten Abfrage posten oder die beteiligten Tabellen + Abfrage in einer Beispieldatei hochladen?
Hallo Database,
anbei meine SQL Anweisung. Der obere Bereich bestand bereits, da die untere Anweisung in eine bestehende Abfarge eingebunden werden soll. Der unterre Bereich ist die neue Anweisung für die PLZ.
SELECT tblKundendaten.AUFTRAG_DATUM, tblKundendaten.VERS_NAME, tblKundendaten.VERS_LKZ, tblKundendaten.VERS_PLZ, tblKundendaten.VERS_ORT, tblKundendaten.EMPF_NAME, tblKundendaten.EMPF_LKZ, tblKundendaten.EMPF_PLZ, tblKundendaten.EMPF_ORT, tblPLZ.PLZvon, tblPLZ.PLZbis, tblPLZ.Gebiet
FROM tblKundendaten, tblPLZ;
SELECT tblkundendaten.empf_plz, tblplz.plzvon, tblplz.plzbis, tblplz.gebiet
FROM tblplz INNER JOIN tblkundendaten ON ( val(tblkundendaten.empf_plz) between val(tblplz.plzvon) and val(tblplz.plzbis));
Hallo,
hast Du meine Hinweise nicht gelesen?
Es darf in einer solchen Abfrage nur EIN Select -Statement auftreten. Lösch also den oberen Teil , so dass NUR
SELECT tblkundendaten.empf_plz, tblplz.plzvon, tblplz.plzbis, tblplz.gebiet
FROM tblplz INNER JOIN tblkundendaten ON ( val(tblkundendaten.empf_plz) between val(tblplz.plzvon) and val(tblplz.plzbis));
in der SQL-Ansicht der Abfrage steht.
"da die untere Anweisung in eine bestehende Abfarge eingebunden werden soll. "
wer sagt/will denn das?
Hallo,
bzw:
SELECT tblKundendaten.AUFTRAG_DATUM, tblKundendaten.VERS_NAME, tblKundendaten.VERS_LKZ, tblKundendaten.VERS_PLZ, tblKundendaten.VERS_ORT, tblKundendaten.EMPF_NAME, tblKundendaten.EMPF_LKZ, tblKundendaten.EMPF_PLZ, tblKundendaten.EMPF_ORT, tblPLZ.PLZvon, tblPLZ.PLZbis, tblPLZ.Gebiet
FROM tblKundendaten, tblPLZ;
SELECT tblkundendaten.empf_plz, tblplz.plzvon, tblplz.plzbis, tblplz.gebiet
FROM tblplz INNER JOIN tblkundendaten ON ( val(tblkundendaten.empf_plz) between val(tblplz.plzvon) and val(tblplz.plzbis));
... OHNE die durchgestrichenen Zeilen in den SQL-Bereich einer NEUEN Abfrage KOPIEREN
Du benötigst die Zeile die mit FROM beginnt sonst funktioniert die Auswahl mit den PLZ nicht richtig.
ACHTUNG - du kannst die Abfrage NICHT im Entwurfmodus öffnen, da Access die JOIN-Bedingung nicht darstellen kann!!
Also die SQL in den SQL-Bereich kopieren und die Abfrage speichern - danach kann sie beliebig oft aufgerufen werden.
danke