lookup field as criterion

  • Thread starter Thread starter Judy
  • Start date Start date
J

Judy

I am creating a query to count the number of clients of
each ethnicity from the main client table. In the main
client table, the ethnicity field is a lookup linked to
the table Ethnicity with a list of choices.

When I put for example "Hispanic" as a criteria for the
querey, it gives me back a data type mismatch.

What is the code I would use in the criteria field of the
query? The eithnicity table is tblEthnicity.
 
I am creating a query to count the number of clients of
each ethnicity from the main client table. In the main
client table, the ethnicity field is a lookup linked to
the table Ethnicity with a list of choices.

When I put for example "Hispanic" as a criteria for the
querey, it gives me back a data type mismatch.
Click to expand...

Exactly. This is one of many reasons that I DESPISE "Lookup Fields".

Your table *appears* to contain the word "Hispanic", *but it doesn't*;
instead the Lookup Field is CONCEALING the actual contents of your
table - a number linked to the primary key of tblEthnicity. If you
want to find records where the field contains the text string
"Hispanic" there *aren't any* - what's there is 3, or some other
numeric value.
What is the code I would use in the criteria field of the
query? The eithnicity table is tblEthnicity.
Click to expand...

Create a Query joining your table to tblEthnicity by the (hidden,
thanks to the Lookup misfeature) EthnicityID, and put the criterion on
the Ethnicity field from tblEthnicity.

And get rid of all your Lookups. They are (as you can see) more of a
hassle than a help once you get past the very minimal basics.
 
The free downloadable sample database at www.bullschmidt.com/access uses
the query by form concept so that on the invoices dialog one can
optionally choose a rep, a customer, and perhaps a date range, click on
a button that says "Input," and then have the invoice form open up
showing all the invoices that match the criteria.

And here is how the query by form concept can work.

On the invoices dialog there are the following controls:
InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
InvDateMax with DefaultValue of =Date()
InvRepNum with DefaultValue of *
InvCustNum with DefaultValue of *

Also on the invoices dialog there is a command button called
cmdInput to open the invoices form with the following code behind
the OnClick property:
DoCmd.OpenForm "frmInv"

And of course there could be a button to open a report the same way:
DoCmd.OpenReport "rptInv", acViewPreview

The invoices form (frmInv) has RecordSource property of qryInv.

And the qryInv query's criteria for the InvDate field has:
Between [Forms]![frmInvDialog]![InvDateMin] And
[Forms]![frmInvDialog]![InvDateMax]

And the qryInv query's criteria for the RepNum field has:
Like [Forms]![frmInvDialog]![InvRepNum]

And the qryInv query's criteria for the CustNum field has:
Like [Forms]![frmInvDialog]![CustNum]

One related point is that you probably wouldn't want to allow blanks
(i.e. Nulls) in fields that are going to be used with Like in any
criteria for that field. Otherwise the blanks wouldn't be shown. And
to counter that you might consider creating the query's SQL statement
dynamically so that the criteria on a particular field isn't used
unless needed.

--
J. Paul Schmidt, Freelance Access and ASP Developer
http://www.Bullschmidt.com/Login.asp - Database on the Web Demo
http://www.Bullschmidt.com/Access
Sample Access Invoices Database


Posted via http://dbforums.com
 
Back
Top