Andy Lacey
andy at minstersystems.co.uk
Fri Jan 27 11:41:55 CST 2006
Yea but "ORDER BY [Row]" too.
And put a Debug.Print sSql in to check that it contains what you expect it
to. If it looks ok copy what you see, create a new Query in Access, go to
SQL view and paste it in. then run it there and see if the SQL itself is ok.
-- Andy Lacey
http://www.minstersystems.co.uk
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Gowey Mike W
> Sent: 27 January 2006 17:01
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Function Error
>
>
>
> You mean something like this?
>
> sSql = "SELECT [SID], [Row] FROM qryInmatesBinNumberUpdate
> WHERE [Row] = '" & j & "' ORDER BY Row"
>
> -----Original Message-----
> From: Andy Lacey [mailto:andy at minstersystems.co.uk]
> Sent: Friday, January 27, 2006 9:57 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Function Error
>
> Clutching at straws. Tried putting [] around SID, Row (twice)
> and the query name?
>
> --
> Andy Lacey
> http://www.minstersystems.co.uk
>
>
>
>
> --------- Original Message --------
> From: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Function Error
> Date: 27/01/06 16:47
>
>
> Nope it's not numeric. I went through and changed the Row
> field to numeric and adjusted the statement to take out the
> single quotes but I still get the same error.
>
> -----Original Message-----
> From: Heenan, Lambert [mailto:Lambert.Heenan at AIG.com]
> Sent: Friday, January 27, 2006 9:25 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Function Error
>
> The SQL is treating Row as if it was a string value.
>
> WHERE Row = '" & j & "' ORDER BY
>
> It's not actually numeric by any chance?
>
>
> Lambert
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Gowey Mike W
> Sent: Friday, January 27, 2006 10:32 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Function Error
>
>
> Just for the heck of it I went through and changed Row to
> Rows and it didn't solve the error. Dang
>
> -----Original Message-----
> From: Gowey Mike W
> Sent: Friday, January 27, 2006 8:21 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Function Error
>
> That's a good question, it works fine in 97, did they change
> it in 2000?
>
>
> -----Original Message-----
> From: Andy Lacey [mailto:andy at minstersystems.co.uk]
> Sent: Friday, January 27, 2006 8:18 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Function Error
>
> Is Row perchance a reserved word?
> --
> Andy Lacey
> http://www.minstersystems.co.uk
>
>
>
>
> --------- Original Message --------
> From: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Function Error
> Date: 27/01/06 14:58
>
>
>
> Sorry the highlight didn't show, the part it starts failing
> at is here:
>
> ' get all SIDs that are not assigned a bin number for the
> current row sSql = "SELECT SID FROM qryInmatesBinNumberUpdate
> WHERE Row = '" & j & "' ORDER BY Row" Set oRS =
> oDB.OpenRecordset(sSql)
>
> -----Original Message-----
> From: Gowey Mike W
> Sent: Friday, January 27, 2006 7:54 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Function Error
>
>
> I'm converting a database from 97 to 2000 and have run into a
> problem with the below function. Could someone please tell me
> why the highlighted part is failing? I get a " Runtime error
> '5' Invalid procedure call "
>
>
> Public Function UpdateSID4Bin() As Boolean
>
> Dim oDB As Database
> Dim oRS As Recordset
> Dim oRSBins As Recordset
> Dim sSql As String
> Dim j As Long
> Dim vRtn As Variant
>
> UpdateSID4Bin = False
>
> Set oDB = CurrentDb
>
> DoCmd.Hourglass True
>
> ' display message in status bar
> vRtn = SysCmd(acSysCmdInitMeter, "Updating Bin Number System
> With New SIDs...", 10)
>
> For j = 0 To 9
> ' update meter
> vRtn = SysCmd(acSysCmdUpdateMeter, j + 1) DoEvents
>
> ' get all SIDs that are not assigned a bin number for the
> current row sSql = "SELECT SID FROM qryInmatesBinNumberUpdate
> WHERE Row = '" & j & "' ORDER BY Row" Set oRS =
> oDB.OpenRecordset(sSql)
>
> Do While Not oRS.EOF
> ' grab an empty bin for the current row
> sSql = "SELECT TOP 1 BinNum FROM BinNumSIDAssign WHERE Row =
> '" & j & "' AND SID IS NULL" Set oRSBins = oDB.OpenRecordset(sSql)
>
> If Not oRSBins.EOF Then
> ' update empty bin with current SID
> sSql = "UPDATE BinNumSIDAssign SET SID = '" &
> oRS.Fields("SID") & "' WHERE Row = '" & j & "' AND BinNum =
> '" & oRSBins.Fields("BinNum") & "'" oDB.Execute sSql End If
>
> ' get next SID
> oRS.MoveNext
>
> Loop
> Next
>
> UpdateSID4Bin = True
>
> ExitFunction:
> On Error Resume Next
>
> oRS.Close
> oRSBins.Close
> oDB.Close
>
> Set oRS = Nothing
> Set oRSBins = Nothing
> Set oDB = Nothing
>
> DoCmd.Hourglass False
> vRtn = SysCmd(acSysCmdRemoveMeter)
>
> End Function
>
> Public Function CheckEvent(pEvent As String) As Boolean
>
> Dim oDB As DAO.Database
> Dim oRS As DAO.Recordset
> Dim sSql As String
>
> CheckEvent = False
>
> Set oDB = CurrentDb
>
> sSql = "SELECT Complete FROM DownloadEvents " & vbCrLf sSql =
> sSql & "WHERE EventName = '" & pEvent & "'" Set oRS =
> oDB.OpenRecordset(sSql)
>
> With oRS
> If Not .EOF Then
> CheckEvent = (.Fields(0) = True)
> End If
> End With
>
> ExitFunction:
> On Error Resume Next
>
> oRS.Close
> oDB.Close
>
> Set oRS = Nothing
> Set oDB = Nothing
>
> End Function
>
> Public Sub InitDownloadEvents()
>
> Dim oDB As DAO.Database
> Dim sSql As String
>
> ' init events
> Set oDB = CurrentDb
> sSql = "UPDATE DownloadEvents SET Complete = False"
> oDB.Execute sSql
>
> oDB.Close
> Set oDB = Nothing
>
> End Sub
>
> Public Function SetEvent(pEvent As String) As Boolean
>
> Dim oDB As DAO.Database
> Dim sSql As String
>
> SetEvent = False
>
> Set oDB = CurrentDb
>
> sSql = "UPDATE DownloadEvents SET Complete = TRUE " & vbCrLf
> sSql = sSql & "WHERE EventName = '" & pEvent & "'" oDB.Execute sSql
>
> SetEvent = True
>
> ExitFunction:
> On Error Resume Next
>
> oDB.Close
> Set oDB = Nothing
>
> End Function
>
>
> Thanks in advance for any help provided.
>
>
> Mike Gowey MCP, MCDST, A+, LME, NET+
> Team Leader - SouthEast Region
> Information Systems Unit
>
>
>
> ________________________________________________
> Message sent using UebiMiau 2.7.2
>
> --
> 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
>
>
> --
> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> ________________________________________________
> Message sent using UebiMiau 2.7.2
>
> --
> 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
>
>