[AccessD] Re: Trouble with SQL statemnent on two different machines

I now know exactly where this is failing.. on the
Max(Right[AuthorityNumber],5) As Number

Is tehre an Issue with these functions when using an XP machine? I have
copied the libraries and made sure all teh references match but it still
fails when processing the SQL Statement.

Connie Kamrowski

Analyst/Programmer
Information Technology
NSW Department of Primary Industries
Orange



Hi All,

I am havinbg an issue with a function and could use some fresh eyes and
knowledge. Both machines are running Access97 SR2. On my machine the
function works fine, on the users machine it falls over. We are accessing
the database from a central location.

Function as follows:

<code>
Private Function getstrNextAuthorityNumber() As String
'Automatically select the next availaible Authority Number for this type of
Authority
On Error GoTo Failure

Dim dbNextAuthority As Database
Dim rsNextAuthority As Recordset

Dim intNextAuthorityNumber As Integer

Dim strSelect As String


strSelect = "SELECT tblCAuthorityType.AuthorityPrefix,
Max(Right([AuthorityNumber],5)) AS [Number] " & _
                        "FROM tblCAuthorityType RIGHT JOIN tblDAuthority ON
tblCAuthorityType.AuthorityTypeID = tblDAuthority.AuthorityType " & _
                        "GROUP BY tblCAuthorityType.AuthorityPrefix " & _
                        "HAVING
(((tblCAuthorityType.AuthorityPrefix)=getgstrAuthorityPrefix()));"

On Error Resume Next

    Set dbNextAuthority = CurrentDb()
    Set rsNextAuthority = dbNextAuthority.OpenRecordset(strSelect,
dbOpenSnapshot, dbFailOnError)

        If rsNextAuthority.RecordCount < 1 Then 'No records - this will not
actually be used.
            intNextAuthorityNumber = 1 'Initial Authority Number for each
type
        Else
            intNextAuthorityNumber = rsNextAuthority!Number + 1 'Increment
last Authority Number by 1
        End If 'rsNextAuthorityNumber.RecordCount = 0

        getstrNextAuthorityNumber = getgstrAuthorityPrefix() & Right("0000"
& Trim(Str(intNextAuthorityNumber)), 5)

ExitRoutine:
        On Error Resume Next
            rsNextAuthority.Close
            Set rsNextAuthority = Nothing
            dbNextAuthority.Close
            Set dbNextAuthority = Nothing
        Exit Function

Failure:
        Call ErrorHandler(lngErrorNumber:=Err.Number,
strErrorDescription:=Err.Description, strErrorSource:=Err.Source)
        Resume ExitRoutine

End Function
<code>

This message is intended for the addressee named and may contain
confidential information. If you are not the intended recipient or received
it in error, please delete the message and notify sender. Views expressed
are those of the individual sender and are not necessarily the views of
their organisation.