Search results

Here is SQL of working query. :) SELECT QuerySNSField.Field, QuerySNSField.Ntype, NTable.SNSValue FROM QuerySNSField INNER JOIN NTable ON (QuerySNSField.Ntype = NTable.TheSoil) AND (QuerySNSField.SNSNumber = NTable.SNSField);

Hi Thanks for suggestions, I have sorted out the problem by rejigging the NTable and then using joins to get out the required value, as per your suggestion, as the Dlookup was the problem. I am also now going to look at the other tables for rejigging too. Thanks

It won't let me post the images because I have less than 10 posts.

Hi That would work apart from NTable does not have the fieldname SNSNumber. It is set out with fields 'TheSoil' '0' '1' '2' '3' '4' '5' '6' which is why I was using the Dlookup. In theory the NTable.[SNSNumber] needs to be a variable.

Hi Are you saying not to use the Dlookup in the Query? The query is only 2 tables with the relevant join which should make the Dlookup work. Thanks

Working SQL with the DLookup SELECT QueryNPSumField.Field, QueryNPSumField.SoilType, QueryNPSumField.TableCropN, QueryNPSumField.TableSNSRain, DLookUp([SoilType],'SNSRainfall',[Crop]=[TableSNSRain]) AS SNSNumber, [TableCropN] & [SoilType] AS Ntype FROM QueryNPSumField INNER JOIN SNSRainfall ON...

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...

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...

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...