Form to display result

J

Jeff

Hi,

The following is part of my program used to count the result of hearing test
on baby and it works. If the baby passes the first time on both ears, we
issue a pass. If the baby fails the first time, either on one ear or both
ears, we perform a second time. If pass on both ears, we issue a pass. If
fail either on one ear or both ears, we issue a fail.

I can display the result with MsgBox, but I want to display the results on a
"Form" like a table as follow:

Year/Month of Test Total number done No. Pass No. Fail Pass rate
Fail rate

How can I do that? Should I store the results in a "temporary table" first
then get the data from the temporary table to display them on a "Form"? Thank
you.


Private Sub btnCount_Click()

Dim strMinDate As String
Dim strMaxDate As String
Dim dMinDate As Date
Dim dMaxDate As Date
Dim dScreenMonth As Date
Dim intMonthCount As Integer
Dim lngPass1 As Long
Dim lngPass2 As Long
Dim lngPass As Long
Dim lngFail2 As Long

strMinDate = DMin("[ScreenDate1]", "tbResult") 'I use String type for
ScreenDate1
strMaxDate = DMax("[ScreenDate1]", "tbResult")
dMinDate = TxtDate(strMinDate) 'I have a function to convert String type
ScreenDate1 to Date type
dMaxDate = TxtDate(strMaxDate)
dScreenMonth = dMinDate
intMonthCount = DateDiff("m", dMinDate, dMaxDate) + 1

Dim i As Integer
Dim strPass As String
Dim strFail As String

'pass=1, fail=2

