Passing null values to a Function

[Guest]

Hi,

I have an Access 2003 database. I need to evaluate several condidtions for
several fields in a query. I created a VBA function that receives 5 fields’
values through the arglist statement. The function works fine except when
any of the fields are empty (null). If any of the values IS NULL, the
function isn’t executed for that record at all and I receive #errror. The
next record returns a valid value as long as none of the fields are empty.

What can I do about this? Any help is appreciated!
Thanks
LeAnn

 

[Alex Dybenko]

Hi,
you have to declare function parameters as Variant, then you can detect Null
inside the function

 

[Guest]

Thanks Alex that did the trick but now I have a different problem.

I want to be able to select (or exclude) records based on the result of that
calculated field in my query. When I try this, it takes forever for the
query to run and no records are returned. Without criteria, the query runs
very fast. I defined all but 1 of the parameters as variant, the other is a
string. The function itself returns a string.

Any ideas?
Thanks

 

[Guest]

Thanks Alex, that did the trick. I posted a reply that hasn't shown up so
forgive me if 2 end up posting.

The query runs fine. However, if I put run criteria for the calculated
field, it takes a very long time. I declared all but 1 of the parameters as
variant - the other is a string. The function returns a string.

What can I do to improve the performance?
Thanks

 

[Alex Dybenko]

Hi,
difficult to say, without looking at your function, but perhaps you can move
some calculations directly in a query

 

[Guest]

Well, it's hard to explain but due to the structure of the tables and
relationships, I end up getting duplicate records with mismatches. I have
tried several scenarios and the function seemed to work fine (without
criteria). The function is below (I need to be able evaluate how many
records match a unit id in the recordsset). I did re-design the table
structure in a different instance that has 2 queries each has date range
parameters, then I use a union query to base the report on. The issue here
is that users will have to enter 4 date/time parameters when really they
should only need to enter 1 beginning date/time and 1 ending date/time. Is
there a way to get just 2 prompts?

===============
Public Function UnitStatus(strUnit As String, strLTResult As Variant,
strNatResult As Variant, strLTTest As Variant, strNATTest As Variant) As
String
'Created by LeAnn Patterson; Modified on 02/02/2006
'Description
'This procedure will return a status for a particular sample.

'Set variables
Dim rstResult1 As String, rstResult2 'use to evaluate the result
fields in each record
Dim bolPool As Boolean
Dim strCount As String 'use to count the number of
records in the recordset
Dim strRecord As String 'use to determine which record
Dim rstTest1 As String, rstTest2 As String 'use to evaluate the LT test
name in each record
Dim rstNAT1 As String, rstNat2 As String 'use to evaluate the Tracker
test name in each record
Dim db As DAO.Database, rst As DAO.Recordset


bolPool = False
strUnit = NullString(strUnit)
strLTResult = NullString(strLTResult)
strNatResult = NullString(strNatResult)
strLTTest = NullString(strLTTest)
strNATTest = NullString(strNATTest)

'Open recordset using current record's unit id
Set db = CurrentDb()

Set rst = db.OpenRecordset("SELECT LT_Extract.UNIT_ID, LT_Extract.TEST,
LT_Extract.RESULT_STATUS_1, Tracker_Extract.unit_id,
Tracker_Extract.nat_test, Tracker_Extract.result " _
& "FROM Tracker_Extract RIGHT JOIN LT_Extract ON Tracker_Extract.unit_id =
LT_Extract.UNIT_ID " _
& "WHERE LT_Extract.Unit_ID = '" & strUnit & "' " _
& "ORDER BY LT_Extract.UNIT_ID, LT_Extract.TEST, Tracker_Extract.nat_test")


'If there are only 2 records for the sample, check to see if either has NT
(not tested)
With rst
.MoveLast
strCount = .RecordCount
If strCount = 2 Then
.MoveFirst
rstTest1 = NullString(![TEST])
rstNAT1 = NullString(![nat_test])
rstResult1 = NullString(![RESULT_STATUS_1])
.MoveNext
rstTest2 = NullString(![TEST])
rstNat2 = NullString(![nat_test])
rstResult2 = NullString(![RESULT_STATUS_1])
If rstResult1 <> "NT" And rstResult2 <> "NT" Then
bolPool = True
End If
End If
End With
rst.Close

