using queries/vba or just native access code in the source control field

[bisqeet]

Hi I'm having a few problems with the correct syntax for one or another functions.

basically the db is reading training data in from a logcard, cleaning up the data and writing statistics based on that logbook to a form.

one of the fields provided that statistic for the fastest 5km rowing time

=DomMin("TrgDuration";"tab_logcard";"TrgDistance = 5000")

now the problem comes when i try to return the date for the same value:

The SQL for that could work as

SELECT tab_Logcard.TrgDate
FROM tab_Logcard
WHERE (((tab_Logcard.TrgDuration)=DMin("TrgDuration","tab_logcard","TrgDistance =5000")));

I thought that would translate as:

dlookup(trgDate; tab_LogCard;DomMin("TrgDuration";"tab_logcard";"TrgDistance = 5000"))

but its just returning a #NAME msg.

is the criteria too complex
what would be another solution ?

note:


german syntax changes dmin/dommin

[ranman256]

Nomally, the sql usage is: MIN(field)
not DMIN. (nor German DOMMIN)

In query design, Just choose the item in the grid field for Totals, you cant pick the wrong one, unless you type it in manually.

[bisqeet]

thanks, but it didnt help me.
Min, Max don't allow for compley where clause.

the dmin allows me to be more accurat with my where clause:

DMin("<Field Name>", "<Table Name>", "<Where Clause>")

i'm not sure why i would want to pick a total - i want the date where the fastest time was recorded.



basically

=DomWert("TrgDate";"tab_logcard";"trgDuration ='xx:yy.z'")

will give me a specific date back from the time 'xx:yy:zz

where xx:yy.z = =DomMin("TrgDuration";"tab_logcard";"TrgDistance = 10000")


as stated the SQL returns the correct value - i just wanted to know how syntax the function correctly in the control source field