For i = 1 To intMonthCount
lngPass1 = DCount("[MasterID]", "tbResult", "[RResult1] = 1 And
[LResult1] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) &
"'")
lngPass2 = DCount("[MasterID]", "tbResult", "[RResult2] = 1 And
[LResult2] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) &
"'")
lngPass = lngPass1 + lngPass2
lngFail2 = DCount("[MasterID]", "tbResult", "[RResult2] = 2 And
Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "' Or [LResult2] = 2
And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'")
strPass = strPass & lngPass & " "
strFail = strFail & lngFail2 & " "

dScreenMonth = DateAdd("m", 1, dScreenMonth)
Next

MsgBox strPass & strFail

End Sub
 
J

Jeanette Cunningham

Hi Jeff,
build a form that is based on tblResult.
You can use the form wizard if you haven't built a form before.
 
J

Jeff

Hi Jeanette,

tbResult stores the test result. The result of calculation (counting) based
on tbResult are stored in Variables and the number of Variable will increase
month after month. My problem is how to display these Variables (result) on a
Form. I am thinking if I should use array. Any ideas?

--
Jeff


Jeanette Cunningham said:
Hi Jeff,
build a form that is based on tblResult.
You can use the form wizard if you haven't built a form before.



Jeff said:
Hi,

The following is part of my program used to count the result of hearing
test
on baby and it works. If the baby passes the first time on both ears, we
issue a pass. If the baby fails the first time, either on one ear or both
ears, we perform a second time. If pass on both ears, we issue a pass. If
fail either on one ear or both ears, we issue a fail.

I can display the result with MsgBox, but I want to display the results on
a
"Form" like a table as follow:

Year/Month of Test Total number done No. Pass No. Fail Pass rate
Fail rate

How can I do that? Should I store the results in a "temporary table" first
then get the data from the temporary table to display them on a "Form"?
Thank
you.


Private Sub btnCount_Click()

Dim strMinDate As String
Dim strMaxDate As String
Dim dMinDate As Date
Dim dMaxDate As Date
Dim dScreenMonth As Date
Dim intMonthCount As Integer
Dim lngPass1 As Long
Dim lngPass2 As Long
Dim lngPass As Long
Dim lngFail2 As Long

strMinDate = DMin("[ScreenDate1]", "tbResult") 'I use String type for
ScreenDate1
strMaxDate = DMax("[ScreenDate1]", "tbResult")
dMinDate = TxtDate(strMinDate) 'I have a function to convert String type
ScreenDate1 to Date type
dMaxDate = TxtDate(strMaxDate)
dScreenMonth = dMinDate
intMonthCount = DateDiff("m", dMinDate, dMaxDate) + 1

Dim i As Integer
Dim strPass As String
Dim strFail As String

'pass=1, fail=2

For i = 1 To intMonthCount
lngPass1 = DCount("[MasterID]", "tbResult", "[RResult1] = 1 And
[LResult1] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) &
"'")
lngPass2 = DCount("[MasterID]", "tbResult", "[RResult2] = 1 And
[LResult2] = 1 And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) &
"'")
lngPass = lngPass1 + lngPass2
lngFail2 = DCount("[MasterID]", "tbResult", "[RResult2] = 2 And
Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "' Or [LResult2] =
2
And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'")
strPass = strPass & lngPass & " "
strFail = strFail & lngFail2 & " "

dScreenMonth = DateAdd("m", 1, dScreenMonth)
Next

MsgBox strPass & strFail

End Sub
Click to expand...


.
Click to expand...
 
J

Jeanette Cunningham

The usual way, using Access, is to use a query as the record source for a
form.
If you need calculated fields for your form, you can use the query to do the
calculations, or you can use the form to do the calculations or you can use
a combination of both query and form to do the calculations for the
calculated fields.

I would expect that you would store things like Pass or Fail, the type of
test and the date of each test in one table. Use another table to store the
personal details of each baby tested.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Jeff said:
Hi Jeanette,

tbResult stores the test result. The result of calculation (counting)
based
on tbResult are stored in Variables and the number of Variable will
increase
month after month. My problem is how to display these Variables (result)
on a
Form. I am thinking if I should use array. Any ideas?

--
Jeff


Jeanette Cunningham said:
Hi Jeff,
build a form that is based on tblResult.
You can use the form wizard if you haven't built a form before.



Jeff said:
Hi,

The following is part of my program used to count the result of hearing
test
on baby and it works. If the baby passes the first time on both ears,
we
issue a pass. If the baby fails the first time, either on one ear or
both
ears, we perform a second time. If pass on both ears, we issue a pass.
If
fail either on one ear or both ears, we issue a fail.

I can display the result with MsgBox, but I want to display the results
on
a
"Form" like a table as follow:

Year/Month of Test Total number done No. Pass No. Fail Pass
rate
Fail rate

How can I do that? Should I store the results in a "temporary table"
first
then get the data from the temporary table to display them on a "Form"?
Thank
you.


Private Sub btnCount_Click()

Dim strMinDate As String
Dim strMaxDate As String
Dim dMinDate As Date
Dim dMaxDate As Date
Dim dScreenMonth As Date
Dim intMonthCount As Integer
Dim lngPass1 As Long
Dim lngPass2 As Long
Dim lngPass As Long
Dim lngFail2 As Long

strMinDate = DMin("[ScreenDate1]", "tbResult") 'I use String type for
ScreenDate1
strMaxDate = DMax("[ScreenDate1]", "tbResult")
dMinDate = TxtDate(strMinDate) 'I have a function to convert String
type
ScreenDate1 to Date type
dMaxDate = TxtDate(strMaxDate)
dScreenMonth = dMinDate
intMonthCount = DateDiff("m", dMinDate, dMaxDate) + 1

Dim i As Integer
Dim strPass As String
Dim strFail As String

'pass=1, fail=2

For i = 1 To intMonthCount
lngPass1 = DCount("[MasterID]", "tbResult", "[RResult1] = 1 And
[LResult1] = 1 And Month(TxtDate(ScreenDate1)) ='" &
Month(dScreenMonth) &
"'")
lngPass2 = DCount("[MasterID]", "tbResult", "[RResult2] = 1 And
[LResult2] = 1 And Month(TxtDate(ScreenDate1)) ='" &
Month(dScreenMonth) &
"'")
lngPass = lngPass1 + lngPass2
lngFail2 = DCount("[MasterID]", "tbResult", "[RResult2] = 2 And
Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "' Or
[LResult2] =
2
And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'")
strPass = strPass & lngPass & " "
strFail = strFail & lngFail2 & " "

dScreenMonth = DateAdd("m", 1, dScreenMonth)
Next

MsgBox strPass & strFail

End Sub
Click to expand...


.
Click to expand...
Click to expand...
 
J

Jeff

Hi Jeanette,

Very glad that you can come back to my question. Yes, I have tbPatient to
store babies' personal details. tbResult stores the test result (hearing test
only). Although some baby may need to test a second time, each baby has only
one record in tbResult. tbPatient and tbResult are one to one in relation.
The fields in tbResult are: ScreenDate1 for date, RResult1 for test result of
right ear and LResult1 for test result of left ear tested the first time.
RResult2 and LResult2 for right & left ear tested the second time. RResult1,
LResult1, RResult2 and LResult2 store 1 for Pass or 2 for Fail. The rule for
final conclusion of "Pass" or "Fail" is: If the baby passes both ears for the
first time, i.e. RResult1=1 AND LReslt1=1, we give a "PASS". If either right
or left ear fails or both ears fail the first time, we perform the test
again. Then, if the baby passes both ears, i.e. RResult2=1 AND LResult2=1, we
give a "PASS". If either right or left ear fails or both ears fail this time,
we give a "FAIL".

Now, I want to general a statistic table to show the total number of babies
tested, the number of babies passed or failed in each month. So, I need to
search the field "ScreenDate1" in tbResult with DMin() to find the earliest
date then count one month by one month until the end of tbResult. I use (a)
DCount() to count the number of the babies who pass the first time
(RResult1=1 AND LResult1=1), (b)DCount to count the number of babies who fail
the first time but pass the second time (RResult2=1 AND LResult2=1),
(c)DCount to count those who fail the second time (RREsult2=2 OR/AND
LResult2=2). The total number passed will be (a)+(b), and the total number
tested will be (a)+(b)+(c).

The results of counting are stored in "Variables", I'd like to display these
results in a "Form" like a table with each month's result in a row. As time
goes by, the records in tbResult will increase and the number of months
(rows) to display will increase. Any ideas? Thank you.

--
Jeff


"Jeanette Cunningham" 來函：
The usual way, using Access, is to use a query as the record source for a
form.
If you need calculated fields for your form, you can use the query to do the
calculations, or you can use the form to do the calculations or you can use
a combination of both query and form to do the calculations for the
calculated fields.

I would expect that you would store things like Pass or Fail, the type of
test and the date of each test in one table. Use another table to store the
personal details of each baby tested.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Jeff said:
Hi Jeanette,

tbResult stores the test result. The result of calculation (counting)
based
on tbResult are stored in Variables and the number of Variable will
increase
month after month. My problem is how to display these Variables (result)
on a
Form. I am thinking if I should use array. Any ideas?

--
Jeff


Jeanette Cunningham said:
Hi Jeff,
build a form that is based on tblResult.
You can use the form wizard if you haven't built a form before.



Hi,

The following is part of my program used to count the result of hearing
test
on baby and it works. If the baby passes the first time on both ears,
we
issue a pass. If the baby fails the first time, either on one ear or
both
ears, we perform a second time. If pass on both ears, we issue a pass.
If
fail either on one ear or both ears, we issue a fail.

I can display the result with MsgBox, but I want to display the results
on
a
"Form" like a table as follow:

Year/Month of Test Total number done No. Pass No. Fail Pass
rate
Fail rate

How can I do that? Should I store the results in a "temporary table"
first
then get the data from the temporary table to display them on a "Form"?
Thank
you.


Private Sub btnCount_Click()

Dim strMinDate As String
Dim strMaxDate As String
Dim dMinDate As Date
Dim dMaxDate As Date
Dim dScreenMonth As Date
Dim intMonthCount As Integer
Dim lngPass1 As Long
Dim lngPass2 As Long
Dim lngPass As Long
Dim lngFail2 As Long

strMinDate = DMin("[ScreenDate1]", "tbResult") 'I use String type for
ScreenDate1
strMaxDate = DMax("[ScreenDate1]", "tbResult")
dMinDate = TxtDate(strMinDate) 'I have a function to convert String
type
ScreenDate1 to Date type
dMaxDate = TxtDate(strMaxDate)
dScreenMonth = dMinDate
intMonthCount = DateDiff("m", dMinDate, dMaxDate) + 1

Dim i As Integer
Dim strPass As String
Dim strFail As String

'pass=1, fail=2

For i = 1 To intMonthCount
lngPass1 = DCount("[MasterID]", "tbResult", "[RResult1] = 1 And
[LResult1] = 1 And Month(TxtDate(ScreenDate1)) ='" &
Month(dScreenMonth) &
"'")
lngPass2 = DCount("[MasterID]", "tbResult", "[RResult2] = 1 And
[LResult2] = 1 And Month(TxtDate(ScreenDate1)) ='" &
Month(dScreenMonth) &
"'")
lngPass = lngPass1 + lngPass2
lngFail2 = DCount("[MasterID]", "tbResult", "[RResult2] = 2 And
Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "' Or
[LResult2] =
2
And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'")
strPass = strPass & lngPass & " "
strFail = strFail & lngFail2 & " "

dScreenMonth = DateAdd("m", 1, dScreenMonth)
Next

MsgBox strPass & strFail

End Sub

--
Jeff


.
Click to expand...
Click to expand...


.
Click to expand...
 
J

Jeanette Cunningham

First, it is so much more difficult to learn about building forms when you
are using a one to one relationship.
One to one relationships are very rare and are best avoided when learning
how to use access.

I suggest you make the relationship between tbPatient and tbResult one to
many.
One patient can have many results. This is how you do the testing anyway.

For easy way to do the calculations for the statistics, redesign tbResult
like this:

tbResult-------
PrimaryKey ResultID
ForeignKey PatientID
DateResult
LEar - number, long integer
REar - number, long integer


If a baby passes the test for LEar, store 1 in the field LEar.
If a baby fails the test for REar, store 2 in in the field REar.

If a baby fails the first test, next time you test that baby, create a new
record.
It will have a later date than the first test for the same baby.
This way you can easily find out how many tests a baby had.


The general statistics table you mention does not need to be built ever.
You can get the statistics in a query whenever you want them.

Create a calculated field in a query built on both tbPatient and tbResult.

In a blank column in the query grid in the Field row, type
FinalResult: IIf([REar]+[LEar] = 2,"Pass","Fail")

Use the query as the record source for a form.
The form will show Pass or Fail each time each baby was tested.

To get the total number of tests each month, it is fairly simple to create a
totals query.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Jeff said:
Hi Jeanette,

Very glad that you can come back to my question. Yes, I have tbPatient to
store babies' personal details. tbResult stores the test result (hearing
test
only). Although some baby may need to test a second time, each baby has
only
one record in tbResult. tbPatient and tbResult are one to one in relation.
The fields in tbResult are: ScreenDate1 for date, RResult1 for test result
of
right ear and LResult1 for test result of left ear tested the first time.
RResult2 and LResult2 for right & left ear tested the second time.
RResult1,
LResult1, RResult2 and LResult2 store 1 for Pass or 2 for Fail. The rule
for
final conclusion of "Pass" or "Fail" is: If the baby passes both ears for
the
first time, i.e. RResult1=1 AND LReslt1=1, we give a "PASS". If either
right
or left ear fails or both ears fail the first time, we perform the test
again. Then, if the baby passes both ears, i.e. RResult2=1 AND LResult2=1,
we
give a "PASS". If either right or left ear fails or both ears fail this
time,
we give a "FAIL".

Now, I want to general a statistic table to show the total number of
babies
tested, the number of babies passed or failed in each month. So, I need to
search the field "ScreenDate1" in tbResult with DMin() to find the
earliest
date then count one month by one month until the end of tbResult. I use
(a)
DCount() to count the number of the babies who pass the first time
(RResult1=1 AND LResult1=1), (b)DCount to count the number of babies who
fail
the first time but pass the second time (RResult2=1 AND LResult2=1),
(c)DCount to count those who fail the second time (RREsult2=2 OR/AND
LResult2=2). The total number passed will be (a)+(b), and the total number
tested will be (a)+(b)+(c).

The results of counting are stored in "Variables", I'd like to display
these
results in a "Form" like a table with each month's result in a row. As
time
goes by, the records in tbResult will increase and the number of months
(rows) to display will increase. Any ideas? Thank you.

--
Jeff


"Jeanette Cunningham" ??:
The usual way, using Access, is to use a query as the record source for a
form.
If you need calculated fields for your form, you can use the query to do
the
calculations, or you can use the form to do the calculations or you can
use
a combination of both query and form to do the calculations for the
calculated fields.

I would expect that you would store things like Pass or Fail, the type of
test and the date of each test in one table. Use another table to store
the
personal details of each baby tested.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Jeff said:
Hi Jeanette,

tbResult stores the test result. The result of calculation (counting)
based
on tbResult are stored in Variables and the number of Variable will
increase
month after month. My problem is how to display these Variables
(result)
on a
Form. I am thinking if I should use array. Any ideas?

--
Jeff


:

Hi Jeff,
build a form that is based on tblResult.
You can use the form wizard if you haven't built a form before.



Hi,

The following is part of my program used to count the result of
hearing
test
on baby and it works. If the baby passes the first time on both
ears,
we
issue a pass. If the baby fails the first time, either on one ear or
both
ears, we perform a second time. If pass on both ears, we issue a
pass.
If
fail either on one ear or both ears, we issue a fail.

I can display the result with MsgBox, but I want to display the
results
on
a
"Form" like a table as follow:

Year/Month of Test Total number done No. Pass No. Fail Pass
rate
Fail rate

How can I do that? Should I store the results in a "temporary table"
first
then get the data from the temporary table to display them on a
"Form"?
Thank
you.


Private Sub btnCount_Click()

Dim strMinDate As String
Dim strMaxDate As String
Dim dMinDate As Date
Dim dMaxDate As Date
Dim dScreenMonth As Date
Dim intMonthCount As Integer
Dim lngPass1 As Long
Dim lngPass2 As Long
Dim lngPass As Long
Dim lngFail2 As Long

strMinDate = DMin("[ScreenDate1]", "tbResult") 'I use String type
for
ScreenDate1
strMaxDate = DMax("[ScreenDate1]", "tbResult")
dMinDate = TxtDate(strMinDate) 'I have a function to convert String
type
ScreenDate1 to Date type
dMaxDate = TxtDate(strMaxDate)
dScreenMonth = dMinDate
intMonthCount = DateDiff("m", dMinDate, dMaxDate) + 1

Dim i As Integer
Dim strPass As String
Dim strFail As String

'pass=1, fail=2

For i = 1 To intMonthCount
lngPass1 = DCount("[MasterID]", "tbResult", "[RResult1] = 1 And
[LResult1] = 1 And Month(TxtDate(ScreenDate1)) ='" &
Month(dScreenMonth) &
"'")
lngPass2 = DCount("[MasterID]", "tbResult", "[RResult2] = 1 And
[LResult2] = 1 And Month(TxtDate(ScreenDate1)) ='" &
Month(dScreenMonth) &
"'")
lngPass = lngPass1 + lngPass2
lngFail2 = DCount("[MasterID]", "tbResult", "[RResult2] = 2 And
Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "' Or
[LResult2] =
2
And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'")
strPass = strPass & lngPass & " "
strFail = strFail & lngFail2 & " "

dScreenMonth = DateAdd("m", 1, dScreenMonth)
Next

MsgBox strPass & strFail

End Sub

--
Jeff


.
Click to expand...


.
Click to expand...
Click to expand...
 
J

Jeff

Hi Jeanette,

I will try to redesign tbResult following your suggestion. Thank you very
much.
--
Jeff


"Jeanette Cunningham" 來函：
First, it is so much more difficult to learn about building forms when you
are using a one to one relationship.
One to one relationships are very rare and are best avoided when learning
how to use access.

I suggest you make the relationship between tbPatient and tbResult one to
many.
One patient can have many results. This is how you do the testing anyway.

For easy way to do the calculations for the statistics, redesign tbResult
like this:

tbResult-------
PrimaryKey ResultID
ForeignKey PatientID
DateResult
LEar - number, long integer
REar - number, long integer


If a baby passes the test for LEar, store 1 in the field LEar.
If a baby fails the test for REar, store 2 in in the field REar.

If a baby fails the first test, next time you test that baby, create a new
record.
It will have a later date than the first test for the same baby.
This way you can easily find out how many tests a baby had.


The general statistics table you mention does not need to be built ever.
You can get the statistics in a query whenever you want them.

Create a calculated field in a query built on both tbPatient and tbResult.

In a blank column in the query grid in the Field row, type
FinalResult: IIf([REar]+[LEar] = 2,"Pass","Fail")

Use the query as the record source for a form.
The form will show Pass or Fail each time each baby was tested.

To get the total number of tests each month, it is fairly simple to create a
totals query.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Jeff said:
Hi Jeanette,

Very glad that you can come back to my question. Yes, I have tbPatient to
store babies' personal details. tbResult stores the test result (hearing
test
only). Although some baby may need to test a second time, each baby has
only
one record in tbResult. tbPatient and tbResult are one to one in relation.
The fields in tbResult are: ScreenDate1 for date, RResult1 for test result
of
right ear and LResult1 for test result of left ear tested the first time.
RResult2 and LResult2 for right & left ear tested the second time.
RResult1,
LResult1, RResult2 and LResult2 store 1 for Pass or 2 for Fail. The rule
for
final conclusion of "Pass" or "Fail" is: If the baby passes both ears for
the
first time, i.e. RResult1=1 AND LReslt1=1, we give a "PASS". If either
right
or left ear fails or both ears fail the first time, we perform the test
again. Then, if the baby passes both ears, i.e. RResult2=1 AND LResult2=1,
we
give a "PASS". If either right or left ear fails or both ears fail this
time,
we give a "FAIL".

Now, I want to general a statistic table to show the total number of
babies
tested, the number of babies passed or failed in each month. So, I need to
search the field "ScreenDate1" in tbResult with DMin() to find the
earliest
date then count one month by one month until the end of tbResult. I use
(a)
DCount() to count the number of the babies who pass the first time
(RResult1=1 AND LResult1=1), (b)DCount to count the number of babies who
fail
the first time but pass the second time (RResult2=1 AND LResult2=1),
(c)DCount to count those who fail the second time (RREsult2=2 OR/AND
LResult2=2). The total number passed will be (a)+(b), and the total number
tested will be (a)+(b)+(c).

The results of counting are stored in "Variables", I'd like to display
these
results in a "Form" like a table with each month's result in a row. As
time
goes by, the records in tbResult will increase and the number of months
(rows) to display will increase. Any ideas? Thank you.

--
Jeff


"Jeanette Cunningham" ??:
The usual way, using Access, is to use a query as the record source for a
form.
If you need calculated fields for your form, you can use the query to do
the
calculations, or you can use the form to do the calculations or you can
use
a combination of both query and form to do the calculations for the
calculated fields.

I would expect that you would store things like Pass or Fail, the type of
test and the date of each test in one table. Use another table to store
the
personal details of each baby tested.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi Jeanette,

tbResult stores the test result. The result of calculation (counting)
based
on tbResult are stored in Variables and the number of Variable will
increase
month after month. My problem is how to display these Variables
(result)
on a
Form. I am thinking if I should use array. Any ideas?

--
Jeff


:

Hi Jeff,
build a form that is based on tblResult.
You can use the form wizard if you haven't built a form before.



Hi,

The following is part of my program used to count the result of
hearing
test
on baby and it works. If the baby passes the first time on both
ears,
we
issue a pass. If the baby fails the first time, either on one ear or
both
ears, we perform a second time. If pass on both ears, we issue a
pass.
If
fail either on one ear or both ears, we issue a fail.

I can display the result with MsgBox, but I want to display the
results
on
a
"Form" like a table as follow:

Year/Month of Test Total number done No. Pass No. Fail Pass
rate
Fail rate

How can I do that? Should I store the results in a "temporary table"
first
then get the data from the temporary table to display them on a
"Form"?
Thank
you.


Private Sub btnCount_Click()

Dim strMinDate As String
Dim strMaxDate As String
Dim dMinDate As Date
Dim dMaxDate As Date
Dim dScreenMonth As Date
Dim intMonthCount As Integer
Dim lngPass1 As Long
Dim lngPass2 As Long
Dim lngPass As Long
Dim lngFail2 As Long

strMinDate = DMin("[ScreenDate1]", "tbResult") 'I use String type
for
ScreenDate1
strMaxDate = DMax("[ScreenDate1]", "tbResult")
dMinDate = TxtDate(strMinDate) 'I have a function to convert String
type
ScreenDate1 to Date type
dMaxDate = TxtDate(strMaxDate)
dScreenMonth = dMinDate
intMonthCount = DateDiff("m", dMinDate, dMaxDate) + 1

Dim i As Integer
Dim strPass As String
Dim strFail As String

'pass=1, fail=2

For i = 1 To intMonthCount
lngPass1 = DCount("[MasterID]", "tbResult", "[RResult1] = 1 And
[LResult1] = 1 And Month(TxtDate(ScreenDate1)) ='" &
Month(dScreenMonth) &
"'")
lngPass2 = DCount("[MasterID]", "tbResult", "[RResult2] = 1 And
[LResult2] = 1 And Month(TxtDate(ScreenDate1)) ='" &
Month(dScreenMonth) &
"'")
lngPass = lngPass1 + lngPass2
lngFail2 = DCount("[MasterID]", "tbResult", "[RResult2] = 2 And
Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "' Or
[LResult2] =
2
And Month(TxtDate(ScreenDate1)) ='" & Month(dScreenMonth) & "'")
strPass = strPass & lngPass & " "
strFail = strFail & lngFail2 & " "

dScreenMonth = DateAdd("m", 1, dScreenMonth)
Next

MsgBox strPass & strFail

End Sub

--
Jeff


.



.
Click to expand...
Click to expand...



.
Click to expand...
 
Top