instr op adres

Status
Niet open voor verdere reacties.

Trientje

Gebruiker
Lid geworden
9 dec 2012
Berichten
60
ik heb een veld straat waarin "straatnaam nr , postbus" in staat.
bv
  • LAAGEIND 62
  • JOZEF DE BOMSTRAAT 69
  • NOORDERLAAN 375, RP
  • EDMOND DE COUSSEMAKERSTRAAT 18, DN01
nu is het de bedoeling dat ik 3 aparte velden krijg voor
straatnaam;huisnr;toevoeging zonder vreemde tekens.

STRAAT_CLEAN...................................HUISNR...TOEV.
LAAGEIND.......................................... 62
JOZEF DE BOMSTRAAT..........................69
NOORDERLAAN...................................375..........RP
EDMOND DE COUSSEMAKERSTRAAT......18............DN01

kan ik instr() gebruiken met meerdere reeksvergelijkingen)

in excel werd deze formule gebruikt om de positie van het eerste cijfer te vinden
MIN(ALS(ISGETAL(VIND.ALLES({1\2\3\4\5\6\7\8\9\0};[@Straat];3));VIND.ALLES({1\2\3\4\5\6\7\8\9\0};[@Straat];3)))

alvast bedankt voor de nodige input!
 
Laatst bewerkt:
Access is (gelukkig maar :)) geen Excel, en dit soort ingewikkelde formules is dan ook niet te maken. Maar met een eigen functie is het wel te doen, en vermoedelijk een heel stuk simpeler.
Ik zou, als de syntax altijd is zoals je hem beschrijft, een functie maakt die m.b.v. twee SPLIT functies de basisstring in twee array’s zet. De eerste SPLIT gebruik je om de toevoeging te elimineren (SPLIT op de komma+spatie). Dan heb je een matrix met twee waarden, of met één (geen toevoeging). De tweede SPLIT is dan op spatie, om het adres te splitsen. Die matrix kan veel meer waarden bevatten, afhankelijk van hoeveel spaties er in het adres zitten. Is ook niet zo interessant, want je weet één ding zeker: in de bovenste (UBound) positie zit het nummer. Je hoeft dus, om de straatnaam te vinden, alleen alle posities weer samen te voegen minus de hoogste, en dan heb je de straatnaam weer compleet.
Alle elementen zet je vervolgens in een apart veld.
 
dag octafish,

bedankt voor je reactive. Maar ik krijg het niet voor elkaar.
ik heb deze formule
Code:
SELECT split([straat],",",1) AS Expr1 FROM [DUMP MAATREGELEN];
alvast ingegeven als test maar krijg een foutmelding dat de expressive niet gedefinieerd is 15-02-2019 14-13-39.png
de versie is : acces 2016
 
Laatst bewerkt:
De (tegen)vraag is: kom je daar zelf uit, of heb je een voorzetje nodig?
 
voorzetje aub ? :confused: ik probeer zoveel mogelijk mijn plan te trekken met vanalles op te zoeken maar ik kom er niet uit.
heb zelfs andere voorbeelden uitgeprobeerd en krijg steeds dezelfde melding :(
de toev. is al in orde misschien niet de meest orthodoxe manier maar het resultaat is er.
Code:
IIf(InStr(1;[STRAAT];", ")>0;Right([straat];(Len([straat])-InStr(1;[straat];", ")-1));Null)

nu nog de straatnaam en het nummer scheiden...
 
Laatst bewerkt:
Je blijft nog even op het Excel pad, zie ik :). Zoals ik in mijn eerste antwoord zei: je hebt een functie nodig om het (simpel) op te lossen, geen formule. Ik zei niet voor niets dat het met formules niet is te maken! Zonder voorbeeld db wordt het een beetje lastig, omdat ik niet precies weet wat je nu wilt bereiken. Access is een programma dat vanuit processen werkt, Excel is meer van: effe iets doen in een celletje. Die aanpak werkt zelden (goed) in Access. Vanuit het proces gedacht, is dus de vraag: wat wil je eigenlijk?
 
