[AccessD] Google Code Search

Hi John

Mid([tblMain]![txtControlNum],4) returns a string. In string comparison
terms 999 beats 1000, so the max is giving you 999 every time. Put Clng()
around the Mid and it'll do a numeric comparison, so 

nz(Max(CLng(Mid([tblMain]![txtControlNum],4))),0)+1 AS lngMaxID


-- Andy Lacey
http://www.minstersystems.co.uk 



> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Clark
> Sent: 06 October 2006 16:32
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Google Code Search
> 
> 
> OK...I need help here...BADLY! I've been like the typical 
> guy, who won't stop and ask for directions...I've been 
> working on this one single issue, since yesterday, and I 
> cannot find the problem.
> 
> I wrote this program, many moons ago, for our Social Services 
> Fraud Unit. They told me, at that time, that they would never 
> exceed, or even get to 1000 records in a year. There largest 
> amount was around 700. The reason this was an issue was their 
> control numbers, which were in the form of 06-001, where this 
> number would be the first case of the year 2006. It then 
> rolls over for the first case of the following year, to 
> 07-001. Despite their claim, I made the control number in the 
> form of, 06-0001, so that it could handle up to 9,999 
> records. When they saw this, they demanded that I change it. 
> They did not want the extra digit in their number. 
> 
> Well, guess what. Two years ago, they went over the 1000 
> record mark and called me to make the change. Actually that 
> year they thought it was a fluke or something, so we just 
> made do somehow...I don't even recall. Last year it was the 
> same issue, so I fixed it...or thought I did. It must have 
> worked because it went up to record 06-1000 this year, before 
> halting, whereas last year it stopped at 05-999.
> 
> Now we are at my current problem, and I just can't figure it 
> out. It looks like it should work and it isn't breaking at 
> 999, but rather 1000, and this confuses me. The code is as follows:
> 
> Function NewControlNum() As String
> Dim strSQL As String
> Dim strNewID As String
> Dim rst As Recordset
> strSQL = "SELECT 
> nz(Max(Mid([tblMain]![txtControlNum],4)),0)+1 AS lngMaxID," & _
>          "Right(Year(Now()),2)" & _
>          "& '-' & [lngMaxID] AS lngNewID " & _
>          "FROM tblMain " & _
>          "WHERE 
> (((Left([tblMain]![txtControlNum],2))=Right(Year(Now()),2)));"
> Set rst = CodeDb.OpenRecordset(strSQL)
> With rst
>    strNewID = rst!lngNewID
>    rst.Close
>    Set rst = Nothing
> End With
> NewControlNum = strNewID
> 
> Rem
> **************************************************************
> *************
> 
> Select Case Len(NewControlNum)
>     Case 4
>         NewControlNum = Left(NewControlNum, 3) & "00" & 
> Right(NewControlNum, 1)
>     Case 5
>         NewControlNum = Left(NewControlNum, 3) & "0" & 
> Right(NewControlNum, 2) End Select
> 
> 
> End Function
> 
> --------------------------------------------------------------
> ------------------------
> 
> BTW...this is an A97 program. 
> 
> It works perfectly fine, filling in the next control number, 
> when you hit upon a new record, until it gets to 06-1000, and 
> then it just wants to keep putting in that same number.
> 
> As a test, I went into the table and added a record 06-1101, 
> just to see what the result would be. It too returned 06-1000 
> as the next number. I deleted the last 5 records so that we 
> were at 06-996, and it let me re-enter them, putting in the 
> proper numbers, as I went (06-997 thru 06-1000).
> 
> I just know it is something simple that I am missing.
> 
> John W. Clark
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
>