Count Records in multiple tables

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.

On Mon, 15 Mar 2010 12:13:10 -0700, Jeff
wrote:

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.

As Bob says, you're misinterpreting how domain functions work. The first
argument is what you want to count (or sum, or look up, or whatever); the
second is a text string specifying the name of the table or query; the third
is another text string with a valid SQL WHERE clause (without the word WHERE)
providing the criteria. Try

Appointments: DCount("*","[dbo_REFERRAL_ORDER]", "[APPT_DT] is not Null")

Unless you create a Query appropriatly joining or unioning the two tables
dbo_REFERRAL_ORDER and dbo_ORDER_LOOKUP3 you will not be able to reference
both tables in the same lookup. My guess is that a UNION query would be in
order, but I don't know anything about the structures or relationships.
--

John W. Vinson [MVP]

I was indeed misunderstanding what I needed to use DCOUNT.

I was able to solve this using a sub-query to identify Non-Null Appointment
Dates in each table and add the number of appointment dates for each order.
Then, in the main query, I summed the number of non-zero results for each
person's orders to get their actual appointment count.


"John W. Vinson" wrote:

On Mon, 15 Mar 2010 12:13:10 -0700, Jeff
wrote:

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.

As Bob says, you're misinterpreting how domain functions work. The first
argument is what you want to count (or sum, or look up, or whatever); the
second is a text string specifying the name of the table or query; the third
is another text string with a valid SQL WHERE clause (without the word WHERE)
providing the criteria. Try

Appointments: DCount("*","[dbo_REFERRAL_ORDER]", "[APPT_DT] is not Null")

Unless you create a Query appropriatly joining or unioning the two tables
dbo_REFERRAL_ORDER and dbo_ORDER_LOOKUP3 you will not be able to reference
both tables in the same lookup. My guess is that a UNION query would be in
order, but I don't know anything about the structures or relationships.
--

John W. Vinson [MVP]

.