Klopt... ik ben een autodidact in excel en omdat dit beperkingen met zich meebracht heb ik me op het accespad begeven. Al doende leer je toch? Soms..... :shocked:.
Als je een site of wat dan ook weet waar ik meer over de functies kan leren en de excelmind achter me kan laten, mag je dit altijd doorgeven.
Visual basic is me vreemd, zover dat ik macro s wel kan aanpassen maar dat is weer een heel ander verhaal.

Er is dus een veld waarin straatnaam huisnummer, toevoeging in staan.
Wat ik nu wil is dat die 3 in aparte velden komen te staan. Om ze daarna te kunnen relateren met een tabel met dezelfde indeling.

Toevoeging staat na de komma , dat komt in een veld
Huisnummer is het getal dat voor de komma staat. Komt in een veld
Wat daarvoor staat is de straatnaam en moet ook in een veld.
 
Laatst bewerkt:
... heb ik me op het accespad begeven. Al doende leer je toch? Soms..... :shocked:.
Acess is niet bepaald een programma dat je 'al doende leert', net zomin als je een open hartoperatie 'al doende' leert. Terwijl je toch echt wel een mes kan vasthouden :). Databases bouwen is, ik zeg het nog maar een keer, een vak waarvoor je je moet verdiepen in de materie. Dus echt wat anders als Excel :).

Dat gezegd hebbende: je hebt nog steeds weinig gezegd over het proces, anders dan dat je één veld in 3 velden wilt hebben. De vraag blijft dus: waar komt dat ene veld vandaan? Laat ik voor het gemak zeggen dat je een import bestand krijgt met dus dat één veld. En dat je een tabel hebt waar (o.a.) dat veld in moet, met andere gegevens, maar dan gesplitst in drie velden. Dan is dus de vraag: hoe kan ik een import bestand zodanig bewerken dat ik die tabel kan inlezen in mijn klantentabel? Kijk, dan hebben we het over een werkproces waarvoor je een oplossing kan bedenken. Er zijn er meerdere, maar ik denk dat voor jou een Toevoegquery dan het handigst is. In die query gebruik je dan onderstaande functies om het gesplitste veld toe te wijzen aan de drie aangewezen velden. Ik gebruik voor de demo even een Selectiequery, maar daar kun je makkelijk een toevoegquery van maken. Eerst de 3 functies:

Code:
Function StraatNaam(Adres As String) As String
Dim a As Variant, b As Variant, i As Integer, tmp As String
    a = Split(Adres, ", ")
    If UBound(a) > LBound(a) Then
        b = Split(a(LBound(a)), " ")
    Else
        b = Split(Adres, " ")
    End If
    For i = LBound(b) To UBound(b) - 1
        If tmp <> "" Then tmp = tmp & " "
        tmp = tmp & b(i)
    Next i
    StraatNaam = tmp
End Function

Code:
Function StraatNummer(Adres As String) As Integer
Dim a As Variant, b As Variant, i As Integer, tmp As String
    a = Split(Adres, ", ")
    If UBound(a) > LBound(a) Then
        b = Split(a(LBound(a)), " ")
    Else
        b = Split(Adres, " ")
    End If
    StraatNummer = b(UBound(b))
End Function

Code:
Function StraatNummerToev(Adres As String) As String
Dim a As Variant
    a = Split(Adres, ", ")
    If UBound(a) > LBound(a) Then
        StraatNummerToev = a(UBound(a))
    End If
End Function

Die zet je in een nieuwe module, als je nog geen module hebt.
In de query zet je dan dit:


PHP:
SELECT STRAAT_CLEAN, StraatNaam([STRAAT_CLEAN]) AS Straat, StraatNummer([STRAAT_CLEAN]) AS Nummer, StraatNummerToev([STRAAT_CLEAN]) AS Toev
FROM Blad1

Uiteraard nog wel de veldnamen etc. aanpassen. De functie blijft gewoon werken, zolang je ze niet hernoemt :).
 
