MS Access creating a form with a SQL function

Question

Good afternoon all,

I am so frustrated because I know visually what I want but I cannot get it working in MS Access. I've got a function in SQL server which I need to use in MS Access. I tried translating it, but it didn't work out. Eventually found out you need a blank form, added a text box but now comes the real struggle; I need a button.

    CREATE FUNCTION fnFietsAantDagenPerJaar
        (
            @Jaar AS int

        )
    RETURNS TABLE
    AS
    RETURN

    SELECT f.Fiets_id, f.Fiets_Type, SUM(DATEDIFF(DAY, h.Huurovereenkomst_Begin_datum, h.Huurovereenkomst_Eind_datum)) AantalDagen
    FROM Fiets f
        INNER JOIN HuurovereenkomstFiets hf
        ON hf.HuurovereenkomstFiets_Fiets_id = f.Fiets_id
        INNER JOIN Huurovereenkomst h
        ON h.Huurovereenkomst_id = hf.HuurovereenkomstFiets_Huurovereenkomst_id
    WHERE YEAR(h.Huurovereenkomst_Begin_datum) = @Jaar AND YEAR(h.Huurovereenkomst_Eind_datum) = @Jaar
    GROUP BY f.Fiets_id, f.Fiets_Type

    GO

This is my SQL function, which works. Now I want a MS Access form which simple says:

   <Year>   [Enter]

Where the the year is a text box and the enter button should execute the function with the year are variable. Anyone that can get me going?

Is this an Access frontend targeting a SQL Server backend database, or are you moving SQL Server code into Access entirely? Access' support for SQL language features is severely lacking and remains largely unchanged for almost 20 years now, for example Access does not support SQL User-defined functions. — Dai
– Dai, Commented Jan 5, 2018 at 12:53
That is true, it's a data link to the server. I am not moving it into Access entirely. — MrEmper
– MrEmper, Commented Jan 5, 2018 at 12:56

Gustav · Accepted Answer · 2018-01-05 13:26:32Z

1

Try with:

PARAMETERS Jaar Integer;
SELECT 
    f.Fiets_id, 
    f.Fiets_Type, 
    SUM(DateDiff("d", h.Huurovereenkomst_Begin_datum, h.Huurovereenkomst_Eind_datum)) AS AantalDagen
FROM 
    Fiets AS f
INNER JOIN HuurovereenkomstFiets AS hf
    ON hf.HuurovereenkomstFiets_Fiets_id = f.Fiets_id
    INNER JOIN Huurovereenkomst AS h
        ON h.Huurovereenkomst_id = hf.HuurovereenkomstFiets_Huurovereenkomst_id
WHERE 
    Year(h.Huurovereenkomst_Begin_datum) = [Jaar] AND 
    Year(h.Huurovereenkomst_Eind_datum) = [Jaar]
GROUP BY 
    f.Fiets_id, 
    f.Fiets_Type

This may be slow as Access has to do full table scans. If so, consider creating a pass-through query using your original SQL. That will be send to SQL Server and handled there, and only the result will be returned.

answered Jan 5, 2018 at 13:26

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MrEmper Over a year ago

This is great, I made a pass-through: SELECT * FROM dbo.fnFietsAantDagenPerJaar. After that added the query to the button. Now when I press the button is says I have no values, while I put it in the textbox.

Gustav Over a year ago

You can't assign a query to a button. Use DLookup or a function to run the query and return the value.

Collectives™ on Stack Overflow

MS Access creating a form with a SQL function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related