Lookup values in a table to be used in a query

[Guest]

I have a table [Invoice Overview Data Table] which has a field [Open/Closed]
in it. This overview Data Table holds the general information about the data
I am collecting on costs of an invoice.

Then I have another table that collects the cost data of the invoices and
this table is called [Invoice Data Table].

The two tables ([Invoice Overview Data Table] and [Invoice Data Table]) both
have common fields called [Invoice Number].

I'd like to use DLookup to get the value for [Open/Closed] in the [Invoice
Overview Table] and place it in a query based on [Invoice Data Table]. I.e. I
want to pull the [Open/Closed] value for the Invoice numbers that match among
[Invoice Overview Data Table] and [Invoice Data Table].

When I use DLookup now, it returns the first [Open/Closed] value for all the
records. It does not appear to be looking at the invoice numbers, matching
them and then selecting the [Open/Closed] value.

Here is the code I used:
Open/Closed: DLookUp("[Invoice Overview Table]![Open/Closed]","[Invoice
Overview Table]","[Invoice Overview Table]![Invoice Number]=[Invoice Number]")

Is there another function I should be using?

I would appreciate any help on this.

Thanks.

Ebitari

 

[pietlinden]

You're thinking in terms of Excel. Excel and Access are fundamentally
different. You don't need a Lookup anywhere. Just add the two tables
to the QBE grid, join them if you need to, and drop in the fields you
wnat to see in your output.

 

[John W. Vinson]

I have a table [Invoice Overview Data Table] which has a field [Open/Closed]
in it. This overview Data Table holds the general information about the data
I am collecting on costs of an invoice.

Then I have another table that collects the cost data of the invoices and
this table is called [Invoice Data Table].

The two tables ([Invoice Overview Data Table] and [Invoice Data Table]) both
have common fields called [Invoice Number].

I'd like to use DLookup to get the value for [Open/Closed] in the [Invoice
Overview Table] and place it in a query based on [Invoice Data Table]. I.e. I
want to pull the [Open/Closed] value for the Invoice numbers that match among
[Invoice Overview Data Table] and [Invoice Data Table].

When I use DLookup now, it returns the first [Open/Closed] value for all the
records. It does not appear to be looking at the invoice numbers, matching
them and then selecting the [Open/Closed] value.

Here is the code I used:
Open/Closed: DLookUp("[Invoice Overview Table]![Open/Closed]","[Invoice
Overview Table]","[Invoice Overview Table]![Invoice Number]=[Invoice Number]")

Is there another function I should be using?

As Piet says, you should be using Access as a relational database, rather than
as Excel on steroids. You don't need ANY function at all. Just create a Query
joining the two tables on the Invoice Number field; you then have all the
fields in both tables available.

Just FWIW, the reason your DLookUp didn't work is that it retrieved all those
records from [Invoice Overview Table] for which the value of Invoice Number
was equal to itself - that is, all records. It then returned the first one.

If (and this *can* happen) you need to use a DLookUp on one table in a query
based on another table, you need to take the search criterion - the Invoice
Number in Invoice Data Table - "out" of the quotes, so that the dlookup is
comparing the value in the current record of the query with the values in the
table:

Open/Closed: DLookUp("[Invoice Overview Table]![Open/Closed]","[Invoice
Overview Table]","[Invoice Overview Table]![Invoice Number]=" & [Invoice
Number])


John W. Vinson [MVP]

 

[Guest]

Great. Thanks. I figured it out.

 

[Guest]

Great. Thanks for your help. Very useful.

I have a table [Invoice Overview Data Table] which has a field [Open/Closed]
in it. This overview Data Table holds the general information about the data
I am collecting on costs of an invoice.

Then I have another table that collects the cost data of the invoices and
this table is called [Invoice Data Table].

The two tables ([Invoice Overview Data Table] and [Invoice Data Table]) both
have common fields called [Invoice Number].

I'd like to use DLookup to get the value for [Open/Closed] in the [Invoice
Overview Table] and place it in a query based on [Invoice Data Table]. I.e. I
want to pull the [Open/Closed] value for the Invoice numbers that match among
[Invoice Overview Data Table] and [Invoice Data Table].

When I use DLookup now, it returns the first [Open/Closed] value for all the
records. It does not appear to be looking at the invoice numbers, matching
them and then selecting the [Open/Closed] value.

Here is the code I used:
Open/Closed: DLookUp("[Invoice Overview Table]![Open/Closed]","[Invoice
Overview Table]","[Invoice Overview Table]![Invoice Number]=[Invoice Number]")

Is there another function I should be using?

As Piet says, you should be using Access as a relational database, rather than
as Excel on steroids. You don't need ANY function at all. Just create a Query
joining the two tables on the Invoice Number field; you then have all the
fields in both tables available.

Just FWIW, the reason your DLookUp didn't work is that it retrieved all those
records from [Invoice Overview Table] for which the value of Invoice Number
was equal to itself - that is, all records. It then returned the first one.

