DLookup and Sequential Numbers

[Owen]

I have looked up and down for an answer to this and cannot find one. I need
to be able to assign sequential numbers to similar records. For example,

Record Number
Job 1 1
Job 1 2
Job 2 1
Job 2 2
Job 1 3

Any help on this will be greatly appreciated,

TIA

Owen

 

[XPS350]

I have looked up and down for an answer to this and cannot find one.  Ineed
to be able to assign sequential numbers to similar records.  For example,  

Record       Number
Job 1             1
Job 1             2
Job 2             1
Job 2             2
Job 1             3

Any help on this will be greatly appreciated,

TIA

Owen

I think "Dmax" will be more usefull in this case. Something like:
Nz(DMax("Number", "YourTable", "Record='Job X'")) + 1

Groeten,

Peter
http://access.xps350.com

 

[John W. Vinson]

I have looked up and down for an answer to this and cannot find one. I need
to be able to assign sequential numbers to similar records. For example,

Record Number
Job 1 1
Job 1 2
Job 2 1
Job 2 2
Job 1 3

Any help on this will be greatly appreciated,

TIA

Owen

You need at least one other field to define an order of records; a table by
itself is just an unsorted bag of data, so there's nothing to distinguish the
multiple instances of Job 1.

ASSUMING you have an Autonumber named ID (or some other field which you can be
sure is unique for each record), and that Record is a Text field, this will
work. It may get pretty slow for large tables:

Number: DCount("*", "MyTableName", "[Record] = '" & [Record] & "' AND ID <= "
& [ID])

 

[Owen]

The code worked well but it only seems to return the number 1, it never goes
beyond that. My code is:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strJobNum As String
Dim strReportNum As String
Dim strJobReportNum As String

strJobNum = cboProjectSelect.Column(2) ' this is used for final report number

strReportNum = Nz(DMax("[ReportNumber]", "AreaTurnoverFromtbl",
"[ReportNumber] =#" & strJobNum & "#")) + 1


strJobReportNum = strJobNum & " - " & strReportNum 'returns job # &
report #

ReportNumbertxt.Value = strJobReportNum ' inserts result in a textbox on
the form


End Sub

Any ideas on what I've done wrong?

 

[XPS350]

The code worked well but it only seems to return the number 1, it never goes
beyond that.  My code is:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strJobNum As String
Dim strReportNum As String
Dim strJobReportNum As String

strJobNum = cboProjectSelect.Column(2)  ' this is used for final report number

strReportNum = Nz(DMax("[ReportNumber]", "AreaTurnoverFromtbl",
"[ReportNumber] =#" & strJobNum & "#")) + 1

strJobReportNum = strJobNum & " - " & strReportNum     'returns job# &
report #

ReportNumbertxt.Value = strJobReportNum   ' inserts result in a textbox on
the form

End Sub

Any ideas on what I've done wrong?

I think "Dmax" will be more usefull in this case. Something like:
Nz(DMax("Number", "YourTable", "Record='Job X'")) + 1

Peter
http://access.xps350.com
.

Maybe the ## are then problem. You only use those withe date/time
fields. I gues JobNum is no such field. Use '.

Groeten,

Peter
http://access.xps350.com