CODEWITHSUNDEEP
sqlsql-serverms-access
MrEmper
MrEmper

Reputation: 235

MS Access using textbox value in a query

I got a simple form containing 1 textbox and 1 button.

Now I wanna use this function:

    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

As a pass-through using the function and take the value from the textbox as the variable. or this query:

    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
    GROUP BY f.Fiets_id, f.Fiets_Type

And make it have this WHERE statement;

WHERE YEAR(h.Huurovereenkomst_Begin_datum) = Textbox1 AND 
YEAR(h.Huurovereenkomst_Eind_datum) = Textbox1

I can't find out how to do it with either solutions, does anyone have a idea how to use a textbox value in my query to get the results?

Upvotes: 0

Views: 2507

Answers (1)

Gustav
Gustav

Reputation: 56026

That could be:

WHERE YEAR(h.Huurovereenkomst_Begin_datum) = [Forms]![YourForm]![Textbox1] AND 
YEAR(h.Huurovereenkomst_Eind_datum) = [Forms]![YourForm]![Textbox1]

Upvotes: 1

Related Questions