indsætte værdi i felt hvis følgende ord indgår i andet felt

Tja, jeg er ikke helt sikker på, at jeg forstår dit problem, men måske kan du gøre det med en konstruktion som:

Function AssignValue(Kombistring As String) As Integer

    If InStr(Kombistring, "bil") > 0 Then
        AssignValue = 20
    ElseIf InStr(Kombistring, "hjul") > 0 Then
        AssignValue = 50
    ElseIf InStr(Kombistring, "ben") > 0 And InStr(Kombistring, "bord") > 0 And InStr(Kombistring, "stol") > 0 Then
        AssignValue = 200
    End If
   
End Function

som jeg læser dit forslag tager den kun højde for "ben bord stol" men der kan jo være ca. 150 kombinationer f.eks: "bil, ben"; "sort, rød"; "hus, sten, tag" etc etc

du kan se dette link hvordan jeg indtil nu har lavet det http://www.eksperten.dk/spm/793605

Den forklarer forhåbeligt lidt.

Okay, du vil hellere have det som et opslag i en tabel og foretaget i SQL. Ja, det er sikkert også det mest fornuftige og gennemtænkte set i en vedligeholdelsessammenhæng.

Okay, så vil jeg foreslå, at du opretter en ekstra tabel med kombinationer og tilhørende point (eller hvad det nu er) for de ord som bare skal forekomme.

Nu antager jeg, at du har disse tre tabeller:

dinOrdTabel
Id, Ord1, Ord2, Ord3

dinKombiordPointTabel
Id, Ord1, Ord2, Ord3, Point

dinForekomstOrdPointTabel
Id, Ord, Point

hvor dinOrdTabel er det udgangspunkt du har og de to andre indeholder ordene og deres tilhørende point.


Så burde du kunne gøre sådan her:


