Access behaves differently in different environments

[Laurel]

The following function has an error in it. There is no column called
"Comment" in the table "tblScores." But in my XP 2003 Pro/Access 2003
environment, control is never transferred to the error handler. Instead,
the function just returns an empty string. In a Windows 2000 environment,
control does go to the error handler, but the error is "<database name>
can't find the field Forms referred to in your expression." Not very
helpful. But the 2000 environment at least is recognizing that an error
occurred.

The database is created in the 2003 environment, using 2000 as the default
file format. The version is 2003 (11.6566.8132) SP 2. Is this a bug that
has been fixed? If so, how does one get fixes? I find the MS site
terminally confusing.

Public Function fncGetOverrideComment(ai_StudentID As Integer, _
adt_date As Date) As String

Dim ls_temp As String, ls_where As String

On Error GoTo Err_GetOverrideComment

ls_where = "Student_id = " & ai_StudentID & " AND Score_Date = #" _
& adt_date & "# And Period_Code = '" & gs_OverridePeriod & "'"

ls_temp = Nz(DLookup("Comment", "tblScores", ls_where))
Dim LV_DEBUG As Variant
LV_DEBUG = DLookup("COMMENT", "TBLSCORES", ls_where)
ls_temp = Nz(LV_DEBUG)

fncGetOverrideComment = ls_temp

Exit_GetOverrideComment:
Exit Function

Err_GetOverrideComment:

MsgBox (Err.Description)

fncGetOverrideComment = Failure
Resume Exit_GetOverrideComment

End Function

 

[Guest]

I don't know of a bug, but there are a couple of problems with the code. See
in line comments below.

--
Dave Hargis, Microsoft Access MVP

The following function has an error in it. There is no column called
"Comment" in the table "tblScores." But in my XP 2003 Pro/Access 2003
environment, control is never transferred to the error handler. Instead,
the function just returns an empty string. In a Windows 2000 environment,
control does go to the error handler, but the error is "<database name>
can't find the field Forms referred to in your expression." Not very
helpful. But the 2000 environment at least is recognizing that an error
occurred.

The database is created in the 2003 environment, using 2000 as the default
file format. The version is 2003 (11.6566.8132) SP 2. Is this a bug that
has been fixed? If so, how does one get fixes? I find the MS site
terminally confusing.

Public Function fncGetOverrideComment(ai_StudentID As Integer, _
adt_date As Date) As String

Dim ls_temp As String, ls_where As String

On Error GoTo Err_GetOverrideComment

ls_where = "Student_id = " & ai_StudentID & " AND Score_Date = #" _
& adt_date & "# And Period_Code = '" & gs_OverridePeriod & "'"

ls_temp = Nz(DLookup("Comment", "tblScores", ls_where))

In the line above, you are not converting a Null to anything. The Nz
function is to avoid nulls. It should be:
ls_temp = Nz(DLookup("Comment", "tblScores", ls_where), vbNullString)
Assigning a Null value to a string will cause an error

Dim LV_DEBUG As Variant

Although not an error, Dims should be at the top of the procedure where they
are easy to find.

LV_DEBUG = DLookup("COMMENT", "TBLSCORES", ls_where)

Try enclosing your field and table names in brackets.
LV_DEBUG = DLookup("[COMMENT]", "TBLSCORES", ls_where)

 

[Guest]

That is not your problem.

Your problem is that the value in gs_OverridePeriod is not correct.

By the way,
Nz(Null)

returns an empty variant (a variant with vartype 0). Empty variants
have the value of 0 or "" depending on how they are used:

4 + Nz(Null) = 4
"4" + Nz(Null) = "4"

(david)

 

[Laurel]

See below

In the line above, you are not converting a Null to anything. The Nz
function is to avoid nulls. It should be:
ls_temp = Nz(DLookup("Comment", "tblScores", ls_where), vbNullString)

Note If you use the Nz function in an expression in a query without using
the valueifnull argument, the results will be a zero-length string in the
fields that contain null values.

This is from the HELP. Wouldn't I getting a zero-length string if the
lookup returned a null?

Assigning a Null value to a string will cause an error

Dim LV_DEBUG As Variant

Although not an error, Dims should be at the top of the procedure where
they
are easy to find.

LV_DEBUG = DLookup("COMMENT", "TBLSCORES", ls_where)

Try enclosing your field and table names in brackets.
LV_DEBUG = DLookup("[COMMENT]", "TBLSCORES", ls_where)

ls_temp = Nz(LV_DEBUG) Same issue with the Nz here.

fncGetOverrideComment = ls_temp

Exit_GetOverrideComment:
Exit Function

Err_GetOverrideComment:

MsgBox (Err.Description)

fncGetOverrideComment = Failure
Resume Exit_GetOverrideComment

End Function