DLookup

[Danielle Sturgess]

I am attempting to perform a data validation (for part inspections) by
checking measurements against a validation table. I'm trying to use the
DLookup function to get 2 values but the code isn't working. I get an
"Invalid use of Null" error:

Dim min As Variant
Dim max As Variant

min = dlookup("[MinValue]", "tblValidation", "[Measurement] =" &
"Perpendicularity")
max = dlookup("[MaxValue]", "tblValidation", "[Measurement] =" &
"Perpendicularity")

If Me![Perpendicularity] < min Then
docmd.openform "frmWarning"
else
If Me![Perpendicularity] > max Then
docmd.openform "frmWarning"
end if
end if

Any ideas? In the tables, all fields in the code are Number fields and are
Double, not the default Long Integer.

Thanks!

 

[Wayne Morgan]

You are passing the string value "Perpendicularity" to the field
[Measurement] in the tblValidation table which you say is a numerical value.
Try modifying the statement as follows:

min = dlookup("[MinValue]", "tblValidation", "[Measurement] =" &
Me.Perpendicularity)

 

[Danielle Sturgess]

You are passing the string value "Perpendicularity" to the field
[Measurement] in the tblValidation table which you say is a numerical
value. Try modifying the statement as follows:

min = dlookup("[MinValue]", "tblValidation", "[Measurement] =" &
Me.Perpendicularity)

--
Wayne Morgan
MS Access MVP

I am attempting to perform a data validation (for part inspections) by
checking measurements against a validation table. I'm trying to use the
DLookup function to get 2 values but the code isn't working. I get an
"Invalid use of Null" error:

Dim min As Variant
Dim max As Variant

min = dlookup("[MinValue]", "tblValidation", "[Measurement] =" &
"Perpendicularity")
max = dlookup("[MaxValue]", "tblValidation", "[Measurement] =" &
"Perpendicularity")

If Me![Perpendicularity] < min Then
docmd.openform "frmWarning"
else
If Me![Perpendicularity] > max Then
docmd.openform "frmWarning"
end if
end if

Any ideas? In the tables, all fields in the code are Number fields and
are Double, not the default Long Integer.

Thanks!

 

[Danielle Sturgess]

I'm sorry, I mis-spoke.
Measurement is a text field in tblValidation. MinValue, MaxValue and the
Perpendicularity field in the If statement are all numerical.

You are passing the string value "Perpendicularity" to the field
[Measurement] in the tblValidation table which you say is a numerical
value. Try modifying the statement as follows:

min = dlookup("[MinValue]", "tblValidation", "[Measurement] =" &
Me.Perpendicularity)

--
Wayne Morgan
MS Access MVP

I am attempting to perform a data validation (for part inspections) by
checking measurements against a validation table. I'm trying to use the
DLookup function to get 2 values but the code isn't working. I get an
"Invalid use of Null" error:

Dim min As Variant
Dim max As Variant

min = dlookup("[MinValue]", "tblValidation", "[Measurement] =" &
"Perpendicularity")
max = dlookup("[MaxValue]", "tblValidation", "[Measurement] =" &
"Perpendicularity")

If Me![Perpendicularity] < min Then
docmd.openform "frmWarning"
else
If Me![Perpendicularity] > max Then
docmd.openform "frmWarning"
end if
end if

Any ideas? In the tables, all fields in the code are Number fields and
are Double, not the default Long Integer.

Thanks!

 

[Wayne Morgan]

In that case, does Measurement contain a number stored as text? In other
words, since you're testing to see if it matches the value in
Perpendicularity, does it or does Measurement contain some other text value?
As written, you are passing the word "Perpendicularity" to Measurement. Is
this word found in any record of this field? Also, since you've "hard coded"
this word in, you will always get the same value back from the DLookup.

If you do want the word passed, there is still a syntax error. The line
would need to be adjusted as follows:

min = dlookup("[MinValue]", "tblValidation", "[Measurement]
='Perpendicularity'")

Note the inclusion of single quotes around the word Perpendicularity.