[AccessD] Google Code Search

Oops, didn't read ahead and see that the subject line had changed and others
had resolved the problem. Sorry folks.

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

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy Lacey
> Sent: 08 October 2006 18:10
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [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
> > 
> > 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
>