Q: How to add a number series field in a query?

[usr_X]

My query returns a list of state abbreviations from a state and territory table. I want to add a calculated field to the query where the first record is 1 and each record thereafter is incremented by 1 until the last record, e.g. 1 AL, 2 AK, 3 AZ, 4 AR.... 50 WY. Is there a way to do this not using VBA? Thanks in advance, Usr_X.

 

[pbaldy]

It would be easier to do in a report, but you could use a DCount() to count records <= the current one. That would require they be sorted of course.

 

[usr_X]

Okay, this is good except I am not sure of the syntax needed to do this, namely to identify <= the current record. About as far as I've gotten is Dcount(["fldState"],"tblStatesAndTerritories") which, of course, only returns the total count for all records, not the total count of the current record and preceding records.

 

[pbaldy]

Try

Dcount("fldState","tblStatesAndTerritories", "fldState <='" & fldState & "'")

 

[usr_X]

Paul, this does exactly what I need. I am exultantly pleased; thanks for you help.

However, I have found one important item to note. Dcount() returns #Error if the field to count is not a numeric field, e.g. Dcount("fldString","tblData","fldString <= " & [fldString] & "") returns #Error whereas Dcount("fldNumber","tblData","fldNumber<= " & [fldNumber] & "") returns the count of the current record and preceding records.

Again, thanks very much. Usr_X.

 

[namliam]

A number is not enclosed by anything a string needs to be quoted... A date hashed...
Number: "fldString <= " & [fldString] & ""
Text: "fldString <= '" & [fldString] & "'"
Date: "fldString <= #" & [fldString] & "#"

 

[pbaldy]

A good reference to add to your bookmarks:

http://www.mvps.org/access/general/gen0018.htm