dlookup problem

[Guest]

I am going nuts trying to get a DlookUp function to work. I have a database
with (at the moment) 2 tables. One table is called tblCountries and contains
several fields, the important ones being Country and RiskClass.

The main table is called tblInternationalCustomers and has, amoung other
fields, one called Country.

I have also created a form, frmInternationCustomers, which contains a number
of fields including Country and a text box called CountryRiskLookup that
contains the lookup code that I am looking for.

What I want to have happen is a lookup to occur when a country is entered
into the frmInternationalCustomers Country field. I want the lookup to look
in the tblCountries Country field and return the RiskClass that is shown for
that country.

I have tried many variations of the following to no avail:

=DLookUp("[RiskClass]","tblCountries","[Country] =
Form![frmInternationalCustomers]![Country]")

Once I get this lookup to work I have to have a lookup that will take city
and a state field information and look to see if the customer is located in a
high risk drug traffic area or a high risk financial crimes area. That one
will be more challenging.

I have both of these lookups working in Excel but don't know how to make
them work in Access.

Your help is most appreciated!

David Vollmer

 

[Duane Hookom]

Try:
=DLookUp("[RiskClass]","tblCountries","[Country] = """ & [Country] & """")
I prefer to make the Country a combo box that includes the RiskClass field.
Then add a text box with a control source of:
=cboCountry.Column(1)
The column numbers are zero-based.

 

[Guest]

Duane,

Thank you, that worked!! I don't understand the quotes and ampersands however.

The reason I didn't use a combo box is that most of the records will be
imported into the database with a country already included.

In the Excel workbook I have a master sheet and over 50 State/Possession
sheets that contain the cities and counties of all of the high risk areas in
this country. I have kept the states separate because there are many
duplicate city names in many states. I am not sure even how to start that
lookup process, especially since it requires two entities.

Thank you again!

David

Try:
=DLookUp("[RiskClass]","tblCountries","[Country] = """ & [Country] & """")
I prefer to make the Country a combo box that includes the RiskClass field.
Then add a text box with a control source of:
=cboCountry.Column(1)
The column numbers are zero-based.

--
Duane Hookom
MS Access MVP

I am going nuts trying to get a DlookUp function to work. I have a database
with (at the moment) 2 tables. One table is called tblCountries and
contains
several fields, the important ones being Country and RiskClass.

The main table is called tblInternationalCustomers and has, amoung other
fields, one called Country.

I have also created a form, frmInternationCustomers, which contains a
number
of fields including Country and a text box called CountryRiskLookup that
contains the lookup code that I am looking for.

What I want to have happen is a lookup to occur when a country is entered
into the frmInternationalCustomers Country field. I want the lookup to
look
in the tblCountries Country field and return the RiskClass that is shown
for
that country.

I have tried many variations of the following to no avail:

=DLookUp("[RiskClass]","tblCountries","[Country] =
Form![frmInternationalCustomers]![Country]")

Once I get this lookup to work I have to have a lookup that will take city
and a state field information and look to see if the customer is located
in a
high risk drug traffic area or a high risk financial crimes area. That one
will be more challenging.

I have both of these lookups working in Excel but don't know how to make
them work in Access.

Your help is most appreciated!

David Vollmer