Dlookup Problem

[DavidW]

I am trying to from a form find out if a queries result = 2
In the query"onuse" is a field called "type". The numbers in that field are
1,2,or3.
I need to use an if statement with this ,I tried

If DLookup("type", "vehset") = 2 Then

and it doesnt recognize then number.
What have I missed?
Thanks
David

 

[-Ted]

Your Dlookup function does not contain any criteria, so
it will just return the value of the "type" field for the
first record in your query. If your query contains more
than 1 record you need to set criteria in the third part
of the function to tell Access which record to lookup the
value for. For instance:

If DLookup("[type]", "vehset", "[Proj ID] =" &
[Me.ProjID]) = 2 Then

Would be an example where the Dlookup function would
lookup the row where the [Proj ID] field of the "vehset"
query = the ProjID field from a current form, and it
would return the corresponding value of the [type] field
in the query.

 

[DavidW]

I maybe using the wrong function.

What I am trying to do is to look through the records to see if any entries
in "type" contain a 2, and if so respond to the first one it comes to. Just
as long as there is a 2 in the field , I Dont need a match. I just need to
know if a 2 exist.
What function would be good for that?

 

[Ted]

Oh, ok, in that case you are using the right function,
but you just need to set the criteria [Type]=2 in your
function. In this case your function will return 2 if it
finds a record, or it will be Null if it doesn't. Try
the following:

DLookup("[type]", "vehset", "[type] = 2" )

If you would rather have the expression return true or
false, you could use:

iif( Isnull(DLookup("[type]", "vehset", "[type] = 2" ))
=true,false,true)

Which would return true when a record with [Type]=2 is
found, and false otherwise.