Query to COUNT based on Condition

[blyssz]

I have a TABLE A in MS access which contains following fields:
Drug name, Type, [Certified date], [Target certified date]
For each Drug there is a [Target certified date] which is always populated, and a [Certified date] field that could be null if Drug has Not been Certified or a DATE is there if Drug has been certified..
In another TABLE B, I have a "Report date" field and "#of Certified", "#of Target certified fields".
And I want to compare "Report Date" with [Target Certified date] and [Certified date] and I want to COUNT the #of Target certified where
Target certified date = < reportdate and #ofcertified where [Certified Date] is not null and Certified Date =<reportdate
I am writing a query which is reading through each occurance of Table A until EOF.
And creating a form named ProjectStatus_Test which contains a text box ProjectStatus and entering the the date which shows the status of the project through the selected date in ProjectStatus
The Code is :
#planned=0
#actual=0
Table_A.Movefirst
do until table_A eof
If MyTable_B![Report Date] >= Forms![ProjectStatus_Test].Form![ProjectStatus] Then
if query![Target Certified date]<= [Table B]![Report Date] then
Planned=Planned+1
If query![Certified Date]<=[Table B]![Report Date] and if not Is Null then
Actual=Actual+1;
query.MoveNext
loop
MyTable_B.Edit
If MyTable_B![Report Date] = Forms![ProjectStatus_Test].Form![ProjectStatus] Then
MyTable_B![#ofcertified] = Actual
Else
If MyTable_B![Report Date] > Forms![ProjectStatus_Test].Form![ProjectStatus] Then
MyTable_B![#oftargetcertified] = Planned
End If
End If
MyTable_Table_B.Update
'make no changes to stored data
End If
MyTable_TableB.MoveNext
Loop
…Now after running this code , I am not getting count of #oftargetcertified and #ofactualcertified in Table_B.
Any suggestions would be appreciated.

 

[LisaWay]

There seem to be several syntax errors in your code. For example,

do until table_A eof

should be:

do until table_A.eof

and

Actual=Actual+1;

should be:

Actual=Actual+1

Also your "If Then Else EndIf" statements do not appear to match up. Please fix these problems and then repost the code here if necessary.

 

[blyssz]

I will carefully review the syntax errors one more time and will update accordingly.
Thanks for the pointer.