USING DLOOKUP TABLES

[Christine Henderson]

I have a problem using the above function in the following simplified
circumstance:

In the lookup table called "Klms Travelled" I have 3 fields, eg:

Receiver Name Receiver Suburb Klms Distance
Jones Melbourne 500
Harrison Sydney 200
Ford Brisbane 700
Jones Sydney 250
Ford Melbourne 550

In the main table named "Clients" I have the [Receiver Name] and [Receiver
Suburb] fields and based on this combination in a separate query (based on
"Clients") in a calculated field I have tried to extract the [Klms Distance]
data from the lookup table as follows:

Total Klms: DLookup("[Klms Distance]", "Klms Travelled", "[Receiver
Name]&[Receiver Suburb]")

I have also tried variations on the criteria but I either get the value of
500 for the first record in the lookup table or nothing.

Can anyone help me with a suitable criteria, or any other suggestions.

Thank you,
Christine

[Salad]

Christine Henderson wrote:

I have a problem using the above function in the following simplified
circumstance:

In the lookup table called "Klms Travelled" I have 3 fields, eg:

Receiver Name Receiver Suburb Klms Distance
Jones Melbourne 500
Harrison Sydney 200
Ford Brisbane 700
Jones Sydney 250
Ford Melbourne 550

In the main table named "Clients" I have the [Receiver Name] and [Receiver
Suburb] fields and based on this combination in a separate query (based on
"Clients") in a calculated field I have tried to extract the [Klms Distance]
data from the lookup table as follows:

Total Klms: DLookup("[Klms Distance]", "Klms Travelled", "[Receiver
Name]&[Receiver Suburb]")

I have also tried variations on the criteria but I either get the value of
500 for the first record in the lookup table or nothing.

Can anyone help me with a suitable criteria, or any other suggestions.

Thank you,
Christine

