DLOOKUP Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Howdy from Oklahoma!

I am just learning ACCESS and the people here have helped me alot!!!

But I now have a Dlookup problem where my result may not be in the table
that I am querying. But when the result is not in the table the [field] that
I am looking up does not show to be NULL.
I then have a IF/THEN statement that I need to do an action if the lookup
[field] has a value and if the the result is not in the table then I need it
to do a different action. I cannot get the IF statement to identify if the
result is not in the table using the "[field]= NULL". Below is the skeleton
of the statement I am trying to run:

Dim Test As Variant

Test = DLookup("[quantity]", "tbl_inventory", "[product#] =
Forms![frm_pohdrecv]![frm_POLNsub].[Form]![Prodno]")

If Test <> Null Then
MsgBox ("Add New Inventory")
Else
MsgBox ("Add Quantity")
End If

End

Any assistance to this would be greatly appreciated on getting past this
delima!!!

And as always THANKS IN ADVANCE!!!!

Chip
 
Nothing ever equals Null, not even Null. You must use the IsNull function to
test for the value of Null:

If IsNull(Test) = True Then
' the Test value is Null
Else
' the Test value is not Null
End If
 
(e-mail address removed)...
Howdy from Oklahoma!

I am just learning ACCESS and the people here have helped me alot!!!

But I now have a Dlookup problem where my result may not be in the table
that I am querying. But when the result is not in the table the [field]
that
I am looking up does not show to be NULL.
I then have a IF/THEN statement that I need to do an action if the lookup
[field] has a value and if the the result is not in the table then I need
it
to do a different action. I cannot get the IF statement to identify if
the
result is not in the table using the "[field]= NULL". Below is the
skeleton
of the statement I am trying to run:

Dim Test As Variant

Test = DLookup("[quantity]", "tbl_inventory", "[product#] =
Forms![frm_pohdrecv]![frm_POLNsub].[Form]![Prodno]")
Click to expand...

You have to have the field tested for OUTSIDE the quotes

If Prodno is numeric do this:

Test = DLookup("[quantity]", "tbl_inventory", "[product#] = " &
Forms![frm_pohdrecv]![frm_POLNsub].[Form]![Prodno])

If it's a character field, do this:

Test = DLookup("[quantity]", "tbl_inventory", "[product#] = '" &
Forms![frm_pohdrecv]![frm_POLNsub].[Form]![Prodno] & "'")


Tom Lake
 
Thanks!!!
That got it working perfectly, now I am trying to figure out how to make one
of the actions append a new record to another table.

THANKS AGAIN!!!
Chip

Ken Snell (MVP) said:
Nothing ever equals Null, not even Null. You must use the IsNull function to
test for the value of Null:

If IsNull(Test) = True Then
' the Test value is Null
Else
' the Test value is not Null
End If

--

Ken Snell
<MS ACCESS MVP>

Chip said:
Howdy from Oklahoma!

I am just learning ACCESS and the people here have helped me alot!!!

But I now have a Dlookup problem where my result may not be in the table
that I am querying. But when the result is not in the table the [field]
that
I am looking up does not show to be NULL.
I then have a IF/THEN statement that I need to do an action if the lookup
[field] has a value and if the the result is not in the table then I need
it
to do a different action. I cannot get the IF statement to identify if
the
result is not in the table using the "[field]= NULL". Below is the
skeleton
of the statement I am trying to run:

Dim Test As Variant

Test = DLookup("[quantity]", "tbl_inventory", "[product#] =
Forms![frm_pohdrecv]![frm_POLNsub].[Form]![Prodno]")

If Test <> Null Then
MsgBox ("Add New Inventory")
Else
MsgBox ("Add Quantity")
End If

End

Any assistance to this would be greatly appreciated on getting past this
delima!!!

And as always THANKS IN ADVANCE!!!!

Chip
Click to expand...
Click to expand...
 
Yup, I used your example for a variant and it worked!!! I am having a
difficult time understanding where and when to use all the quote marks. But
I greatly appreciate you response as it helped me get this problem corrected.

THANKS AGAIN!!!!
Chip

Tom Lake said:
(e-mail address removed)...
Howdy from Oklahoma!

I am just learning ACCESS and the people here have helped me alot!!!

But I now have a Dlookup problem where my result may not be in the table
that I am querying. But when the result is not in the table the [field]
that
I am looking up does not show to be NULL.
I then have a IF/THEN statement that I need to do an action if the lookup
[field] has a value and if the the result is not in the table then I need
it
to do a different action. I cannot get the IF statement to identify if
the
result is not in the table using the "[field]= NULL". Below is the
skeleton
of the statement I am trying to run:

Dim Test As Variant

Test = DLookup("[quantity]", "tbl_inventory", "[product#] =
Forms![frm_pohdrecv]![frm_POLNsub].[Form]![Prodno]")
Click to expand...

You have to have the field tested for OUTSIDE the quotes

If Prodno is numeric do this:

Test = DLookup("[quantity]", "tbl_inventory", "[product#] = " &
Forms![frm_pohdrecv]![frm_POLNsub].[Form]![Prodno])

If it's a character field, do this:

Test = DLookup("[quantity]", "tbl_inventory", "[product#] = '" &
Forms![frm_pohdrecv]![frm_POLNsub].[Form]![Prodno] & "'")


Tom Lake
Click to expand...
 
Actually, Jet will allow the syntax that Chip uses (with the
Forms![frm_pohdrecv]![frm_POLNsub].[Form]![Prodno] reference inside the
quotes) when using a value from a form.
--

Ken Snell
<MS ACCESS MVP>



Tom Lake said:
(e-mail address removed)...
Howdy from Oklahoma!

I am just learning ACCESS and the people here have helped me alot!!!

But I now have a Dlookup problem where my result may not be in the table
that I am querying. But when the result is not in the table the [field]
that
I am looking up does not show to be NULL.
I then have a IF/THEN statement that I need to do an action if the lookup
[field] has a value and if the the result is not in the table then I need
it
to do a different action. I cannot get the IF statement to identify if
the
result is not in the table using the "[field]= NULL". Below is the
skeleton
of the statement I am trying to run:

Dim Test As Variant

Test = DLookup("[quantity]", "tbl_inventory", "[product#] =
Forms![frm_pohdrecv]![frm_POLNsub].[Form]![Prodno]")
Click to expand...

You have to have the field tested for OUTSIDE the quotes

If Prodno is numeric do this:

Test = DLookup("[quantity]", "tbl_inventory", "[product#] = " &
Forms![frm_pohdrecv]![frm_POLNsub].[Form]![Prodno])

If it's a character field, do this:

Test = DLookup("[quantity]", "tbl_inventory", "[product#] = '" &
Forms![frm_pohdrecv]![frm_POLNsub].[Form]![Prodno] & "'")


Tom Lake
Click to expand...
 
Back
Top