Using DMax() on a String to add Sequential number on a group

[batwings]

Hi there

I have searched and searched but to no avail,

My question is can I use DMax() to add a Sequential Number to the end of groups of Strings.

My Field is Datatype String

The data looks as below
I would like to add -001 -002 -003... to each Group

A-B1-1-330-01 would end up like A-B1-1-330-01-001
A-B1-1-330-01 would end up like A-B1-1-330-01-002
A-B1-1-330-01 would end up like A-B1-1-330-01-003
A-B1-1-330-02 would end up like A-B1-1-330-01-001
A-B1-1-330-02 would end up like A-B1-1-330-01-002
A-B1-1-330-02 would end up like A-B1-1-330-01-003
A-B1-1-330-02 would end up like A-B1-1-330-01-004
A-B1-1-330-03 would end up like A-B1-1-330-01-001
A-B1-1-330-03 would end up like A-B1-1-330-01-002

If not with DMax() what other way would it be possible to do this?

 

[Galaxiom]

The easy way is to separate the value into two fields.
One for the numerical suffix which increments and the other for the rest of the string field.
Concatenate them for display on your forms and reports.

[MainPart] & Format([suffix],"000")

P.S. I think the examples in your original post are not quite what you intended.

 

[Galaxiom]

If you really want to keep it in one field then you can parse the suffix to get a number.
Val(Right([wholestring],3))

Then do DMax on that and concatenate as above.
IMO, the two fields is a much better option as it is simpler to see and uses much less processing.

 

[batwings]

Galaxiom

many Thanks for your help

You are of course correct my examples were flawed from the start and should have looked like this:-

A-B1-1-330-01 would end up like A-B1-1-330-01-001
A-B1-1-330-01 would end up like A-B1-1-330-01-002
A-B1-1-330-01 would end up like A-B1-1-330-01-003
A-B1-1-330-02 would end up like A-B1-1-330-02-001
A-B1-1-330-02 would end up like A-B1-1-330-02-002
A-B1-1-330-02 would end up like A-B1-1-330-02-003
A-B1-1-330-02 would end up like A-B1-1-330-02-004
A-B1-1-330-03 would end up like A-B1-1-330-03-001
A-B1-1-330-03 would end up like A-B1-1-330-03-002

I'm interested in going the route of splitting the String into 2 parts and doing as you say,

][MainPart] & Format([suffix],"000") [/QUOTE
but I'm still not sure how to do it in a query.

 

[Galaxiom]

Add the new suffix field to the table then run two update queries where fieldname is the existing field and suffix is the new incrementing field.

UPDATE [Tablename] SET [suffix] = Val(Right([fieldname],3))

UPDATE [Tablename] SET [fieldname] = Left([fieldname],14)

This is the expression for combining them in a textbox on a form:
=[fieldname] & Format([suffix],"000")

Hope you can see the way it works.

 

[DCrake]

If you then create a query that is grouped by the suffix and maxes it as well you should be able to use a dlookup on that query to get the max used and then add one to it.

An alternative is to have an extra field in the main table that hold the last increment number issued against the suffix and use this as a reference point for the next increment.

David

 

[batwings]

Thanks for all of the help and suggestions but I'm not sure if they can get me what I am really after...or maybe I just do not fully understand! or didn't explain well enough.

Basically I need to find each group that has the same string and find out how many rows are in each group, if I have 5 rows with identical strings in my first group I need to know how to find that out and then the suffix will need to be /001, /002, /003, /004, /005 and if the next group has only 2 rows the suffix will be /001, /002, /003 (I used slashes this time for clarity)

From A-B1-1-330-01 to A-B1-1-330-01/001
From A-B1-1-330-01 to A-B1-1-330-01/002
From A-B1-1-330-01 to A-B1-1-330-01/003
From A-B1-1-330-01 to A-B1-1-330-01/004
From A-B1-1-330-01 to A-B1-1-330-01/005

The next group could be like:-

From A-B1-1-330-17 to A-B1-1-330-17/001
From A-B1-1-330-17 to A-B1-1-330-17/002
From A-B1-1-330-17 to A-B1-1-330-17/003

 

[Galaxiom]

The suffix part needs to be in a separate field.
The DMAX needs to look at the suffix where the 01 0r 17 part of the main code (in your examples) matches the value you are working on.

I would probably also put this into a separate field since it designates some other attribute of the record. lets call it "fieldx" for now.

Something like this will find the maximum suffix where fieldx was equal to a textbox on a form:
DMax("suffixfield","tablename", "fieldx= Forms!formname!textboxname")