help with COUNT

[octatyan]

Hi,

I have a form that 1 budget record at a time (budget for marketing, HR, payroll, etc...). Each budget can be amended (ie add more money to the budget). Each amendment is given a code such as mktg1 (for marketing budgets first amendment), mktg2, mktg3, etc...

I would like the form to show how many amendments have been made to the record that is being shown.

How do I do that? I'm thinking I would use a query that groups all the amendments under their respective budget. If this is the answer to my solution, how do I do that?

Also, when the form shows how many amendments were made, I would like it to show a # (ie 3) and not the actual amendments (which I think is what the query would show).

Any help is greatly appreciated!

 

[jaydwest]

Why don't you try an aggregate query to count the number of records for a budget. Since you didn't provide much detail, I get to pick the fields.

dim rst as dao.recordset
dim mysql as string

mysql = "SELECT Count(BudgetID) AS CountOfBudgetID FROM tbl_Budget WHERE (tbl_Budget.BudgetID = " & Me![BudgetID] & ");"

set rst = CurrentDB.openrecordset(mysql)
If not rst.eof then
Me![Count] = NZ(rst!CountOfBudgetID,0)
End If

 

[bradcccs]

You could use DCount()

Plenty of references on the forum

Search for: DCount

Post back if you have no luck

Brad.