Hello Again,

I want to assign a number to each record that will be part of a shipping
number. I want the number value to count up until the contract number
changes. Then, I want the number to go back to 1 and start counting up again
until the next contract change.

For example

Contract 1111111 Box 1 of 2 Number Value: 1
Contract 1111111 Box 2 of 2 Number Value: 2
Contract 1111111 Skid 1 of 1 Number value: 3
Contract 2222222 Box 1 of 2 Number value: 1
Contract 2222222 Box 2 of 2 Number value: 2
Contract 2222222 Skid 1 of 1 Number value: 3
Contract 2222222 Bundle 1 of 2 Number value: 4
Contract 2222222 Bundle 2 of 2 Number value: 5
..
..
..



I posted this question a few days ago and was told to use the DCount()
function with Contract_Number as my criteria. I tried that in a query, but
my value is always the total number of records:

Contract 1111111 Box 1 of 2 Number Value: 8
Contract 1111111 Box 2 of 2 Number Value: 8
Contract 1111111 Skid 1 of 1 Number value: 8
Contract 2222222 Box 1 of 2 Number value: 8
Contract 2222222 Box 2 of 2 Number value: 8
Contract 2222222 Skid 1 of 1 Number value: 8
Contract 2222222 Bundle 1 of 2 Number value: 8
Contract 2222222 Bundle 2 of 2 Number value: 8

The DCount might be the solution, but I need some help setting up the
expression, because I am not doing it right. I need this number value for a
shipping code for a company we do business with. They have a specific
format they use, and I have to stick with it. When I get this last number,
I will use concatenate to join all the parts of the shipping code. Can I set
this up in a table, or will I need to run a query? Any help would be
appreciated.

Thanks,

Chad

Hi:

Put this into a module

On Tue, 29 Mar 2005 22:19:44 GMT, "ChadDiesel"
<shaqattack1992-newsgroups (AT) yahoo (DOT) com> wrote:

Hi:
Put this into a module:

Option Compare Database
Option Explicit

Global cc As Long
Global svcontract As String

Public Function zerocc()
cc = 0
svcontract = ""
End Function

Public Function AddToCC(contract As String)
If contract <> svcontract Then
svcontract = contract
cc = 0
End If
cc = cc + 1
AddToCC = cc
End Function

Create an update query:

This is the sql:
UPDATE Contract SET Contract.[count] = AddToCC([Contract]![cnum]);

where count is your counter field and cnum is the string containing
contract number.

You may need to change svcontract and contract to Long if that is how
it is defined in your db.

Good Luck

Ira Solomon

On Tue, 29 Mar 2005 22:19:44 GMT, "ChadDiesel"
<shaqattack1992-newsgroups (AT) yahoo (DOT) com> wrote: