Re: USING DLOOKUP TABLES

"Christine Henderson" <castle@xxxxxxxxxx> wrote in message
news:WVNZe.147$hJ5.18180@xxxxxxxxxxxxxxxxxx
>
> 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.





.

Relevant Pages

  • Re: problem getting a query to recognize "Not" and "<>" in a value list combo box
    ... Source was the one to display all records, so column 1 had a value of "*", ... and column 2 had a value of "All Leases". ... After reading your first suggestion, I modified those two components of the ... This query is based on one table, ...
    (microsoft.public.access.forms)
  • Re: Autonumber using alphanumerics
    ... So as I scroll though the client listing it will display the ... > consensus against using lookup data type fields in your table definitions. ... which I have been able to create and it works well in a query. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Show code meaning rather than code value
    ... To use a Text Box to display the Code's Meaning from a separate Lookup ... the query, and will not affect the relationships shown under Database Tools ...
    (microsoft.public.access.forms)
  • Re: Autonumber using alphanumerics
    ... forms display data. ... consensus against using lookup data type fields in your table definitions. ... which I have been able to create and it works well in a query. ... > message "no valid fields can be found in 'Corporate Client Details Query'. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Returning a value based on another value
    ... corresponding name displayed in the datasheet view of the table. ... You need to include the lookup table in the form's query. ... and using the lookup to display the project name on the form. ...
    (microsoft.public.access.modulesdaovba)