DLookup using another Dlookup result

[stu999]

Hi

I am trying to do another DLookup in my query, using a First DLookup result as the 2nd's field name. I will post the code hopefully to explain better.

1st Dlookup, this returns a number from 0 to 5, which is working.

SNSIndex: DLookUp([SoilType],[TableName])

2nd DLookup, not working properly as it returns the same values as the 1st DLookup even though it is supposed to be looking at a different Table.

N: DLookUp([SNSIndex],[TableNameN],[Soil]=[SoilType])

Image showing joins etc

Thanks for any help
Stu

 

[Trevor G]

Stu you need to be aware if it is finding string, numerics etc.

Look here for example

For numerical values:

DLookup("FieldName" , "TableName" , "Criteria = n")​

For strings: (note the single apostrophe before and after the string value)

DLookup("FieldName" , "TableName" , "Criteria= 'string'")​

For dates: (note the # before and after the date value)

DLookup("FieldName" , "TableName" , "Criteria= #date#")​

 

[stu999]

Hi Trevor

The SNSIndex value for the first DLookup is returning a number, therefore I should have it written like;

SNSIndex: DLookUp("SoilType","TableName","Crop = SNSCat")

The problem with this is that it gives me an error database engine cannot find input table or query name 'TableName' which I why I had it written with [] brackets as that seemed to work.
I have also been trying Allen Browne's ELookup but get similiar results.

Could it be something to do with my joins in the query that is causing the issue's?

Thanks
Stu

 

[rainman89]

im not sure you can create a expression and call from it. You need a table or query to pull from.

TablenameN is an expression in your current query

As for your 2nd problem, you have just tablename in there. You probably dont have a table named tablename...

 

[stu999]

Hi
I did think that was causing my problem trying to call from the expression, but wasn't sure if that was the case.

TableName is also an expression, a concatenated string of "SNS" & [Rainfall] which means the dlookup needs to look at either of the 3 tables which start SNS..... which seems to work.

So from what you are saying I probably need to make another query or table with the result from the 1st DLookup to create the 2nd.

I thought it would be fairly easy to do all this table cross referencing in access as all of this data is from an excel spreasheet where I have been using VLookup to get the results, but access doesn't seem as capable/easy to do this as I was anticipating!