'Assign a status to the sample.
If strLTResult = "NT" Then
UnitStatus = "Not Tested"
ElseIf strLTResult = "" Then
UnitStatus = "Test Pending"
ElseIf strLTTest = strNATTest Then
UnitStatus = "IDS Tested"
ElseIf strLTTest = "TestA" And strNATTest = "TestB" And bolPool = True Then
UnitStatus = "Investigate"
ElseIf strLTTest = "TestB" And strNATTest = "TestA" And bolPool = True Then
UnitStatus = "Investigate"
ElseIf strLTTest = "TestA" And strNATTest = "TestB" And bolPool = False Then
UnitStatus = "Mismatch"
ElseIf strLTTest = "TestB" And strNATTest = "TestA" And bolPool = False Then
UnitStatus = "Mismatch"
ElseIf strLTTest <> "" And strNATTest = "" Then
UnitStatus = "Investigate"
End If

Set rst = Nothing
Set db = Nothing

End Function

 

[Alex Dybenko]

Hi,
i think you have to put Tracker_Extract and LT_Extract tables to your query,
pass field values, to calculate bolPool,into function as arguments, and run
there only last part (no recordset opening)

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Well, it's hard to explain but due to the structure of the tables and
relationships, I end up getting duplicate records with mismatches. I have
tried several scenarios and the function seemed to work fine (without
criteria). The function is below (I need to be able evaluate how many
records match a unit id in the recordsset). I did re-design the table
structure in a different instance that has 2 queries each has date range
parameters, then I use a union query to base the report on. The issue
here
is that users will have to enter 4 date/time parameters when really they
should only need to enter 1 beginning date/time and 1 ending date/time.
Is
there a way to get just 2 prompts?

===============
Public Function UnitStatus(strUnit As String, strLTResult As Variant,
strNatResult As Variant, strLTTest As Variant, strNATTest As Variant) As
String
'Created by LeAnn Patterson; Modified on 02/02/2006
'Description
'This procedure will return a status for a particular sample.

'Set variables
Dim rstResult1 As String, rstResult2 'use to evaluate the
result
fields in each record
Dim bolPool As Boolean
Dim strCount As String 'use to count the number
of
records in the recordset
Dim strRecord As String 'use to determine which
record
Dim rstTest1 As String, rstTest2 As String 'use to evaluate the LT
test
name in each record
Dim rstNAT1 As String, rstNat2 As String 'use to evaluate the
Tracker
test name in each record
Dim db As DAO.Database, rst As DAO.Recordset


bolPool = False
strUnit = NullString(strUnit)
strLTResult = NullString(strLTResult)
strNatResult = NullString(strNatResult)
strLTTest = NullString(strLTTest)
strNATTest = NullString(strNATTest)

'Open recordset using current record's unit id
Set db = CurrentDb()

Set rst = db.OpenRecordset("SELECT LT_Extract.UNIT_ID, LT_Extract.TEST,
LT_Extract.RESULT_STATUS_1, Tracker_Extract.unit_id,
Tracker_Extract.nat_test, Tracker_Extract.result " _
& "FROM Tracker_Extract RIGHT JOIN LT_Extract ON Tracker_Extract.unit_id =
LT_Extract.UNIT_ID " _
& "WHERE LT_Extract.Unit_ID = '" & strUnit & "' " _
& "ORDER BY LT_Extract.UNIT_ID, LT_Extract.TEST,
Tracker_Extract.nat_test")


'If there are only 2 records for the sample, check to see if either has NT
(not tested)
With rst
.MoveLast
strCount = .RecordCount
If strCount = 2 Then
.MoveFirst
rstTest1 = NullString(![TEST])
rstNAT1 = NullString(![nat_test])
rstResult1 = NullString(![RESULT_STATUS_1])
.MoveNext
rstTest2 = NullString(![TEST])
rstNat2 = NullString(![nat_test])
rstResult2 = NullString(![RESULT_STATUS_1])
If rstResult1 <> "NT" And rstResult2 <> "NT" Then
bolPool = True
End If
End If
End With
rst.Close

'Assign a status to the sample.
If strLTResult = "NT" Then
UnitStatus = "Not Tested"
ElseIf strLTResult = "" Then
UnitStatus = "Test Pending"
ElseIf strLTTest = strNATTest Then
UnitStatus = "IDS Tested"
ElseIf strLTTest = "TestA" And strNATTest = "TestB" And bolPool = True
Then
UnitStatus = "Investigate"
ElseIf strLTTest = "TestB" And strNATTest = "TestA" And bolPool = True
Then
UnitStatus = "Investigate"
ElseIf strLTTest = "TestA" And strNATTest = "TestB" And bolPool = False
Then
UnitStatus = "Mismatch"
ElseIf strLTTest = "TestB" And strNATTest = "TestA" And bolPool = False
Then
UnitStatus = "Mismatch"
ElseIf strLTTest <> "" And strNATTest = "" Then
UnitStatus = "Investigate"
End If

Set rst = Nothing
Set db = Nothing

End Function

Hi,
difficult to say, without looking at your function, but perhaps you can
move
some calculations directly in a query