Obtaining data from a table in a query using the Dlookup function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

greencecil - 27 Jan 2005 16:23 GMT

Hi! This is a long one, so here's the background:
I've got 2 tables, one called tblEnergy and one called tblPackaging. In
tblEnergy I have data for energy consumed for production of some different
types of packaging. The columns in the table have (amongst others) the
headings 'material type' and 'energy for production (MJ/kg)' . Under material
type I have materials like cardboard, plastic film, aluminium foil etc. For
each of these materials the energy for production is given in the 'energy for
production' column.
In 'tblPackaging' I can choose what material the packaging is made of and
the packaging materials are given in columns in the table that have names
like 'material type 1', 'material type 2' etc.
I've made a query, where I'm trying to use the Dlookup function in order to
obtain the energy for production for the relevant material. So if I've
understood the Dlookup function correctly I think I should be able to use the
following formula: Dlookup("[tblEnergy]![Energy for production
(MJ/kg)]","tblEnergy","[tblEnergy]![material type]=[material type 1]"
However I get error messages and this does not seem to work. Has anyone got
any bright ideas on how I should write the correct syntax for this function?

Reply to this Message

Penguin - 28 Jan 2005 04:01 GMT

Try this:
DLookUp("[Energy for production]","tblEnergy","[material type] = 1")

>Hi! This is a long one, so here's the background:
>I've got 2 tables, one called tblEnergy and one called tblPackaging. In
[quoted text clipped - 14 lines]
>However I get error messages and this does not seem to work. Has anyone got
>any bright ideas on how I should write the correct syntax for this function?

Reply to this Message

greencecil - 28 Jan 2005 09:05 GMT

Thanks for your suggestion.
I will try it, but I don't really understand why "material type = 1" would
give me the value for 'material type 2' or 'material type 3' when I need it?
I want the relevant material type value, which can be for 'material type 1' ,
'material type 2', 'material type 3' etc. I can try it, but I don't think it
will work for the other material types.
Any suggestions on syntax that will solve this?

> Try this:
> DLookUp("[Energy for production]","tblEnergy","[material type] = 1")
[quoted text clipped - 17 lines]
> >However I get error messages and this does not seem to work. Has anyone got
> >any bright ideas on how I should write the correct syntax for this function?

Reply to this Message