Ik begrijp je reactie voor 100% en dat is inderdaad het frustrerende. Roeien met de riemen die je hebt. En zo probeer je je plan te trekken. En ik zou het zo graag onder de knie krijgen.... maar je moet de kans krijgen.
De interesse is er.... daar mankeert het niet aan!
Maar inderdaad het zijn verschillende excelbestanden van 2 bedrijven die fusioneren. Zo zijn er 9 verschillende bestanden sommige bestanden hebben 20000 records en die hebben ze via formules uitgebreid om gelijke velden te krijgen en vlookups er op los te laten en daarna een samengestelde cel met daarbij de gegevens met hetzelfde adresveld. Daarop een macro loslaten , is chaos en crash gegarandeerd. Dus daarom acces en de 9 bestanden waarvan ik 5 tabellen heb kunnen maken dmv verschillende toevoegquerys En in 2 tabellen heb je adressen met verschillende lay-out. En daar zit nu net het probleem. De lay-out of uitsplitsing moet hetzelfde zijn om een relatie te kunnen leggen. Zodat ik via een query die gegevens kan combineren.

Ik wil je alvast bedanken voor je geduld.
Prettige avond
 
Laatst bewerkt:
M.b.v. de functies en de query kun je in ieder geval de gegevens op de correcte manier splitsen, zoals je wellicht al getest hebt. Ik zou sowieso alle gelijke entiteiten (persoonsgegevens zijn één entiteit) in één tabel onderbrengen, dan heb je ook geen relatie nodig. En dat gaat prima met een Toevoegquery.
 
:thumb: :thumb: :thumb:
de selectiequery werkt maar als ik er een toevoegquery of nieuwe tabel wil van maken krijg ik 24 dezelfde foutmeldingen
2 x aantal velden adresbestand

Code:
INSERT INTO [EANDIS DUMP MAATREGELEN] ( straat_clean, Huisnr, Toev, adres )
SELECT [Maatregelen ANTWERPEN].*, StraatNaam([STRAAT]) AS StraatX, StraatNummer([STRAAT]) AS Nummer, StraatNummerToev([STRAAT]) AS Toev, Trim([straatx]) & " " & Trim([nummer]) & IIf([toev] Is Not Null," " & [toev] & ", ",", ") & [PC].[Postcode Hoofdgemeente] & " " & [pc].[Hoofdgemeente] AS Expr1
FROM [Maatregelen ANTWERPEN] LEFT JOIN PC ON ([Maatregelen ANTWERPEN].Postcode = PC.[postcode-txt]) AND ([Maatregelen ANTWERPEN].Plaats = PC.Gemeente);

foutmelding :
fout 13 tijdens uitvoering

typen komen niet met elkaar overeen
 

Bijlagen

  • PC.xlsx
    108,7 KB · Weergaven: 120
  • AN_Dump_Maatregelen.xlsx
    1,4 MB · Weergaven: 35
Laatst bewerkt:
Ik snap niet waarom je een veld [adres] hebt gemaakt; het is alleen maar het resultaat van een samenvoeging van de andere velden. Dit veld is dus totaal overbodig. Dit soort dingen doe je eigenlijk nooit in een database, omdat je de normalisering ervan bij het groot vuil zet. En wat je nou uitgerekend probeert te doen, is om je Db zo correct mogelijk te normaliseren :).
 
het veld adres werd gemaakt omdat dit in de andere tabel met dezelfde indeling standaard staat, maar deze heb ik er nu uitgehaald, ik begrijp de redenering. maar ook zonder dat veld krijg ik nog dezelfde fout 12x (enkel bij uitvoering, niet bij weergave) als ik een andere query gebruik dan selectie. :(.
de tabel pc is een gewone tabel in acces welke ik voor het gemak even geexporteerd heb naar excel om te kunnen toevoegen als bijlage. het andere bestand is het effectieve xls bestand welke gekoppeld is aan acces
 
Laatst bewerkt:
Ik zal er vandaag even naar kijken. Maar ik zie wel een fout in je query, dus die kan je er alvast uithalen, en kijken of het dan wel lukt.
Code:
INSERT INTO [EANDIS DUMP MAATREGELEN] ( straat_clean, Huisnr, Toev, adres )
SELECT [Maatregelen ANTWERPEN].*
Bij een Toevoegquery ga je, vanuit een bron (tabel) of waarden (vaste tekst of formules bijvoorbeeld) een tabel vullen. Daarbij moet je altijd aangeven welke velden gevuld moeten worden (eerste regel). Vervolgens geef je dus aan waarmee je die velden gaat vullen. Dat moet heel specifiek worden aangegeven. Als je 4 vulden wilt vullen, dan mag je ook maar 4 waarden toevoegen, en geen 5 of 3. Daarnaast moeten die waarden ook nog eens in de juiste volgorde staan.

In jouw query zouden deze waarden wel gaan: “Bachtstraat”, 12, “B”, “Bachstraat 12B”. Maar deze niet: “Bachstraat 12B”, “Bachtstraat”, 12, “B”. En dat komt omdat het tweede veld een getalveld is, en dus een getal moet krijgen, en geen tekst.
Kijken we nu naar jouw code, waarin je dus 4 velden wilt vullen, dan zien we a) dat je veel meer velden vult, en b) dat je (ik althans) geen enkel idee hebt wát je in welke volgorde gaat toevoegen. En dat komt dus alleen maar door dit: [Maatregelen ANTWERPEN].*. Al staat er maar één veld in die tabel, dan gaat het al fout, omdat daarmee de volgorde niet meer klopt, en het aantal velden ook niet meer. Dus dat stuk moet er absoluut uit!