In Dlookup you pass the Column field to look up/calculate on, the table
name, and the filter clause (like a where clause w/o the word Where.

I will assume you have a query called Klms Travelled. A field in that
query is [Klms Distance]. You now need to filter. Quotes around
alphas, # around dates, nothing around numbers. Ex:
"[NameField] = 'Smith'"
"[DateField] = #" & Date & "#"
"NumberField = 123"

YOu might have a variable. Ex:
str = Smith
"[NameField] = '" & str & "'"

num = 123
"NumberField = " & num

Your where/filter clause needs to be adjusted.

[Brian Wilson]

"Christine Henderson" <ca****@idl.com.au> wrote in message
news:1127113545.43858@idlweb...

I have a problem using the above function in the following simplified
circumstance:

In the lookup table called "Klms Travelled" I have 3 fields, eg:

Receiver Name Receiver Suburb Klms Distance
Jones Melbourne 500
Harrison Sydney 200
Ford Brisbane 700
Jones Sydney 250
Ford Melbourne 550

In the main table named "Clients" I have the [Receiver Name] and [Receiver
Suburb] fields and based on this combination in a separate query (based on
"Clients") in a calculated field I have tried to extract the [Klms
Distance]
data from the lookup table as follows:

Total Klms: DLookup("[Klms Distance]", "Klms Travelled", "[Receiver
Name]&[Receiver Suburb]")

I have also tried variations on the criteria but I either get the value of
500 for the first record in the lookup table or nothing.

Can anyone help me with a suitable criteria, or any other suggestions.

Thank you,
Christine

For a fixed example, you would write (note the sets of quotes surrounding
the values):

DLOOKUP("[Klms Distance]",
"[Klms Travelled]",
"[Receiver Name]=""Ford"" AND [Receiver Suburb]=""Melbourne""")

In your case, instead of the values "Ford" and "Melbourne", you would get
the values from your main form.

[jeremygetsmail]

Christine,

Look into the help file. It will show you how to use DLookup.

Jeremy

[Christine Henderson]

Dear Brian

Thanks for your suggestion regarding the cominbation of the two fields,
ie [Receiver Name]= "Ford" AND [Receiver Suburb]= "Melbourne"

My problem is that I do not want to specify a record such as "Ford" and
"Melbourne" I need to lookup all records from my main table query and
have the klms travelled returned from the lookup table.

I understand the use of quotes etc and have tried the expression above
without specifying specific records however I only get the klms for the
first record in the lookup table.

Is it possible to specify just the field names as the criteria without
needing = a record.

Thanks for any help available on this problem.

Regards
Christine
*** Sent via Developersdex http://www.developersdex.com ***

[jimfortune]

Christine Henderson wrote:

Dear Brian

Thanks for your suggestion regarding the cominbation of the two fields,
ie [Receiver Name]= "Ford" AND [Receiver Suburb]= "Melbourne"

My problem is that I do not want to specify a record such as "Ford" and
"Melbourne" I need to lookup all records from my main table query and
have the klms travelled returned from the lookup table.

I understand the use of quotes etc and have tried the expression above
without specifying specific records however I only get the klms for the
first record in the lookup table.

Is it possible to specify just the field names as the criteria without
needing = a record.

Thanks for any help available on this problem.

Regards
Christine
*** Sent via Developersdex http://www.developersdex.com ***

I'm taking a shot in the dark. The SQL of your query might look
something like:

SELECT Main.[Receiver Name], Main.[Receiver Suburb],
Main.SomeDataField, [Klms Travelled].[Klms Distance] FROM Main INNER
JOIN [Klms Travelled] ON (Main.[Receiver Suburb] = [Klms
Travelled].[Receiver Suburb]) AND (Main.[Receiver Name] = [Klms
Travelled].[Receiver Name]);

Basically, you need to select both tables into the query editor and
link both the [Receiver Name] and [Receiver Suburb] fields. You can
even get fancy by changing the join types by clicking on the lines
joining the two tables. You can show any data from Main along with the
[Klms Distance] for that combination of [Receiver Name] and [Receiver
Suburb].

The way I would actually do it is to use a subquery to gather just the
[Klms Distance] appropriate for that record instead of using the query
you have. Perhaps a little more information about how the [Klms
Distance] is obtained would allow someone to craft an appropriate
subquery.

James A. Fortune

[Brian Wilson]

"Christine Henderson" <ca****@idl.com.au> wrote in message
news:WV*****************@news.uswest.net...

Dear Brian

Thanks for your suggestion regarding the cominbation of the two fields,
ie [Receiver Name]= "Ford" AND [Receiver Suburb]= "Melbourne"

My problem is that I do not want to specify a record such as "Ford" and
"Melbourne" I need to lookup all records from my main table query and
have the klms travelled returned from the lookup table.

I understand the use of quotes etc and have tried the expression above
without specifying specific records however I only get the klms for the
first record in the lookup table.

Is it possible to specify just the field names as the criteria without
needing = a record.

Thanks for any help available on this problem.

Regards
Christine

Hi Christine
Perhaps the suggestion from James will be of some help, but I just wanted to
add that DLOOKUPs are supposed to be an easy way to look up a single record
in a table. E.g. if the name is Ford and the suburb is Melbourne, then how
many kilometers is this?
What I now guess you are doing is a query which should involve two tables.
So just create a new query in design view and add the main table, then the
lookup table.
Drag the field [Clients].[Receiver Name] onto [Klms Travelled].[Receiver
Name] and then [Clients].[Receiver Suburb] onto [Klms Travelled].[Receiver
Suburb]. Then select the fields you want to display and run the query. You
will notice that if the distance is not found in the lookup table, then the
record will not be displayed at all. To remedy this, return to the design
view, right-click each joining line and make sure you select "display all
records from Clients...". When you re-run the query, you should find that
all client records are displayed and where possible a distance is also
included.
Let us know if this sorts it out.

[Jana]

Christine:

You're wanting to use the DLookup to get a specific Klms Traveled, but
you aren't specifying WHICH record you want looked up. That's why
you're only getting the first record's Klms Traveled. The last
parameter of the DLookup is a Where clause without the word Where. In
the example you have your Where clause is really saying "Where
[Receiver Name]&[Receiver Suburb]". The problem is, you don't have
what [Receiver Name]&[Receiver Suburb] should be equal to. You need to
feed some criteria to your clause so that it says [Receiver
Name]&[Receiver Suburb] = 'Something'. You need to pass something to
your DLookup so it makes a complete criteria specification. For
instance, if you wanted to find the Klms Traveled for Harrison in
Sydney, your DLookup would look like this:

DLookup("[Klms Distance]", "Klms Travelled", "[Receiver Name]&[Receiver
Suburb] = " & "'HarrisonSydney'")

You can replace the 'HarrisonSydney' with a field from a form, or if
this is for a query, you can use the field names from your query.

For a form field, do this:
DLookup("[Klms Distance]", "Klms Travelled", "[Receiver Name]&[Receiver
Suburb]='" & Forms!FormName!FieldName & "'")

If you're trying to do this in a query, do this:
DLookup("[Klms Distance]", "Klms Travelled", "[Receiver Name]&[Receiver
Suburb]='" & [Receiver Name] & [Receiver Suburb] & "'")

Hope that helps!

[jimfortune]

Jana wrote:

Christine:

You're wanting to use the DLookup to get a specific Klms Traveled, but
you aren't specifying WHICH record you want looked up. That's why
you're only getting the first record's Klms Traveled. The last
parameter of the DLookup is a Where clause without the word Where. In
the example you have your Where clause is really saying "Where
[Receiver Name]&[Receiver Suburb]". The problem is, you don't have
what [Receiver Name]&[Receiver Suburb] should be equal to. You need to
feed some criteria to your clause so that it says [Receiver
Name]&[Receiver Suburb] = 'Something'. You need to pass something to
your DLookup so it makes a complete criteria specification. For
instance, if you wanted to find the Klms Traveled for Harrison in
Sydney, your DLookup would look like this:

DLookup("[Klms Distance]", "Klms Travelled", "[Receiver Name]&[Receiver
Suburb] = " & "'HarrisonSydney'")

You can replace the 'HarrisonSydney' with a field from a form, or if
this is for a query, you can use the field names from your query.

For a form field, do this:
DLookup("[Klms Distance]", "Klms Travelled", "[Receiver Name]&[Receiver
Suburb]='" & Forms!FormName!FieldName & "'")

If you're trying to do this in a query, do this:
DLookup("[Klms Distance]", "Klms Travelled", "[Receiver Name]&[Receiver
Suburb]='" & [Receiver Name] & [Receiver Suburb] & "'")

Hope that helps!

In addition to what I wrote it would be good to add that DLookup is, in
effect, a poor man's equivalent to a subquery. So think in terms of a
normal query containing a DLookup to get a particular piece of
information that relates to the current record in the query. Perhaps
show the fields of interest in Main.

James A. Fortune