Max Query

[drewduncan]

Hi,

I have a field called which auto generates by a piece of code for every new record.

The field in question is a text field and has a prefix of 'PTCL' and then the number e.g. PTCL123 becomes PTCL124 on a new record and so on.

This works fine until the value becomes PTCL1000. Since it is not a numeric field and when I run a piece of SQL to find the max value of the 'number part' 999 is seen as higher than 1000 therefore the field remains at PTCL1000.

Any help would be great.

Below is the code i am using:

Public Function GetNextPTCLNumber() As Variant

Dim DB As Database
Dim rst As Recordset

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT Max(IIf([project].ptclnumber] Is Not Null,
Right([Project].[PTCLNumber],((Len([Project].[PTCLNumber])-4))),0)) AS MaxOfPTCLNumber
FROM Project;", dbOpenDynaset)

If rst.RecordCount > 0 Then
rst.MoveFirst
GetNextPTCLNumber = "PTCL" & rst!MaxOfPTCLNumber + 1
rst.Close
Else
End If

Set DB = Nothing

End Function

 

[nicsin]

Hi,

you could try to introduce a CInt() as:

Set rst = DB.OpenRecordset("SELECT Max(IIf([project].ptclnumber] Is Not Null,
Right(cint([Project].[PTCLNumber],((Len([Project].[PTCLNumber])-4)))),0)) AS MaxOfPTCLNumber
FROM Project;", dbOpenDynaset)

 

[MakeItSo]

Try by replacing
Right([Project].[PTCLNumber],((Len([Project].[PTCLNumber])-4))),0)) AS MaxOfPTCLNumber
with
[blue]Val([/blue]Right([Project].[PTCLNumber],((Len([Project].[PTCLNumber])-4))),0)) AS MaxOfPTCLNumber

Then 1000 is treated as anumber, not as a string and therefore 1000 should be greater than 999.

Regards,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[drewduncan]

Excellent guys!!

Yet again a simple thing - its better to ask sometimes than spend ages trying to work it out!.

Cheers again