Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005

    Unanswered: Function returning criteria to SQL query

    Below I'm trying to work out how to use a function to specify the criteria for a query. In this particular case, 5 weeknumbers startin in the current week, and four weeks forward.

    On executing it I get a "data type mismatch in the criteria expression", most likely because the return value is interpreted as a text string with quotation marks "45 or 46 or 47 or 48 or 49". If I type that into the query manually, thats the exact error I get, whereas it works perfectly if I just type 45 or 46 or 47... etc.

    So how do i return the criteria without getting the quotation marks? or is there another way to do this? I am currently dabbling with some pretty tangled datepart / dateadd expression to lose the function altogether, but haven't worked it out yet.

    Cheers, Trin

    The transform - note the where clause points to the function further down.

    TRANSFORM First(func_findopfoelgningborger([Forloeb_relativuge],[Forloeb_ugenummer])) AS Personer
    SELECT Forloeb_tilopfoelgning.Forloeb_relativuge
    FROM Forloeb_tilopfoelgning
    WHERE (((Forloeb_tilopfoelgning.Forloeb_ugenummer)=func_fokusuger()))
    GROUP BY Forloeb_tilopfoelgning.Forloeb_relativuge
    PIVOT Forloeb_tilopfoelgning.Forloeb_ugenummer;
    The function, I've also tried returning the values and editing the query to a "where in (values)" style query. I've also tried returning as the data type variant.

    Public Function func_fokusuger() As String
        Dim denneuge As Byte
        Dim udvalgteuger As String
        Dim i As Byte
        denneuge = DatePart("ww", Date, vbUseSystemDayOfWeek, vbUseSystem)
        For i = 0 To 4
            udvalgteuger = udvalgteuger & "" & LTrim(Str(func_ugekorrektur(denneuge + i))) & ""
            If i < 4 Then udvalgteuger = udvalgteuger & " or "
        Next i
        func_fokusuger = udvalgteuger
    End Function
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Oct 2005
    Never mind. I worked out an alternative solution adding "Between DatePart("ww";Date()) And DatePart("ww";DateAdd("ww";4;Date()))" to the criteria expression.

    Ocassionally morning coffee DOES seem to help.

    Cheers, Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts