how can we use Sum IF

[Frank Situmorang]

Hello,

I tried to have a report to show the events in our church. since Union Query
is too complicated to my understanding, so I use Select Query and on each
date event ( Birth, marriage, death etc) I use a filter by Form. with the
criteria in the dates field:
Between [Forms]![frmReportDates_All DateEvents]![txtBegDate] And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]

However Although I filter only for Feb for example, all the dates of birth
is still shown on the report. When I want to count each column, the result
will be misleading ( is not waht we wantted). My question is how can we make
a formula to be able to count only what we want. Can we make: = Count( Date
of Birth) IF(Between [Forms]![frmReportDates_All DateEvents]![txtBegDate] And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]).

Something like that?

I appreciate your help

 

[Douglas J. Steele]

While it may seem counterintuitive, the solution is to use Sum, not Count:

Sum(IIf([DateofBirth] BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND
[Forms]![frmReportDates_All DateEvents]![txtEndDate], 1, 0))

 

[Frank Situmorang]

Thanks Dough for your explanation, but when I tried it there is a syntax
error says: "3075 Syantax error in query expression
'First([Sum(IIf([TGLLahir] BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND
[Forms]![frmReportDates_All DateEvents]![txtEndDate], 1, 0))])'.

Can you help me again?. For your information the report is based on a quiry
on each coulum of dates, I have a Criteria: BETWEEN
[Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND
[Forms]![frmReportDates_All DateEvents]![txtEndDate]

Thanks in advance


H. Frank Situmorang

While it may seem counterintuitive, the solution is to use Sum, not Count:

Sum(dIIf([DateofBirth] BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND
[Forms]![frmReportDates_All DateEvents]![txtEndDate], 1, 0))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

I tried to have a report to show the events in our church. since Union
Query
is too complicated to my understanding, so I use Select Query and on each
date event ( Birth, marriage, death etc) I use a filter by Form. with the
criteria in the dates field:
Between [Forms]![frmReportDates_All DateEvents]![txtBegDate] And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]

However Although I filter only for Feb for example, all the dates of birth
is still shown on the report. When I want to count each column, the result
will be misleading ( is not waht we wantted). My question is how can we
make
a formula to be able to count only what we want. Can we make: = Count(
Date
of Birth) IF(Between [Forms]![frmReportDates_All DateEvents]![txtBegDate]
And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]).

Something like that?

I appreciate your help

 

[Douglas J. Steele]

What's the entire SQL of your query?

Incidentally, First is seldom useful...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Dough for your explanation, but when I tried it there is a syntax
error says: "3075 Syantax error in query expression
'First([Sum(IIf([TGLLahir] BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND
[Forms]![frmReportDates_All DateEvents]![txtEndDate], 1, 0))])'.

Can you help me again?. For your information the report is based on a
quiry
on each coulum of dates, I have a Criteria: BETWEEN
[Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND
[Forms]![frmReportDates_All DateEvents]![txtEndDate]

Thanks in advance


H. Frank Situmorang

While it may seem counterintuitive, the solution is to use Sum, not
Count:

Sum(dIIf([DateofBirth] BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND
[Forms]![frmReportDates_All DateEvents]![txtEndDate], 1, 0))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

I tried to have a report to show the events in our church. since Union
Query
is too complicated to my understanding, so I use Select Query and on
each
date event ( Birth, marriage, death etc) I use a filter by Form. with
the
criteria in the dates field:
Between [Forms]![frmReportDates_All DateEvents]![txtBegDate] And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]

However Although I filter only for Feb for example, all the dates of
birth
is still shown on the report. When I want to count each column, the
result
will be misleading ( is not waht we wantted). My question is how can we
make
a formula to be able to count only what we want. Can we make: = Count(
Date
of Birth) IF(Between [Forms]![frmReportDates_All
DateEvents]![txtBegDate]
And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]).

Something like that?

I appreciate your help

 

[KARL DEWEY]

Try it this way --
Sum(IIf([TGLLahir] BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND [Forms]![frmReportDates_All
DateEvents]![txtEndDate], 1, 0))

--
KARL DEWEY
Build a little - Test a little

Thanks Dough for your explanation, but when I tried it there is a syntax
error says: "3075 Syantax error in query expression
'First([Sum(IIf([TGLLahir] BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND
[Forms]![frmReportDates_All DateEvents]![txtEndDate], 1, 0))])'.

Can you help me again?. For your information the report is based on a quiry
on each coulum of dates, I have a Criteria: BETWEEN
[Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND
[Forms]![frmReportDates_All DateEvents]![txtEndDate]

Thanks in advance


H. Frank Situmorang

While it may seem counterintuitive, the solution is to use Sum, not Count:

Sum(dIIf([DateofBirth] BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND
[Forms]![frmReportDates_All DateEvents]![txtEndDate], 1, 0))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

I tried to have a report to show the events in our church. since Union
Query
is too complicated to my understanding, so I use Select Query and on each
date event ( Birth, marriage, death etc) I use a filter by Form. with the
criteria in the dates field:
Between [Forms]![frmReportDates_All DateEvents]![txtBegDate] And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]

However Although I filter only for Feb for example, all the dates of birth
is still shown on the report. When I want to count each column, the result
will be misleading ( is not waht we wantted). My question is how can we
make
a formula to be able to count only what we want. Can we make: = Count(
Date
of Birth) IF(Between [Forms]![frmReportDates_All DateEvents]![txtBegDate]
And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]).