Sowieso is het heel onverstandig om met .* te werken in een query, want je hebt geen idee welke velden je terug krijgt. Maar in een Toevoegquery mag het dus echt niet.
 
ik heb het eens helemaal gestript tot de 3 gemaakte velden
ook dan krijg ik de foutmelding 12x
uiteindelijk doet hij toch de toevoeging of maakt hij een nieuw bestand.
Code:
Function StraatNummer(Adres As String) As Integer
Dim a As Variant, b As Variant, i As Integer, tmp As String
    a = Split(Adres, ", ")
    If UBound(a) > LBound(a) Then
        b = Split(a(LBound(a)), " ")
    Else
        b = Split(Adres, " ")
    End If
[COLOR="#FF0000"]   StraatNummer = b(UBound(b))[/COLOR]
End Function
als ik foutmelding selecteer dan stopt hij op de rode tekst.
Als ik
Code:
Nummer :StraatNummer([STRAAT])
uit de query haal krijg ik geen foutmelding bij het toevoegen ook niet bij de "niet-gestripte" query.
 
Kijk eens wat er op dat moment in Adres staat (muis boven Adres houden). Je kunt ook het venster <Lokale variabelen> aanzetten, en kijken wat er op dat moment in de variabelen a en b staat. Eer gaat zo te zien iets fout met de inhoud van het veld, ik vermoed dat er geen getal in staat.
 
ik zie dat mijn laatst reactie niet geplaatst is .
ik had gisteren gevonden waar het probleem zat.
het is door de query op een andere tabel te laten draaien dat ik het opmerkte. daar waren er 7 foutmeldingen
het was inderdaad in volgende adressen:
18-02-2019 10-40-05.png
 

Bijlagen

  • foutmelding 13.xlsx
    10,9 KB · Weergaven: 37
De vraag is nu: Hoe ga je dit oplossen? :). Bij dit soort aantallen denk ik: even handmatig de tabel aanpassen, dat is veel minder werk dan het in de code oplossen. Sowieso zou ik van het veld Huisnummer een tekstveld maken, zodat je geen problemen hebt met huisnummers die meerdere nummers combineren. Wat jij een ‘toevoeging’ noemt, is dus eigenlijk geen huisnummertoevoeging, maar een ander type. Om de huisnummers (met toevoeging) dan correct in te lezen, moet je de spaties tussen nummer en letter verwijderen.
 
kan ik de foutmelding op één of andere manier uitschakelen?

heb wel nog volgende module gevonden
Code:
Option Compare Database
Option Explicit

Function ZoekStraat(Adres As Variant) As String
Dim Teller As Byte
Dim AantalTekens As Byte
Dim StartLtstNum As Byte
Dim VorigTeken As String
Dim Teken As String

If IsNull(Adres) Then
    AantalTekens = 0
Else
    AantalTekens = Len(Adres)
End If

StartLtstNum = 0
VorigTeken = "X"

