Hi Gurus
I have a query that contains reference to the following function:
Public Function UserStatus() As Byte
Const ProEro = 1: 'on error GoTo ERR
'---
UserStatus = Nz(DLookup("[L-SSL-ID]", "[M-WOR]", "[M-WOR]![ID]=" &
WHOM()), 0)
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro)
UserStatus = 0
Resume xit
End Function
THe WHOM() function is a function that reads the a value in the main form
(the person who is logged in).
Is there anyway (I am sure there is), that the userstatus function can be
faster?
The userstatus is determined by a button that the user presses in the main
menu and that is then stored in the M-WOR table.
TIA
- Nicolaas
8  1718 
On Thu, 2 Sep 2004 16:20:49 +1200, "WindAndWaves" <ac****@ngaru.com>
wrote:
Replace DLookup by an equivalent select query.
-Tom. Hi Gurus
I have a query that contains reference to the following function:
Public Function UserStatus() As Byte
Const ProEro = 1: 'on error GoTo ERR
'---
UserStatus = Nz(DLookup("[L-SSL-ID]", "[M-WOR]", "[M-WOR]![ID]=" &
WHOM()), 0)
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro)
UserStatus = 0
Resume xit
End Function
THe WHOM() function is a function that reads the a value in the main form
(the person who is logged in).
Is there anyway (I am sure there is), that the userstatus function can be
faster?
The userstatus is determined by a button that the user presses in the main
menu and that is then stored in the M-WOR table.
TIA
- Nicolaas
Here are some useful functions from Allen Browne.
I always use ELookup(........) etc Much faster and does not load the
database so much
HTH
Phil
Function EMin(expr As String, domain As String, Optional Criteria)
On Error GoTo Err_EMin
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String
'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMin = Null
Else
EMin = rs(0)
End If
rs.Close
Exit_EMin:
Set rs = Nothing
Set MyDb = Nothing
Exit Function
Err_EMin:
' MsgBox Err.Description, vbExclamation, "EMin Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMin = CVErr(5) 'Out of range.
Else
EMin = CVErr(Err.Number)
End If
Resume Exit_EMin
End Function
Function EMax(expr As String, domain As String, Optional Criteria)
On Error GoTo Err_EMax
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String
'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr & " DESC"
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMax = Null
Else
EMax = rs(0)
End If
rs.Close
Exit_EMax:
Set rs = Nothing
Set MyDb = Nothing
Exit Function
Err_EMax:
' MsgBox Err.Description, vbExclamation, "EMax Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMax = CVErr(5) 'Out of range.
Else
EMax = CVErr(Err.Number)
End If
Resume Exit_EMax
End Function
Public Function ELookup(expr As String, domain As String, Optional Criteria,
Optional OrderClause)
On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
'e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String
'Build the SQL string.
SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close
Exit_ELookup:
Set rs = Nothing
Set MyDb = Nothing
Exit Function
Err_ELookup:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
ELookup = CVErr(5) 'Out of range.
Else
ELookup = CVErr(Err.Number)
End If
Resume Exit_ELookup
End Function
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:Md********************@news.xtra.co.nz... Hi Gurus
I have a query that contains reference to the following function:
Public Function UserStatus() As Byte
Const ProEro = 1: 'on error GoTo ERR
'---
UserStatus = Nz(DLookup("[L-SSL-ID]", "[M-WOR]", "[M-WOR]![ID]=" &
WHOM()), 0)
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro)
UserStatus = 0
Resume xit
End Function
THe WHOM() function is a function that reads the a value in the main form
(the person who is logged in).
Is there anyway (I am sure there is), that the userstatus function can be
faster?
The userstatus is determined by a button that the user presses in the main
menu and that is then stored in the M-WOR table.
TIA
- Nicolaas
Brilliant
Thanks a million!
"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message
news:41***********************@mercury.nildram.net ... Here are some useful functions from Allen Browne.
I always use ELookup(........) etc Much faster and does not load the
database so much
HTH
Phil
Function EMin(expr As String, domain As String, Optional Criteria)
On Error GoTo Err_EMin
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String
'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMin = Null
Else
EMin = rs(0)
End If
rs.Close
Exit_EMin:
Set rs = Nothing
Set MyDb = Nothing
Exit Function
Err_EMin:
' MsgBox Err.Description, vbExclamation, "EMin Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMin = CVErr(5) 'Out of range.
Else
EMin = CVErr(Err.Number)
End If
Resume Exit_EMin
End Function
Function EMax(expr As String, domain As String, Optional Criteria)
On Error GoTo Err_EMax
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String
'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr & " DESC"
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMax = Null
Else
EMax = rs(0)
End If
rs.Close
Exit_EMax:
Set rs = Nothing
Set MyDb = Nothing
Exit Function
Err_EMax:
' MsgBox Err.Description, vbExclamation, "EMax Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMax = CVErr(5) 'Out of range.
Else
EMax = CVErr(Err.Number)
End If
Resume Exit_EMax
End Function
Public Function ELookup(expr As String, domain As String, Optional
Criteria, Optional OrderClause)
On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
'e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String
'Build the SQL string.
SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close
Exit_ELookup:
Set rs = Nothing
Set MyDb = Nothing
Exit Function
Err_ELookup:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
ELookup = CVErr(5) 'Out of range.
Else
ELookup = CVErr(Err.Number)
End If
Resume Exit_ELookup
End Function
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:Md********************@news.xtra.co.nz... Hi Gurus
I have a query that contains reference to the following function:
Public Function UserStatus() As Byte
Const ProEro = 1: 'on error GoTo ERR
'---
UserStatus = Nz(DLookup("[L-SSL-ID]", "[M-WOR]", "[M-WOR]![ID]=" &
WHOM()), 0)
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro)
UserStatus = 0
Resume xit
End Function
THe WHOM() function is a function that reads the a value in the main
form (the person who is logged in).
Is there anyway (I am sure there is), that the userstatus function can
be faster?
The userstatus is determined by a button that the user presses in the
main menu and that is then stored in the M-WOR table.
TIA
- Nicolaas
Hoi Tom
Basically, Phil in the other answer is recommending the same thing, using
the Elookups. That seems brilliant to me.
Thank you for your reply.
Nicolaas
"Tom van Stiphout" <no*************@cox.net> wrote in message
news:dn********************************@4ax.com... On Thu, 2 Sep 2004 16:20:49 +1200, "WindAndWaves" <ac****@ngaru.com>
wrote:
Replace DLookup by an equivalent select query.
-Tom.
Hi Gurus
I have a query that contains reference to the following function:
Public Function UserStatus() As Byte
Const ProEro = 1: 'on error GoTo ERR
'---
UserStatus = Nz(DLookup("[L-SSL-ID]", "[M-WOR]", "[M-WOR]![ID]=" &
WHOM()), 0)
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro)
UserStatus = 0
Resume xit
End Function
THe WHOM() function is a function that reads the a value in the main form
(the person who is logged in).
Is there anyway (I am sure there is), that the userstatus function can be
faster?
The userstatus is determined by a button that the user presses in the
mainmenu and that is then stored in the M-WOR table.
TIA
- Nicolaas
is there also one for ecount or should I work that out myself?!
"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message
news:41***********************@mercury.nildram.net ... Here are some useful functions from Allen Browne.
I always use ELookup(........) etc Much faster and does not load the
database so much
HTH
Phil
Function EMin(expr As String, domain As String, Optional Criteria)
On Error GoTo Err_EMin
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String
'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMin = Null
Else
EMin = rs(0)
End If
rs.Close
Exit_EMin:
Set rs = Nothing
Set MyDb = Nothing
Exit Function
Err_EMin:
' MsgBox Err.Description, vbExclamation, "EMin Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMin = CVErr(5) 'Out of range.
Else
EMin = CVErr(Err.Number)
End If
Resume Exit_EMin
End Function
Function EMax(expr As String, domain As String, Optional Criteria)
On Error GoTo Err_EMax
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String
'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr & " DESC"
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMax = Null
Else
EMax = rs(0)
End If
rs.Close
Exit_EMax:
Set rs = Nothing
Set MyDb = Nothing
Exit Function
Err_EMax:
' MsgBox Err.Description, vbExclamation, "EMax Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMax = CVErr(5) 'Out of range.
Else
EMax = CVErr(Err.Number)
End If
Resume Exit_EMax
End Function
Public Function ELookup(expr As String, domain As String, Optional
Criteria, Optional OrderClause)
On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
'e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String
'Build the SQL string.
SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close
Exit_ELookup:
Set rs = Nothing
Set MyDb = Nothing
Exit Function
Err_ELookup:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
ELookup = CVErr(5) 'Out of range.
Else
ELookup = CVErr(Err.Number)
End If
Resume Exit_ELookup
End Function
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:Md********************@news.xtra.co.nz... Hi Gurus
I have a query that contains reference to the following function:
Public Function UserStatus() As Byte
Const ProEro = 1: 'on error GoTo ERR
'---
UserStatus = Nz(DLookup("[L-SSL-ID]", "[M-WOR]", "[M-WOR]![ID]=" &
WHOM()), 0)
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro)
UserStatus = 0
Resume xit
End Function
THe WHOM() function is a function that reads the a value in the main
form (the person who is logged in).
Is there anyway (I am sure there is), that the userstatus function can
be faster?
The userstatus is determined by a button that the user presses in the
main menu and that is then stored in the M-WOR table.
TIA
- Nicolaas
Anyway, I came up with :
Public Function Ecount(expr As String, domain As String, Optional Criteria)
'replacement for dCount
Const ProEro = 3: 'On Error GoTo err
Dim Dbs As Database
Dim RST As Recordset
Dim SqlS As String
'--- Build the SQL string.
SqlS = "SELECT count(" & expr & ") as C FROM " & domain
If Not IsMissing(Criteria) And Not Criteria = "" Then
SqlS = SqlS & " WHERE " & Criteria
End If
SqlS = SqlS & ";"
'---'Lookup the value.
Set Dbs = DBEngine(0)(0)
Set RST = Dbs.OpenRecordset(SqlS, dbOpenForwardOnly)
Ecount = RST.Fields("C")
RST.close
xit:
Set RST = Nothing
Set Dbs = Nothing
Exit Function
ERR:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
Ecount = 0
Resume xit
End Function
"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message
news:41***********************@mercury.nildram.net ... Here are some useful functions from Allen Browne.
I always use ELookup(........) etc Much faster and does not load the
database so much
HTH
Phil
Function EMin(expr As String, domain As String, Optional Criteria)
On Error GoTo Err_EMin
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String
'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMin = Null
Else
EMin = rs(0)
End If
rs.Close
Exit_EMin:
Set rs = Nothing
Set MyDb = Nothing
Exit Function
Err_EMin:
' MsgBox Err.Description, vbExclamation, "EMin Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMin = CVErr(5) 'Out of range.
Else
EMin = CVErr(Err.Number)
End If
Resume Exit_EMin
End Function
Function EMax(expr As String, domain As String, Optional Criteria)
On Error GoTo Err_EMax
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au, Phil S
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String
'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr & " DESC"
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMax = Null
Else
EMax = rs(0)
End If
rs.Close
Exit_EMax:
Set rs = Nothing
Set MyDb = Nothing
Exit Function
Err_EMax:
' MsgBox Err.Description, vbExclamation, "EMax Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMax = CVErr(5) 'Out of range.
Else
EMax = CVErr(Err.Number)
End If
Resume Exit_EMax
End Function
Public Function ELookup(expr As String, domain As String, Optional
Criteria, Optional OrderClause)
On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
'e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String
'Build the SQL string.
SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close
Exit_ELookup:
Set rs = Nothing
Set MyDb = Nothing
Exit Function
Err_ELookup:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
ELookup = CVErr(5) 'Out of range.
Else
ELookup = CVErr(Err.Number)
End If
Resume Exit_ELookup
End Function
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:Md********************@news.xtra.co.nz... Hi Gurus
I have a query that contains reference to the following function:
Public Function UserStatus() As Byte
Const ProEro = 1: 'on error GoTo ERR
'---
UserStatus = Nz(DLookup("[L-SSL-ID]", "[M-WOR]", "[M-WOR]![ID]=" &
WHOM()), 0)
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro)
UserStatus = 0
Resume xit
End Function
THe WHOM() function is a function that reads the a value in the main
form (the person who is logged in).
Is there anyway (I am sure there is), that the userstatus function can
be faster?
The userstatus is determined by a button that the user presses in the
main menu and that is then stored in the M-WOR table.
TIA
- Nicolaas
It looks like you have things well in hand, but if not, try Trevor
Best's replacement functions. Located at http://easyweb.easynet.co.uk/~trevor/AccFAQ/. Look under downloads,
then domain aggregate function replacements.
Tom This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Darren Smith |
last post by:
I am having a great deal of difficulty accessing individual fields
generated from a Sql Server 7 view.
When I specify the actual field name, I get the error:
Microsoft OLE DB Provider for ODBC...
|
by: Jennifer Smith |
last post by:
I want to be able to display my recordset as follows:
a e
b f
c g
d h
Instead of :
a b
c d
|
by: Dominic Tocci |
last post by:
When I submit a form to an asp page, the request.form is not getting the
data. This only happens on my local copy of IIS, and not on my web host.
It's a simple request.form, so I know it's not a...
|
by: Chris Styles |
last post by:
Dear All,
I've been using some code to verify form data quite happily, but i've
recently changed the way my form is structured, and I can't get it to work
now.
Originally :
The form is...
|
by: prodirect |
last post by:
Hi all,
I hope someone can help me. I've recently created a database and wanted to
put it up on an ftp sight so that multiple people could access the same
tables at the same time from different...
|
by: Eugen Walcher |
last post by:
Hello all, I've tried posting this same question on other newsgroups
with no luck. This group seems to have a lot more activity so I
apologize if you have seen it before.
I'm trying to...
|
by: CrazyAtlantaGuy |
last post by:
I'm having a strange problem and I was hoping someone could give me
some guidance. I am accessing a Microsoft SQL 2000 server through ASP
scripts on our webserver. The sql server and web server...
|
by: Jimmy Reds |
last post by:
Hi,
I have a blood glucose meter (a Lifescan OneTouch Ultra in case anyone
was wondering) which I connect to my PC using a USB cable and I would
like to have a go at accessing the data on this...
|
by: djhulme |
last post by:
Hi,
I'm using GCC. Please could you tell me, what is the maximum number of
array elements that I can create in C, i.e.
char* anArray = (char*) calloc( ??MAX?? , sizeof(char) ) ;
I've...
|
by: hugh welford |
last post by:
Hi
Have just installed IIS7 on Vista and am trying to access a .mdb file
through ASP. Getting server error.
I think the problem is in the file permission. Under XP Pro/IIS6 is used to
have to...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