If (and this *can* happen) you need to use a DLookUp on one table in a query
based on another table, you need to take the search criterion - the Invoice
Number in Invoice Data Table - "out" of the quotes, so that the dlookup is
comparing the value in the current record of the query with the values in the
table:

Open/Closed: DLookUp("[Invoice Overview Table]![Open/Closed]","[Invoice
Overview Table]","[Invoice Overview Table]![Invoice Number]=" & [Invoice
Number])


John W. Vinson [MVP]

 

[Guest]

what was confusing me with the joins is that when you add a table to the
query it automatically joins then in ID. I had to delete the ID join and
join on [Invoice Number]. That worked.

Thanks.

 

[Guest]

Thanks for your help with this. Could you also look at my issue titled
'Visual Basic Code to open a folder in Code builder'?

I haven't had any replies about this one.

Thanks.

I have a table [Invoice Overview Data Table] which has a field [Open/Closed]
in it. This overview Data Table holds the general information about the data
I am collecting on costs of an invoice.

Then I have another table that collects the cost data of the invoices and
this table is called [Invoice Data Table].

The two tables ([Invoice Overview Data Table] and [Invoice Data Table]) both
have common fields called [Invoice Number].

I'd like to use DLookup to get the value for [Open/Closed] in the [Invoice
Overview Table] and place it in a query based on [Invoice Data Table]. I.e. I
want to pull the [Open/Closed] value for the Invoice numbers that match among
[Invoice Overview Data Table] and [Invoice Data Table].

When I use DLookup now, it returns the first [Open/Closed] value for all the
records. It does not appear to be looking at the invoice numbers, matching
them and then selecting the [Open/Closed] value.

Here is the code I used:
Open/Closed: DLookUp("[Invoice Overview Table]![Open/Closed]","[Invoice
Overview Table]","[Invoice Overview Table]![Invoice Number]=[Invoice Number]")

Is there another function I should be using?

As Piet says, you should be using Access as a relational database, rather than
as Excel on steroids. You don't need ANY function at all. Just create a Query
joining the two tables on the Invoice Number field; you then have all the
fields in both tables available.

Just FWIW, the reason your DLookUp didn't work is that it retrieved all those
records from [Invoice Overview Table] for which the value of Invoice Number
was equal to itself - that is, all records. It then returned the first one.

If (and this *can* happen) you need to use a DLookUp on one table in a query
based on another table, you need to take the search criterion - the Invoice
Number in Invoice Data Table - "out" of the quotes, so that the dlookup is
comparing the value in the current record of the query with the values in the
table:

Open/Closed: DLookUp("[Invoice Overview Table]![Open/Closed]","[Invoice
Overview Table]","[Invoice Overview Table]![Invoice Number]=" & [Invoice
Number])


John W. Vinson [MVP]

 

[UpRider]

Probably the best you can do is to hit F5 when Explorer has the focus. That
will update the display.

HTH, UpRider

Thanks for your help with this. Could you also look at my issue titled
'Visual Basic Code to open a folder in Code builder'?

I haven't had any replies about this one.

Thanks.

I have a table [Invoice Overview Data Table] which has a field
[Open/Closed]
in it. This overview Data Table holds the general information about the
data
I am collecting on costs of an invoice.

Then I have another table that collects the cost data of the invoices
and
this table is called [Invoice Data Table].

The two tables ([Invoice Overview Data Table] and [Invoice Data Table])
both
have common fields called [Invoice Number].

I'd like to use DLookup to get the value for [Open/Closed] in the
[Invoice
Overview Table] and place it in a query based on [Invoice Data Table].
I.e. I
want to pull the [Open/Closed] value for the Invoice numbers that match
among
[Invoice Overview Data Table] and [Invoice Data Table].

When I use DLookup now, it returns the first [Open/Closed] value for all
the
records. It does not appear to be looking at the invoice numbers,
matching
them and then selecting the [Open/Closed] value.

Here is the code I used:
Open/Closed: DLookUp("[Invoice Overview Table]![Open/Closed]","[Invoice
Overview Table]","[Invoice Overview Table]![Invoice Number]=[Invoice
Number]")

Is there another function I should be using?

As Piet says, you should be using Access as a relational database, rather
than
as Excel on steroids. You don't need ANY function at all. Just create a
Query
joining the two tables on the Invoice Number field; you then have all the
fields in both tables available.

Just FWIW, the reason your DLookUp didn't work is that it retrieved all
those
records from [Invoice Overview Table] for which the value of Invoice
Number
was equal to itself - that is, all records. It then returned the first
one.

If (and this *can* happen) you need to use a DLookUp on one table in a
query
based on another table, you need to take the search criterion - the
Invoice
Number in Invoice Data Table - "out" of the quotes, so that the dlookup
is
comparing the value in the current record of the query with the values in
the
table:

Open/Closed: DLookUp("[Invoice Overview Table]![Open/Closed]","[Invoice
Overview Table]","[Invoice Overview Table]![Invoice Number]=" & [Invoice
Number])


John W. Vinson [MVP]