Something like that?

I appreciate your help

 

[Mike Painter]

Hello,

I tried to have a report to show the events in our church. since
Union Query is too complicated to my understanding,...

This seems to be an important undertaking for you. While it seems that you
are far along on development it appears that your basic table structure is
not set up right and this is causing you problems.
These problems will not go away and as users find out what can be done
requests for more complex information will grow. The time between hearing
that "It's perfect" and "can you add one more little thing will get shorter
and shorter and bad design will make it harder and harder.
If development gets passed on to somebody else they will have trouble.

I would suggest that you freeze what you are doing, pas it out and then redo
the table structure.

 

[Frank Situmorang]

Thanks Dough for your willingness to help me. This is the entire query. What
I did on each dates on the criteria and OR row, I put the filter of the
form. so that once I filter the rage of date in the form, it will show up the
name. But the problem is it seem the OR function does not work.

Here is the query in sql view:
SELECT bukuangkby.FNAME, bukuangkby.MNAME, bukuangkby.LNAME, [FNAME] & " " &
[MNAME] & " " & [LNAME] AS NamaFull, bukuangkby.JenisKel,
bukuangkby.TGLLAHIR, bukuangkby.TGLBPTIS_M, bukuangkby.TGL_pen,
bukuangkby.ATSPERCA_M, bukuangkby.ATSSUR1_K, bukuangkby.ATSSUR2_K,
bukuangkby.KMATIAN_K, bukuangkby.KELMURT_K, bukuangkby.KELHILA_K,
bukuangkby.Tgl_Nikah
FROM bukuangkby
WHERE (((bukuangkby.Tgl_Nikah) Between [Forms]![frmReportDates_All
DateEvents]![txtBegDate] And [Forms]![frmReportDates_All
DateEvents]![txtEndDate])) OR (((bukuangkby.TGLLAHIR) Between
[Forms]![frmReportDates_All DateEvents]![txtBegDate] And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]) AND
((bukuangkby.TGLBPTIS_M) Between [Forms]![frmReportDates_All
DateEvents]![txtBegDate] And [Forms]![frmReportDates_All
DateEvents]![txtEndDate]) AND ((bukuangkby.TGL_pen) Between
[Forms]![frmReportDates_All DateEvents]![txtBegDate] And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]) AND
((bukuangkby.ATSPERCA_M) Between [Forms]![frmReportDates_All
DateEvents]![txtBegDate] And [Forms]![frmReportDates_All
DateEvents]![txtEndDate]) AND ((bukuangkby.ATSSUR1_K) Between
[Forms]![frmReportDates_All DateEvents]![txtBegDate] And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]) AND
((bukuangkby.ATSSUR2_K) Between [Forms]![frmReportDates_All
DateEvents]![txtBegDate] And [Forms]![frmReportDates_All
DateEvents]![txtEndDate]) AND ((bukuangkby.KMATIAN_K) Between
[Forms]![frmReportDates_All DateEvents]![txtBegDate] And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]) AND
((bukuangkby.KELMURT_K) Between [Forms]![frmReportDates_All
DateEvents]![txtBegDate] And [Forms]![frmReportDates_All
DateEvents]![txtEndDate]) AND ((bukuangkby.KELHILA_K) Between
[Forms]![frmReportDates_All DateEvents]![txtBegDate] And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]));

Thanks in advance

--
H. Frank Situmorang

What's the entire SQL of your query?

Incidentally, First is seldom useful...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Dough for your explanation, but when I tried it there is a syntax
error says: "3075 Syantax error in query expression
'First([Sum(IIf([TGLLahir] BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND
[Forms]![frmReportDates_All DateEvents]![txtEndDate], 1, 0))])'.

Can you help me again?. For your information the report is based on a
quiry
on each coulum of dates, I have a Criteria: BETWEEN
[Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND
[Forms]![frmReportDates_All DateEvents]![txtEndDate]

Thanks in advance


H. Frank Situmorang

While it may seem counterintuitive, the solution is to use Sum, not
Count:

Sum(dIIf([DateofBirth] BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND
[Forms]![frmReportDates_All DateEvents]![txtEndDate], 1, 0))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello,

I tried to have a report to show the events in our church. since Union
Query
is too complicated to my understanding, so I use Select Query and on
each
date event ( Birth, marriage, death etc) I use a filter by Form. with
the
criteria in the dates field:
Between [Forms]![frmReportDates_All DateEvents]![txtBegDate] And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]

However Although I filter only for Feb for example, all the dates of
birth
is still shown on the report. When I want to count each column, the
result
will be misleading ( is not waht we wantted). My question is how can we
make
a formula to be able to count only what we want. Can we make: = Count(
Date
of Birth) IF(Between [Forms]![frmReportDates_All
DateEvents]![txtBegDate]
And
[Forms]![frmReportDates_All DateEvents]![txtEndDate]).

Something like that?

I appreciate your help

 

[Frank Situmorang]

Thanks Mike for your comment, yes I have to admit that I develop this church
membership database is not because I am expert, but just because the help of
all you in this news troup.

The difference now is I put all the dates in the member table instead of in
the events table. I can not redo it, it will be a lot of efforts.
Actually if I just filter one by one of the dates it is easy, but it needs
many reports. Probably I need to make a dynamic filter which is based on one
report.

Thanks for your idea