USING DLOOKUP TABLES

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

re: USING DLOOKUP TABLES

Christine Henderson wrote:[color=blue]
> 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
>[/color]

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.

re: USING DLOOKUP TABLES

"Christine Henderson" <castle@idl.com.au> wrote in message
news:1127113545.43858@idlweb...[color=blue]
>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[/color]


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.

re: USING DLOOKUP TABLES

Christine,

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

Jeremy

re: USING DLOOKUP TABLES

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 ***

re: USING DLOOKUP TABLES

Christine Henderson wrote:[color=blue]
> 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 ***[/color]

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

re: USING DLOOKUP TABLES

"Christine Henderson" <castle@idl.com.au> wrote in message
news:WVNZe.147$hJ5.18180@news.uswest.net...[color=blue]
>
> 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[/color]

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.

re: USING DLOOKUP TABLES

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!

re: USING DLOOKUP TABLES

Jana wrote:[color=blue]
> 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![/color]

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