If AantalTekens > 0 Then
    For Teller = 1 To AantalTekens
        Teken = Mid(Adres, Teller, 1)
        If IsNumeric(Teken) And Not IsNumeric(VorigTeken) Then StartLtstNum = Teller
        VorigTeken = Teken
    Next
    
    If StartLtstNum > 1 Then
        ZoekStraat = Trim(Mid(Adres, 1, StartLtstNum - 1))
    Else
        ZoekStraat = Adres
    End If
Else
    ZoekStraat = ""
End If
End Function

Function ZoekHuisnummer(Adres As Variant) As Variant
Dim Teller As Byte
Dim AantalTekens As Byte
Dim StartLtstNum As Byte
Dim StartLtstTxt As Byte
Dim VorigTeken As String
Dim Teken As String

If IsNull(Adres) Then
    AantalTekens = 0
Else
    AantalTekens = Len(Adres)
End If

StartLtstNum = 0
StartLtstTxt = 0
VorigTeken = "X"

If AantalTekens > 0 Then
    For Teller = 1 To AantalTekens
        Teken = Mid(Adres, Teller, 1)
        If IsNumeric(Teken) And Not IsNumeric(VorigTeken) Then StartLtstNum = Teller
        If Not IsNumeric(Teken) And IsNumeric(VorigTeken) Then StartLtstTxt = Teller
        VorigTeken = Teken
    Next
    
    If StartLtstNum > 1 Then
        If StartLtstTxt > StartLtstNum Then
            ZoekHuisnummer = Mid(Adres, StartLtstNum, StartLtstTxt - StartLtstNum)
        Else
            ZoekHuisnummer = Mid(Adres, StartLtstNum)
        End If
    Else
        ZoekHuisnummer = Null
    End If
Else
    ZoekHuisnummer = Null
End If
End Function

Function ZoekToevoegsel(Adres As Variant) As String
Dim Teller As Byte
Dim AantalTekens As Byte
Dim StartLtstNum As Byte
Dim StartLtstTxt As Byte
Dim VorigTeken As String
Dim Teken As String
Dim AW As Integer

If IsNull(Adres) Then
    AantalTekens = 0
Else
    AantalTekens = Len(Adres)
End If

StartLtstNum = 0
StartLtstTxt = 0
VorigTeken = "X"

If AantalTekens > 0 Then
    For Teller = 1 To AantalTekens
        Teken = Mid(Adres, Teller, 1)
        If IsNumeric(Teken) And Not IsNumeric(VorigTeken) Then StartLtstNum = Teller
        If Not IsNumeric(Teken) And IsNumeric(VorigTeken) Then StartLtstTxt = Teller
        VorigTeken = Teken
    Next
    
    If StartLtstNum > 1 Then
        If StartLtstTxt > StartLtstNum Then
            ZoekToevoegsel = Trim(Mid(Adres, StartLtstTxt))
            AW = Asc(ZoekToevoegsel)
            If AW < 65 Or (AW > 90 And AW < 97) Or AW > 122 Then ZoekToevoegsel = Mid(ZoekToevoegsel, 2)
        Else
            ZoekToevoegsel = ""
        End If
    Else
        ZoekToevoegsel = ""
    End If
Else
    ZoekToevoegsel = ""
End If
End Function

maar die geeft dan weer andere resultaten daar waar jouw module wel correct is.
 
...maar die geeft dan weer andere resultaten daar waar jouw module wel correct is.
ALTIJD uitgaan van functies die (in beginsel) het juiste resultaat geven :). Datamigratie is altijd een heikel punt, want hoe goed je het ook voorbereid, er komen altijd gevallen voor waarin het nét iets anders is ingevuld. Om die gevallen dan níet te importeren, of verkeerd, is in mijn ogen onwenselijk, want je krijgt verkeerde data. En niemand die achteraf nog kan zien dát er verkeerd is geïmporteerd. Dat zou je dus niet moeten willen.

Omdat het hier om een paar records gaat, is het handmatig aanpassen ervan vele malen beter en (zeker) sneller, dan dus moedwillig fouten in je data toestaan. Kortom: even in het bronbestand duiken, de data aanpassen en opnieuw importeren. Is eigenlijk de enige zinvolle optie. Aanpassen van de routines, die hier toch niet mee uit de voeten kunnen, kost vele malen meer tijd.
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan