Concatenation of Fields

[akik]

On my form I want to create a field which will concatenate as follows.

I know how to concatenate but don't know how to create the last calculation.

12357-“Cat”-“DocNo”-“SubDocNo”-[(needs to go to the table and find the greatest value for field (which has a max value for that combination of (Cat and DocNo and SubDocNo)+ 1 and display that value in this field)]

eg. 12357-PRO-10-02-0001

Next time when the button is pressed it should become

12357-PRO-10-02-0002

If User picks Cat = TNO then

12357-TNO-10-02-0001

in the next instance should be

12357-TNO-10-02-0002

User will pick the Cat, DocNo and SubDocNo from a drop down list.

where 12357 is fixed values and will not change
Doc Type is string
DocNo is number
SubDocNo is number



Pls help…

Thanks

 

[LittleSmudge]

You havn't said so explicitly but I'm assuming that "Cat", "DocNo" and "SubDocNo" and "IndexNo" are stored in separate fields in the table

If not then AT Least store "Cat"-"DocNo"-SubDocNo" in a separate field to the Index and concatenate when displaying on the Form/Reports

Solution
"SELECT Top 1 IndexNo FROM tblName " _
"WHERE ((fldCat = '" & txtCat & "') " _
"AND (fldDocNo = " & txtDocNo & &quot " _
"AND (fldSubDocNo = " & txtSubDocNo & &quot) " _
"ORDER BY IndexNo ;"

Will return the current highest value of Index number for the given combination of Cat, DocNo and SubDocNo.
Then increment it and store it in the relevant field

fldxxx represent the table's field names for these values
and
txtxxx represent the controls on the form.



G LS