DLOOKUP in a query??

[xyba]

I have a query in which I want to put a Dlookup expression but can't seem to get it to work.

One of the fields in my query is FieldA. I want to lookup the value of FieldA in TableX and return the corresponding value in FieldB of TableX.

Both fields are text fields by the way.

The only help I can find is referring to looking up the values from a form.

Not having a good day with this Db today :banghead:

*EDIT* Should maybe have mentioned, TableX is not the main table referenced in the query it is a simple two field list table with just FieldA (a name field) and FieldB (a department field).

 

[stopher]

Try
DLookup("FieldA","TableX", "FieldB='someTextValue'")
or
DLookup("FieldA","TableX", "FieldB='" & [myQueryField] & "'")

note the use of single and double quotes

 

[jdraw]

In addition to the suggestion from stopher, could you please post the SQL of your query?

What is the relationship between your "main table" and tableX?

It is usually more expedient to get a focused response when you use real table names and SQL rather than using the tableX fieldA.... which does not help with context.

Good luck.

 

[xyba]

Try
DLookup("FieldA","TableX", "FieldB='someTextValue'")
or
DLookup("FieldA","TableX", "FieldB='" & [myQueryField] & "'")

note the use of single and double quotes

Thanks but I'm still confused. If I want the query to return the value of FieldB what would I write instead of 'someTextValue' or instead of [myQueryField]. I'm probably being thick

 

[xyba]

In addition to the suggestion from stopher, could you please post the SQL of your query?

What is the relationship between your "main table" and tableX?

It is usually more expedient to get a focused response when you use real table names and SQL rather than using the tableX fieldA.... which does not help with context.

Good luck.

Sure...here's the SQL. I want to lookup the value in Team field in the FOSListT table that corresponds to FOS field in LocationImprov query.

SELECT LocationImprov.ID, LocationImprov.Location, LocationImprov.Processed, LocationImprov.Received, LocationImprov.DocID, LocationImprov.SubjectID, LocationImprov.FirstName, LocationImprov.LastName, LocationImprov.Ref1Date, DateDiff("d",[LastDate],Date()) AS Days, LocationImprov.LastDate, LocationImprov.FOS
FROM LocationImprov INNER JOIN FOSListT ON LocationImprov.FOS = FOSListT.FOS

 

[jdraw]

Since your tables are joined (and Team field is in the FOSListT table) , why not use/try

SELECT LocationImprov.ID, LocationImprov.Location, 
LocationImprov.Processed, LocationImprov.Received,
 LocationImprov.DocID, LocationImprov.SubjectID, 
LocationImprov.FirstName, LocationImprov.LastName, 
LocationImprov.Ref1Date, DateDiff("d",[LastDate],Date()) AS Days, 
LocationImprov.LastDate, LocationImprov.FOS
[COLOR="Red"],FOSListT.Team[/COLOR]
FROM LocationImprov INNER JOIN FOSListT ON 
LocationImprov.FOS = FOSListT.FOS

 

[xyba]

Since your tables are joined (and Team field is in the FOSListT table) , why not use/try

SELECT LocationImprov.ID, LocationImprov.Location, 
LocationImprov.Processed, LocationImprov.Received,
 LocationImprov.DocID, LocationImprov.SubjectID, 
LocationImprov.FirstName, LocationImprov.LastName, 
LocationImprov.Ref1Date, DateDiff("d",[LastDate],Date()) AS Days, 
LocationImprov.LastDate, LocationImprov.FOS
[COLOR=red],FOSListT.Team[/COLOR]
FROM LocationImprov INNER JOIN FOSListT ON 
LocationImprov.FOS = FOSListT.FOS

:banghead: Lol...I said I was having a bad day.

Thanks for pointing that out.