Hi!
I have a table were each time someone from a village enters money is recorded, the amount and the date. I want to query that adding the total amounts per month. I managed to do it for a whole year whenever the year is the current one.
SELECT DISTINCTROW water_selling.village, water_selling.name_wug, Sum(IIf(water_selling.date>="1/1/" & DatePart("yyyy",Date()) And water_selling.date<="1/31/" & DatePart("yyyy",Date()),water_selling.amount)) AS amount_january, Sum(IIf(water_selling.date>="2/1/" & DatePart("yyyy",Date()) And water_selling.date<="2/29/" & DatePart("yyyy",Date()),water_selling.amount)) AS amount_february, ...
FROM water_selling
GROUP BY water_selling.village, water_selling.name_wug;
(I just copied january and february...) This works quite fine. But what I'm trying to do is that instead of the current date, the user can fix the year on a form:
SELECT DISTINCTROW water_selling.village, water_selling.name_wug, Sum(IIf(water_selling.date>="1/1/" & [Forms]![FIELDMON_waterselling]![year] And water_selling.date<="1/31/" & [Forms]![FIELDMON_waterselling]![year],water_selling.amount)) AS amount_january, Sum(IIf(water_selling.date>="2/1/" & [Forms]![FIELDMON_waterselling]![year] And water_selling.date<="2/29/" & [Forms]![FIELDMON_waterselling]![year],water_selling.amount)) AS amount_february, ...
FROM water_selling
GROUP BY water_selling.village, water_selling.name_wug;
Why is now not working? I've been trying quite a lot of different things around this query, but I cannot get it...
I'll really appreciate your help!
JoRDi.
I have a table were each time someone from a village enters money is recorded, the amount and the date. I want to query that adding the total amounts per month. I managed to do it for a whole year whenever the year is the current one.
SELECT DISTINCTROW water_selling.village, water_selling.name_wug, Sum(IIf(water_selling.date>="1/1/" & DatePart("yyyy",Date()) And water_selling.date<="1/31/" & DatePart("yyyy",Date()),water_selling.amount)) AS amount_january, Sum(IIf(water_selling.date>="2/1/" & DatePart("yyyy",Date()) And water_selling.date<="2/29/" & DatePart("yyyy",Date()),water_selling.amount)) AS amount_february, ...
FROM water_selling
GROUP BY water_selling.village, water_selling.name_wug;
(I just copied january and february...) This works quite fine. But what I'm trying to do is that instead of the current date, the user can fix the year on a form:
SELECT DISTINCTROW water_selling.village, water_selling.name_wug, Sum(IIf(water_selling.date>="1/1/" & [Forms]![FIELDMON_waterselling]![year] And water_selling.date<="1/31/" & [Forms]![FIELDMON_waterselling]![year],water_selling.amount)) AS amount_january, Sum(IIf(water_selling.date>="2/1/" & [Forms]![FIELDMON_waterselling]![year] And water_selling.date<="2/29/" & [Forms]![FIELDMON_waterselling]![year],water_selling.amount)) AS amount_february, ...
FROM water_selling
GROUP BY water_selling.village, water_selling.name_wug;
Why is now not working? I've been trying quite a lot of different things around this query, but I cannot get it...
I'll really appreciate your help!
JoRDi.