RE: Custom Counting Function, need help!

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Gerwin,

I am impressed with your solution here as it would solve a very similar
problem I have. The only difference I have is that I do not have the returns
column that Waylen has. I have repeating account numbers but not the return
of their occurences, so I can't yet quite use your solution. As the table of
account numbers is into thousands I can't manually go through and assign an
occurence value. The account numbers are sorted. Do you know if it is
possible to compare account numbers and if they are a match increment an
occurence counter similar to having a second column of occurence number.
Ultimately what I'd like to do is be able to display on my form that if
multiple incidences of an account numbr occur the user will see "X of Y"
records for that particular account number, on a form that may be displaying
upto many thousands of records.

Thanks for any thoughts you may have,
Ian.

"Gerwin Berentschot" wrote:

Okay, that's a different issue. I came up with the next example solution
which most likely needs tweeking to fit your tables, queries, field names,
etc.

1. Created the following code in module:
Function Accountnumbers(accnum, accID)
' Count records handled before this one
icounted = DCount("[ID]", "[tbl_Accountnumbers]", "[ID]<" & accID & " and
Accountnumber=" & accnum)
' Return the value
Accountnumbers = icounted + 1
End Function

2. I used a table with fields 'ID' and 'Accountnumber'

3. Created a query and added both fields

4. Ordered query result ascending on ID

5. Added following field to the query: Runningcount:
Accountnumbers([Accountnumbers]";"[ID]")

Hope this works for you.

--
Gerwin Berentschot
gerwin@xxxxxxxxxxxx(nospam)
www.mraccess.nl



"Gerwin Berentschot" wrote:

Isn't it possible you just use count in your query? Just insert a field like:

AccountCount:
Dcount("[accountID]";"[tblname]";"[Accountnumber]="&[accountnumber])

--
Gerwin Berentschot
gerwin@xxxxxxxxxxxx(nospam)
www.mraccess.nl



"Waylen" wrote:

Hello,

I need to build a user defined function in vba that I can call from a query.
This function will need to count and assign the count value for each like
value in a field.
Example used would be the account number, but the account number can exist
once, or multiple times. I need an accurate count for each time the account
number occurs.
AccountNumber Function()
123 1
456 1
123 2
123 3
456 2

Any help appreciated, I've been struggling with this for many hours.

Sincerely,

~Waylen...
.

Relevant Pages

  • RE: Display queried records with Null values (null recordcount)
    ... tables related to Assets and Transactions tables in the query, ... since AssetID from tblAssets is a foreign ... The query would return multiple rows per account, ...
    (microsoft.public.access.gettingstarted)
  • RE: How to Retrieve and diplay the details in the form
    ... query for the main form. ... "naveen prasad" wrote: ... but when i change the account number the appropriate fields are not changing. ... and get the values of d1 and display in other fields in the form. ...
    (microsoft.public.access.queries)
  • Re: Distribute to user account objects in specific AD group in SCC
    ... listed in the query above, however I got the above error. ... It does NOT work the same way as users in a usergroup. ... problem upon removing the computer account from the AD group and invoking ... *could* attempt to leverage the Top Console User, and target "computers ...
    (microsoft.public.sms.admin)
  • Re: Distribute to user account objects in specific AD group in SCC
    ... a subset query can only have one column. ... It does NOT work the same way as users in a usergroup. ... computer account collections to download first, ... > *could* attempt to leverage the Top Console User, and target ...
    (microsoft.public.sms.admin)
  • Re: Distribute to user account objects in specific AD group in SCC
    ... For your second query, perhaps try using the subselect 'not in' ... This gave me a list of all the computers in the AD group. ... It does NOT work the same way as users in a usergroup. ... problem upon removing the computer account from the AD group and invoking ...
    (microsoft.public.sms.admin)