Expressions and Functions in Query Not Working

[ItsRoland]

I'm trying to build a query that contains an overview of the rest of the database, as in has all the important information in it.



I need to display the max value for "impact" in the table "tImpact". I set up an expression as follows:

DMax([tImpact].[impact], [tImpact], [tImpact].[scenarioID] = [tScenarios].[scenarioID])

Please note that [tScenarios].[scenarioID] is also in the query. For some reason, when I go to datasheet view, it prompts me for a parameter for [tImpact]. This expression is the only place that the table [tImpact] appears in the query.

I'm also using a function to get a value for the query. The function calculates how much the Impact is reduced, which I already know is calculating properly, as its being used in a few other places as well. The final result is stored in a variable, "Ireduc", and I then set that to the function name.

'Calculations...

impactReduction = Ireduc

Exit Function

End Function

I have another function giving values to the query that's set up the same way, and that one is working properly, but this one is displaying a 0 for every record. If it makes a difference, the working functions returns a string, and the one in question returns a double.

Let me know if any additional information is needed.

Any help is greatly appreciated!

[Minty]

Your syntax is wrong have a look here http://access.mvps.org/access/general/gen0018.htm

Something like

DMax("impact", "tImpact", "[scenarioID] = [tScenarios].[scenarioID]")

However as this is in a table you can join to you should really use a join or a sub-query, as it's a lot more efficient.

[ItsRoland]

Ah, didn't realize I needed quotes when using it in a query expression.


After making the change, it's now telling me it couldn't find the name: 'tScenarios.scenarioID'. I've checked to make sure it's spelled correctly, so I can confirm that is not the issue.

[Minty]

So have you included tScenarios in your query? It will have to be for the query to use it's value.

[ItsRoland]

Yes, the table being referenced is included in the query.

[Minty]

You may need to concatenate the string - I hardly ever use Domain functions in queries try;

DMax("impact", "tImpact", "[scenarioID] = " & [tScenarios].[scenarioID] & "")

This assumes ScenarioID is a number field , if not add single quotes around it ;

DMax("impact", "tImpact", "[scenarioID] = '" & [tScenarios].[scenarioID] & "'")

[ItsRoland]

That fixed the issue with the expression, thank you!


Now I just need to figure out why the function isn't working properly.