Calculate a No. in one field based on result in another field

[CindiN]

Hi,
I want to be able to look in calcResults field to find "Completed", then look to the Credits field to find the "#". Then at the bottom of the report give a total of Credits that have a calcResults status of "Completed".

I have a text box in the Page Footer called txtCredits for this total. My formulas just don't seem to be working.

Thank you for any help you can give.
CindiN

 

[CTOROCK]

If I understand you question correctly, you could

Dim StrField as String
Dim Db as Database
Dim Rst as Recordset
Set Db = CurrentDb
Set Rst = Db.OpenRecordset("Tbl_YourTable", dbOpenDynaset)

StrField = TxtYourBox.Value
Rst.FindFirst "Your Field" = """ & StrField & """"

From there you can get the info:

TxtCredits = Rst.Feilds("Credits&quot.Value


Is this what you were looking for? "The greatest risk, is not taking one."

 

[CindiN]

CTOROCK,

Thanks for your response, however, I'm not too familiar with VB. I created a new text box in the Page Footer, then put the statement you wrote above in a module. I replaced my information as follows:

Dim StrField As String
Dim Db As Database
Dim Rst As Recordset
Set Db = CurrentDb
Set Rst = Db.OpenRecordset("Worksheet2QRY", dbOpenDynaset)

StrField = calcResults.Value
Rst.FindFirst "Worksheet1Qry.TRAINING_UNITS" = """ & StrField & """""

From there you can get the info:

txtCredits = Rst.Feilds("Worksheet1Qry.TRAINING_UNITS&quot.Value

Is this correct? Am I supposed to replace the word "Value" above with "Completed"? 'Cause when I do, I get an error.

I'm sorry, is there an easier way, maybe just opening my txtCredits properties and putting in a calculation in the Control Source?

Thank you so much for your patience.
CindiN

 

[CTOROCK]

Try this:
TxtCredits = Rst.Feilds("TRAINING_UNITS&quot.Value

Is that where you were getting an error? Because you no longer need to specify where to get "Training_Units" because it's getting it from Rst which is your table.

Then put this at the end

Rst.Close

does this help? "The greatest risk, is not taking one."