Lookup (or variant) function

[Monty]

Hope this is the right forum for this question; my apologies if not.

I'm wondering if there's a variation of DLookup, DCount, or another
function entirely that would allow me to find a record based on the
values of two fields in any one record. I'm not sure of the syntax
(from the help page), but I'm guessing it would look something like:

DLookup("[Field1 & Field2", "MyDomain", "Field1 = Value1 AND Field2 =
Value2")

Hope that makes sense. Does such a syntax or separate function exist?

Thanks

 

[Guest]

AFAIK, there is no function provided that does what you ask. You can either
write one yourself, or use two different calls:

DLookup("[Field1]", "MyDomain", "Field1 = Value1 AND Field2 => Value2")
DLookup("[Field2]", "MyDomain", "Field1 = Value1 AND Field2 => Value2")

How do you intend to use the returned value(s)?

Sprinks

 

[fredg]

Hope this is the right forum for this question; my apologies if not.

I'm wondering if there's a variation of DLookup, DCount, or another
function entirely that would allow me to find a record based on the
values of two fields in any one record. I'm not sure of the syntax
(from the help page), but I'm guessing it would look something like:

DLookup("[Field1 & Field2", "MyDomain", "Field1 = Value1 AND Field2 =
Value2")

Hope that makes sense. Does such a syntax or separate function exist?

Thanks

No, your syuntax won't work.
If you know that Field1 = Value1 and Field2 = Value2 why do you need
DLookUp to tell you what Field1 or Field2 values are?

If CriteriaField1 and CriteriaField2 are both Number datatypes:

= DLookup("[Field1]", "MyDomain", "[CriteriaField1] = " & [Control1] &
" AND [CriteriaField2] = " & [Control2)

The Criteria fields are the fields that you use to restrict the value
returned in [Field1].

Control1 and Control2 are the controlnames on your form that contains
the criteria.

However, if the Criteria fields are either Text or Date datatypes,
then you need a different syntax.
Look up in VBA help each of the following:
DLookUp
Where Clause
Restrict data to a subset of records.

 

[Arvin Meyer [MVP]]

You can only look up 1 value, but it can be based upon multiple criteria:

DLookup("[UserID]", "tblPersons", "[LastName] = '" & txtLName & "' And
[FirstName] = '" & txtFName & "'")

You can also use a recordset to look up a record with multiple criteria and
you can return the values from several fields or even several records.

 

[John W. Vinson]

My intention is to sift through records that may contain duplicate
entries in Field1 OR Field2, but not both. The two fields combined
will form a unique 'key', so to speak. So there can be duplicate
values in Field1 and again in Field2, but not for the same record.
The comparison values come from a form not attached to a table: it's a
user entry form for adding new records to the DB.

I'm hearing a couple of things, and one of them leads me to believe I
can possibly use a DCount thusly:

intResult = DCount("[Field1]", "MyDB", "[Field1] = ' " & Me.Control1 &
" ' AND [Field2] = ' " & Me.Control2 & " ' ")

If the function returns non-zero then a record containing both of
those values exists (I hope I have that right), which is all I really
need to know. I can write error routines based on that.

Do I have that correct?

If you're just counting records you can use

DCount("*", "tablename", "criteria")

The criteria is a string expression which is a valid SQL WHERE clause. It can
be almost arbitrarily complex - multiple fields, subqueries, anything which
will work in a Query. You can even create a query in the query design window
which returns the records you want, and cut and paste the WHERE clause
(without the word WHERE) into the third argument of a domain function.


HOWEVER... to just find duplicate records in a table based on two fields, you
don't need to use domain functions at all. You can prevent such records from
ever being created in the first place by using a unique Index on the two
fields (an index can refer to up to *ten* fields, not just one); or you can
use a Totals query

SELECT Field1, Field2, First([someotherfield]), Last([someotherfield]),
Count(*)
FROM MyTable
GROUP BY Field1, Field2
HAVING Count(*) > 1;

to see how many duplicates there are, and (if desired) two arbitrary different
values of some other field in the table.

John W. Vinson [MVP]