Jeff said:
I am trying to get a count of Appointments from multiple tables using
this statement - when I try to run it I get the unspecified
expression error.
Appointments: DCount("*",[dbo_REFERRAL_ORDER.APPT_DT] is not Null Or
[dbo_ORDER_LOOKUP3.APPT_DT]is not null)
The APPT_DT field in each table is a date field. The tables are
joined by Orner Number, but the appointment date will populate in the
different tables based on the data entry source. However, there
could be data for the same order in both tables, so I cannot count
each one indepentently & sum the result.
You need to take another look at the documentation:
**********************************************************************
DCount Method
<snip>
expression.DCount(Expr, Domain, Criteria)
expression Required. An expression that returns one of the objects in
the Applies To list.
Expr Required String. An expression that identifies the field for which
you want to count records. It can be a string expression identifying a
field in a table or query, or it can be an expression that performs a
calculation on data in that field. In expr, you can include the name of
a field in a table, a control on a form, a constant, or a function. If
expr includes a function, it can be either built-in or user-defined, but
not another domain aggregate or SQL aggregate function.
Domain Required String. A string expression identifying the set of
records that constitutes the domain. It can be a table name or a query
name.
Criteria Optional Variant. An optional string expression used to
restrict the range of data on which the DCount function is performed.
For example, criteria is often equivalent to the WHERE clause in an SQL
expression, without the word WHERE. If criteria is omitted, the DCount
function evaluates expr against the entire domain. Any field that is
included in criteria must also be a field in domain; otherwise the
DCount function returns a Null.
<snip>
=DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")
**********************************************************************
The domain can either be a table name or the name of a saved query. So
your first order of business is to create a saved query that returns the
records you wish to count. Unless you intend to reuse this saved query
to support other tasks (for example, a form or report that displays the
records resulting from that query), I would optimize this even further
by writing a query to actually perform the count and using DLookup to
display the result:
select count(*) as ApptCount from
dbo_REFERRAL as r join dbo_ORDER_LOOKUP3 o
ON r.[Orner Number] = o.[Orner Number]
WHERE r.APPT_DT is not null or o.APPT_DT is not null
Run this query and verify it returns the propery results, then save it
as AppointmentCount. Then, use this expression in your new query:
Appointments: DLookup("ApptCount","AppointmentCount")
