Sequential Numbering Expression Help

[mcranda]

I have two fields that make up a number: Needs Approval Item and SearchTerm. These two fields are stored seperately in the table as well are concatenated (SearchTerm + Needs Approval Item) before update into a field called CMIR 2. My probelm is that I need to sequentially number the CMIR 2 record starting with a 1.

SeqNum: DCount("[CMIR 2]","112 Add Sequential Number to CMIR 2","[CMIR 2] = '" & [CMIR 2] & "'")-DCount("[CMIR 2]","112 Add Sequential Number to CMIR 2","[CMIR 2] = '" & [CMIR 2] & "' AND [Cat Number] > " & [Cat Number])

Here is my current expression. 112 Add Sequetial Number to CMIR 2 is the Query name. Cat Number is my unique ID. CMIR 2 is what I need sequentially numbered.

It is coming up with an error message.

Thanks

 

[pr2-eugin]

What is the error message that you are getting?

 

[mcranda]

Just saying #Error when I run it the query.

 

[mcranda]

If I delete the items after -Dcount then it just counts the number of CMIR 2 records instead of sequentially numbering them. Somehow this part of the formula is causing the error

-DCount("[CMIR 2]","112 Add Sequential Number to CMIR 2","[CMIR 2] = '" & [CMIR 2] & "' AND [Cat Number] > " & [Cat Number])

 

[pr2-eugin]

Did you try the other way around?? I mean try placing SeqNum: DCount("[CMIR 2]","112 Add Sequential Number to CMIR 2","[CMIR 2] = '" & [CMIR 2] & "' AND [Cat Number] > " & [Cat Number])What do you get???

 

[mcranda]

If i just put that expression in:
SeqNum: DCount("[CMIR 2]","112 Add Sequential Number to CMIR 2","[CMIR 2] = '" & [CMIR 2] & "' AND [Cat Number] > " & [Cat Number])

I get #Error when it is run

 

[mcranda]

Maybe I should try a ranking formula.

 

[mcranda]

Expr1: DCount("*","112 Add Sequential Number to CMIR 2","[CMIR 2] <= " & [CMIR 2])

I tried this one and my error stated missing operator in query expression '[CMIR 2] <= 165rT Needs Approval

 

[pr2-eugin]

Since CMIR 2 holds string values you need to enclose them inside single quotes.. what you had earlier was fine.. I am still puzzled.. if possible could you just upload a sample db takin away sensitive information??

 

[mcranda]

Now I am getting quite a few errors in the new database. I put the table and the query in a database and took out all the other fields and included only a subset of the table as well.

 

[mcranda]

I need URGENT Help. Please someone.