SELECT o.Ord1, o.Ord2, o.Ord3, o.Ord1 & o.Ord2 & o.Ord3 AS Kombiord, Nz(p1.Point, Nz(p2.Point, 0)) AS Point
FROM dinOrdTabel o
LEFT JOIN dinForekomstordPointTabel p1 ON (o.Ord1 = p1.Ord Or o.Ord2 = p1.Ord Or o.Ord3 = p1.Ord)
LEFT JOIN dinKombiordPointTabel p2 ON
(Nz(o.Ord1,"")=Nz(p2.Ord1,"") And Nz(o.Ord2,"")=Nz(p2.Ord2,"") And Nz(o.Ord3,"")=Nz(p2.Ord3,"")) Or (Nz(o.Ord1,"")=Nz(p2.Ord1,"") And Nz(o.Ord3,"")=Nz(p2.Ord2,"") And Nz(o.Ord2,"")=Nz(p2.Ord3,"")) Or (Nz(o.Ord2,"")=Nz(p2.Ord1,"") And Nz(o.Ord1,"")=Nz(p2.Ord2,"") And Nz(o.Ord3,"")=Nz(p2.Ord3,"")) Or (Nz(o.Ord2,"")=Nz(p2.Ord1,"") And Nz(o.Ord3,"")=Nz(p2.Ord2,"") And Nz(o.Ord1,"")=Nz(p2.ord3,"")) Or (Nz(o.Ord3,"")=Nz(p2.Ord1,"") And Nz(o.Ord1,"")=Nz(p2.Ord2,"") And Nz(o.Ord2,"")=Nz(p2.Ord3,"")) Or (Nz(o.Ord3,"")=Nz(p2.Ord1,"") And Nz(o.Ord2,"")=Nz(p2.Ord2,"") And Nz(o.Ord1,"")=Nz(p2.Ord3,""))
Or
(Nz(o.Ord1,"")=Nz(p2.Ord1,"") And Nz(o.Ord3,"")=Nz(p2.Ord3,"") And Nz(o.Ord3,"")=Nz(p2.Ord2,"")) Or (Nz(o.Ord1,"")=Nz(p2.Ord1,"") And Nz(o.Ord3,"")=Nz(p2.Ord3,"") And Nz(o.Ord2,"")=Nz(p2.Ord2,"")) Or (Nz(o.Ord2,"")=Nz(p2.Ord1,"") And Nz(o.Ord1,"")=Nz(p2.Ord3,"") And Nz(o.Ord3,"")=Nz(p2.Ord2,"")) Or (Nz(o.Ord2,"")=Nz(p2.Ord1,"") And Nz(o.Ord3,"")=Nz(p2.Ord3,"") And Nz(o.Ord1,"")=Nz(p2.ord2,"")) Or (Nz(o.Ord3,"")=Nz(p2.Ord1,"") And Nz(o.Ord1,"")=Nz(p2.Ord3,"") And Nz(o.Ord2,"")=Nz(p2.Ord2,"")) Or (Nz(o.Ord3,"")=Nz(p2.Ord1,"") And Nz(o.Ord2,"")=Nz(p2.Ord3,"") And Nz(o.Ord1,"")=Nz(p2.Ord2,""))
Or
(Nz(o.Ord1,"")=Nz(p2.Ord2,"") And Nz(o.Ord3,"")=Nz(p2.Ord1,"") And Nz(o.Ord3,"")=Nz(p2.Ord3,"")) Or (Nz(o.Ord1,"")=Nz(p2.Ord2,"") And Nz(o.Ord3,"")=Nz(p2.Ord1,"") And Nz(o.Ord2,"")=Nz(p2.Ord3,"")) Or (Nz(o.Ord2,"")=Nz(p2.Ord2,"") And Nz(o.Ord1,"")=Nz(p2.Ord1,"") And Nz(o.Ord3,"")=Nz(p2.Ord3,"")) Or (Nz(o.Ord2,"")=Nz(p2.Ord2,"") And Nz(o.Ord3,"")=Nz(p2.Ord1,"") And Nz(o.Ord1,"")=Nz(p2.ord3,"")) Or (Nz(o.Ord3,"")=Nz(p2.Ord2,"") And Nz(o.Ord1,"")=Nz(p2.Ord1,"") And Nz(o.Ord2,"")=Nz(p2.Ord3,"")) Or (Nz(o.Ord3,"")=Nz(p2.Ord2,"") And Nz(o.Ord2,"")=Nz(p2.Ord1,"") And Nz(o.Ord1,"")=Nz(p2.Ord3,""))
Or
(Nz(o.Ord1,"")=Nz(p2.Ord2,"") And Nz(o.Ord3,"")=Nz(p2.Ord3,"") And Nz(o.Ord3,"")=Nz(p2.Ord1,"")) Or (Nz(o.Ord1,"")=Nz(p2.Ord2,"") And Nz(o.Ord3,"")=Nz(p2.Ord3,"") And Nz(o.Ord2,"")=Nz(p2.Ord1,"")) Or (Nz(o.Ord2,"")=Nz(p2.Ord2,"") And Nz(o.Ord1,"")=Nz(p2.Ord3,"") And Nz(o.Ord3,"")=Nz(p2.Ord1,"")) Or (Nz(o.Ord2,"")=Nz(p2.Ord2,"") And Nz(o.Ord3,"")=Nz(p2.Ord3,"") And Nz(o.Ord1,"")=Nz(p2.ord1,"")) Or (Nz(o.Ord3,"")=Nz(p2.Ord2,"") And Nz(o.Ord1,"")=Nz(p2.Ord3,"") And Nz(o.Ord2,"")=Nz(p2.Ord1,"")) Or (Nz(o.Ord3,"")=Nz(p2.Ord2,"") And Nz(o.Ord2,"")=Nz(p2.Ord3,"") And Nz(o.Ord1,"")=Nz(p2.Ord1,""))
Or
(Nz(o.Ord1,"")=Nz(p2.Ord3,"") And Nz(o.Ord3,"")=Nz(p2.Ord1,"") And Nz(o.Ord3,"")=Nz(p2.Ord2,"")) Or (Nz(o.Ord1,"")=Nz(p2.Ord3,"") And Nz(o.Ord3,"")=Nz(p2.Ord1,"") And Nz(o.Ord2,"")=Nz(p2.Ord2,"")) Or (Nz(o.Ord2,"")=Nz(p2.Ord3,"") And Nz(o.Ord1,"")=Nz(p2.Ord1,"") And Nz(o.Ord3,"")=Nz(p2.Ord2,"")) Or (Nz(o.Ord2,"")=Nz(p2.Ord3,"") And Nz(o.Ord3,"")=Nz(p2.Ord1,"") And Nz(o.Ord1,"")=Nz(p2.ord2,"")) Or (Nz(o.Ord3,"")=Nz(p2.Ord3,"") And Nz(o.Ord1,"")=Nz(p2.Ord1,"") And Nz(o.Ord2,"")=Nz(p2.Ord2,"")) Or (Nz(o.Ord3,"")=Nz(p2.Ord3,"") And Nz(o.Ord2,"")=Nz(p2.Ord1,"") And Nz(o.Ord1,"")=Nz(p2.Ord2,""))
Or
(Nz(o.Ord1,"")=Nz(p2.Ord3,"") And Nz(o.Ord3,"")=Nz(p2.Ord2,"") And Nz(o.Ord3,"")=Nz(p2.Ord1,"")) Or (Nz(o.Ord1,"")=Nz(p2.Ord3,"") And Nz(o.Ord3,"")=Nz(p2.Ord2,"") And Nz(o.Ord2,"")=Nz(p2.Ord1,"")) Or (Nz(o.Ord2,"")=Nz(p2.Ord3,"") And Nz(o.Ord1,"")=Nz(p2.Ord2,"") And Nz(o.Ord3,"")=Nz(p2.Ord1,"")) Or (Nz(o.Ord2,"")=Nz(p2.Ord3,"") And Nz(o.Ord3,"")=Nz(p2.Ord2,"") And Nz(o.Ord1,"")=Nz(p2.ord1,"")) Or (Nz(o.Ord3,"")=Nz(p2.Ord3,"") And Nz(o.Ord1,"")=Nz(p2.Ord2,"") And Nz(o.Ord2,"")=Nz(p2.Ord1,"")) Or (Nz(o.Ord3,"")=Nz(p2.Ord3,"") And Nz(o.Ord2,"")=Nz(p2.Ord2,"") And Nz(o.Ord1,"")=Nz(p2.Ord1,""))


Da der er brugt left outer joins vil der blive returneret null, hvis kriteriet ikke opfyldes. Da Nz funktionen i SELECT delen sørger for, at der bliver prioriteret mellem de forskellige point-værdier der returneres, sikres det, at din "findes i" fra dinForekomstordPointTabel bliver prioriteret over en evt. samtidig returneret værdi fra den hvor alle ord skal forefindes (dinKombiordPointTabel).


På sin vis kunne det sikkert have været lettere at søge med LIKE i det sammensatte ord, men der er nogle faldgruber, som du undgår ved at teste på alle kombinationerne i stedet, selv om det jo giver en meget lang SQL sætning.

Ved at teste på alle kombinationerne i forespørgslen undgår du også at skulle oprette rækker for alle kombinationerne. Testen på disse kombinationer laves i forespørgslen, hvilket er lettere og sikrere.

Det nemmeste vil jo nok være at køre en dlookup når felterne er udfyldt, den skal så udfylde point feltet. Herefter kan man bare lave en elseif sætning e.lign. som overskriver hvis et af felterne er et af dine "specielle" ord.

Man hvad du egentlig sidder og roder med, syntes jeg er temmelig vanskeligt at gennemskue!~)