How do I get a query result into a textfield?-CodePudding

I am creating an accounting database. For the financial results, my form has a textfield ("Previous Solde") where the solde of previous months is shown. That exact amount is calculated with this query:

SELECT [sq Argenta Prep Vorig Saldo].Account, Sum([sq Argenta Prep Vorig Saldo].SomVanBedrag) AS SomVanSomVanBedrag FROM [sq Argenta Prep Vorig Saldo] WHERE (((Year([Boekdatum]) & "/" & Month([Boekdatum]))<"2022/6")) GROUP BY [sq Argenta Prep Vorig Saldo].Account HAVING ((([sq Argenta Prep Vorig Saldo].Account)="BE61973184860917"));

The account number ("BE61973184860917") and the closing date ("2022/6") of course are replaced with variables. The query does exactly what I want it to do. When I run this query in Access, I get the expected result. The question is: how do I get the result of the query into the textfield (Previous Solde) on the form?

CodePudding user response：

Could simplify that SQL

SELECT Sum([sq Argenta Prep Vorig Saldo].SomVanBedrag) AS SomVanSomVanBedrag 
FROM [sq Argenta Prep Vorig Saldo] 
WHERE Format([Boekdatum], "yyyy/mm") < "2022/06"
     AND [sq Argenta Prep Vorig Saldo].Account = "BE61973184860917";

Then use DLookup() domain aggregate function expression in textbox to pull from that query.
=DLookup("SomVanSomVanBedrag", "queryname")

Or don't use query and instead use DSum() domain aggregate function expression in textbox.

=DSum("SomVanBedrag", "sq Argenta Prep Vorig Saldo", 
      "Format(Boekdatum, 'yyyy/mm') < '2022/06' AND Account = 'BE61973184860917'")

Note the use of placeholder zero in the month part. Without it, records will not filter/sort correctly - 2022/12 would be less than 2022/6. This means query run in December would not retrieve Feb - Sep data for that same year. Even if the value is structured as a number without the /, the 0 would be needed. If users are selecting from a combobox, make sure values include placeholder 0 for 2-digit month.

CodePudding user response：

The simple method is to use DSum in the ControlSource of that textbox.

To include your parameters from, I guess, two other textboxes named, say, txtDatum and txtAccount holding the values BE61973184860917 and 2022/6:

=DSum("[SomVanBedrag]","[sq Argenta Prep Vorig Saldo]","[Boekdatum] < DateValue(" & [txtDatum] & ") And [Account] = '" & [txtAccount] & "'")