Access, lookup for data in another table if matches

1
votes

I have a Union query with invoice data like invoice number, supplier and so on. This query is created for the purpose of providing credit note information.

My problem arise when I would like to provide exchange rate for invoices in different currencies. If there is for example RON currency, I need to check currency and date of invoice and then provide value from another table.

I stored currencies and their values in another database. I wanted to use Dlookup function but it works only current database. Not sure what can I do. Is VBA needed here or it can be avoided?

Edit: Having problem with syntax:

Query:

SELECT [Faktury].InvoiceNumber, [Faktury].InvoiceDate, [Faktury].InvoiceCountry, [Faktury].Currency, DLookUp("Value","Tabela1","Currency1 =" & [Currency]) AS Wyr1
FROM [Faktury];

Dlookup syntax:

DLookUp("Value";"Tabela1";"Currency1 =" & [Currency])

Query has column with Currency used in invoice and Tabela1 has Currency1 and Value. I get error or no value is shown...

1
ms-accesslookup
Where are you running this DLookUp, in VBA? And what is the query you are referring to that contains the column currency? – agold
Dlookup is run as a query Column. Please look at edit. – lowak
Don't use DLookUp here, just use use 'Tabela1' as a normal table in the query. – agold
Right... LEFT JOIN. I was not thinking clearly at that moment. Thanks! – lowak

1 Answers

3
votes

To access a a table in another database you can create a link to it:

Go to External Data > Access (although you could use any other type of data source) > choose the database file, and select Link to the data source by creating a linked table.

Link database

Then click Ok and select the table(s) you want to link (i.e. use in your database). Now you can use the table (Currency in my example) in your queries or in VBA like a normal table. For example with DLookup in VBA:

MsgBox DLookup("EuroValue", "Currency", "ID='" & InputBox("Currency?") & "'")

or in a (SQL) query:

SELECT EuroValue FROM [Currency] WHERE ID='USD';

or

SELECT DLookUp("EuroValue","Currency","ID='USD'");