How to format the date in the data source in Access form?

Question

I get errors trying to format and handle the date in the data source of a textbox.

I am trying to calculate a sum of fields in the last month

=DSum("Betrag"; "tbl_Ausgaben";
    "[Datum] > #" 
    & DateAdd ('m'; -1; Date()) - Day (DateAdd ('m'; -1; Date()))     //should returns e.g. 31-05-2023
    & "# AND [Datum] <= #" 
    & Date() - Day(Date())                                            //should returns e.g. 30-06-2023
    & "#")

But I get error as resault. I tried to make the code as simple as possible to find out the issue like:

=DSum("Betrag"; "tbl_Ausgaben"; "[Datum] > DateAdd ('m'; -1; Date())")
**OR**
=DomSumme("Betrag";"tbl_AusgabenPersonal";"[Datum] > #" & Date() & "#")

But that also returns an error. I think it is because the date format in Germany is dd.mm.yyyy and Access needs dd-mm-yyyy So I tried to format it with:

=Format(Date();"dd-mm-yyyy")

But Access changes it after pressing ok to

=Format(Datum();"""dd-""mm""-yyyy""")

and the resault is: dd-07-yyyy instead of 02-07-2023

Why does the format function get so strange?

Answer 1

Don't concatenate the criteria, use direct calculation with DateSerial:

=DSum("Betrag"; "tbl_Ausgaben";
"[Datum] >= DateSerial(Year(Date()), Month(Date()) - 1, 1) And [Datum] < DateSerial(Year(Date()), Month(Date()), 1)")