Sequence No in order

[bronson_mech]

Hello guys,

i need a help, how to create a Sequence # in report instead of Auto number........



In Table i created ID as Auto number........ as well as in form also auto number will be formed according to our entry but in Report i want each line item should start with 1,2,3...... for eg: if see the image.......if i enter issue slip No it gives me list of record related to issue slip No, in Sq No column instead of 11,12,13.... i need Sq no as 1,2,3.......... Is that Possible............... can anyone help me please.............

[AlexHedley]

Replace the TestTable with your Table/Query and add in the Fields you want to show instead of Num1, date1 etc

SELECT DCount("id","TestTable","id <= " & [id]) AS Counter, Num1, date1, TestTable.id
FROM TestTable 
ORDER BY TestTable.id;

[bronson_mech]

mr.alex

where do i need to add...... whether in SQ No Control Source (in Expression builder) or where can u pls help me i'm just an beginner.........

[AlexHedley]

If you create it as a new Query and the base your Report on this Query and drag the new Sequence Field on too.

[bronson_mech]

ok right now this is the Query i'm using it in my Report. in this, IssueID is the Sqno....... so do i need to add this code in this query in expression build?..... or else can u provide a sample file for example.....pls..........

[bronson_mech]

sry i forgot attach the image

[AlexHedley]

You could create a new Query based on qryIssuedDetails.
Add the following as a new Field then pull down the * to get the original Fields you had.

Seq: DCount("IssueID","qryIssuedDetails","IssueID <= " & [IssueID])

[bronson_mech]

am i doing right...........

[AlexHedley]

No. You don't need it as a Criteria.

Add that in the Field Row (The first row) of the first column, then double click the * to make that the second column.

You may need to look at your ID value too.

Also the Code tags have caused an issue with the & (you don't need "amp;")

[bronson_mech]

now i'm right..........

[AlexHedley]

DCount("IssueID","qryIssuedDetails","IssueID <= " & [IssueID])


&lt; &amp; are issues with the way I posted it, sorry about that.
&lt; should be <
&amp; should be &

[bronson_mech]

[AlexHedley]

I take it you don't need the empty rows?
Is IssueID not your Key column?
Why would they be empty? Were they test records?

You could add a Criteria to qryIssuedDetails where IssueID IS NOT NULL.

[bronson_mech]

no i don't ve empty field....... once i run this query i get that 2 error msg and then it automatically create that empty field.......

[AlexHedley]

Could you post a screenshot of the results from QryIssuedDetails?