Avatar billede Bjørn Mester
04. oktober 2020 - 17:19 Der er 10 kommentarer og
1 løsning

Køre en Function fra forespørgsel

Hej
En opfølger til spørgsmål https://www.computerworld.dk/eksperten/spm/1034757. Tak for gode svar Terry (Det virker fint i formularen).
Er det muligt at lægge Public Function GetClubs() As String (Se #10) ind så den bliver kørt direkte fra forespørgslen.
Jeg har lagt nedenstående i et modul for sig og henvisning til formularen er rettet til og det fungerer fint når jeg prøver med "Debug.Print GetClubs1" men svarene kommer ikke med over i forspørgslen.

Det jeg er kommet frem til er at den "burde" se sådan ud:
Public Function GetClubs1() As String

Dim varItem As Variant      'Selected items
Dim strWhere As String      'String to use as WhereCondition
Dim lngLen As Long          'Length of string
Dim strDelim As String      'Delimiter for this field type.


    GetClubs1 = ""

    strDelim = """"

    'Loop through the ItemsSelected in the list box.
    With Forms!Oversigt!Liste52    'Hvad fejler her???
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then

                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ";"

            End If
        Next
    End With

    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
'        strWhere = "IN (" & Left$(strWhere, lngLen) & ")"
'        strWhere = "[Klub_Alias] IN (" & Left$(strWhere, lngLen) & ")"
        strWhere = "Like (" & Left$(strWhere, lngLen) & ")"
   
    End If

    GetClubs1 = strWhere

End Function

Mvh.
Bjørn
Avatar billede terry Ekspert
04. oktober 2020 - 17:31 #1
I actually did try that idea when I was working on a solution for your previous question but I'm sorry to say it doesnt work, or maybe I should say, I couldnt get it to work. Although I didnt use so much time on it :-(
Avatar billede terry Ekspert
04. oktober 2020 - 17:33 #2
Why do you need to run the GetClubs() function from a query?
Avatar billede terry Ekspert
04. oktober 2020 - 17:34 #3
I'm asking becuase depending on th ereason, I may have another solution :-)
Avatar billede Bjørn Mester
04. oktober 2020 - 18:01 #4
Hej
Egentlig ikke andet end jeg tænkte at det ville være smart at have funktionen i sit eget modul og kunne bruge den til andre ting. Og efter at have læst frem og tilbage på nettet er det jo ikke noget problem at bruge en function i en query.
Det er det så åbenbart alligevel? :-(
Men jeg vil da gerne vide hvad anden løsning kunne være. :-)

BR.
Bjørn
Avatar billede terry Ekspert
04. oktober 2020 - 18:17 #5
I'm a bit puzzled why I cant get it to work myseldf :-(

If I add a new line at the end of the GetClubs() function
like this...
GetClubs = "Burning Arrows"

Then it seems to work.

But if I use
GetClubs1 = strWhere

Then it doesnt.

Let me play around a bit to see if I can find out why GetClubs1 = strWhere doesnt work
Avatar billede terry Ekspert
04. oktober 2020 - 19:10 #6
The problems is this:

If I use this
GetClubs = strWhere
GetClubs contains a string "Burning Arrows" where the "" is part of the string

GetClubs = "Burning Arrows"
GetClubs contains Burning Arrows WITHOUT the ""


You can try this. Select only one of the clubs in the list.




Public Function GetClubs1() As String

Dim varItem As Variant      'Selected items
Dim strWhere As String      'String to use as WhereCondition
Dim lngLen As Long          'Length of string
Dim strDelim As String      'Delimiter for this field type.


    GetClubs1 = ""

    'Loop through the ItemsSelected in the list box.
    With Forms!Oversigt!Liste52    'Hvad fejler her???
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
               
                strWhere = strWhere & .ItemData(varItem) & ";"

            End If
        Next
    End With

    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
        strWhere = Left$(strWhere, lngLen)
       
 
    End If

    GetClubs1 = strWhere

End Function

Then in the query criteria

Like (GetClubs1())
Avatar billede terry Ekspert
04. oktober 2020 - 19:22 #7
My other idea was to alter the actual SQL in the query using VBA but I'm not sure thats a good idea
Avatar billede Bjørn Mester
04. oktober 2020 - 20:13 #8
Tak Terry

Det er så åbenbart sådan. :-/

Jeg er forvirret på et højere plan. Hvis det virker i formularen burde det vel også virke i forespørgslen??? :-)

Mvh.
Bjørn
Avatar billede terry Ekspert
04. oktober 2020 - 20:29 #9
Things arent as streightforward as that. And I think I'd probably confuse myself tryiing to explain why its doesnt work.

As you can see we can call a function and it will work if you only return one club as a string, but not with two or more.

I'll continue trying to get it to work

br
Terry
Avatar billede Bjørn Mester
04. oktober 2020 - 21:13 #10
Thank you very much.
Avatar billede terry Ekspert
05. oktober 2020 - 15:46 #11
Still havent found a solution :-(

Heres the problem, try this.

Enter this in the query criteria

In ([Enter clubs])

Now when you run the query enter a club name for example Burning Arrows
You should see a result with 6 rows.

Now run the query again and enter two club names.

Now it doesnt work. This is because Access doesnt know that you are entering two club names, it accepts what you enter as one club, and because there isnt a club with the string you entered it doesnt show any rows.

And this is exactly what is happening when you try using the GetClubs() function to return clubs. If you only return one club then it works, but two or more it doesnt.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester