display max date in form textbox, from unrelated table (1 Viewer)

[wiklendt]

hi everyone.

i have a form. i would like to return the max date from, a field in a table which is not bound to anything on that form, into a form textbox control on the form.

googling around (and searching this forum), i've only found threads where people are wanting to get max values from subforms, and a few obscure date things that aren't really related to my issue.

i've tried:

=DMax(DateModified,"tblRLBtargets")

and

=DLOOKUP(DateModified,"tblRLBtargets",Max(tblRLBtargets.DateModified))

in the control itself but both return either #error or #name? or "compile error: expected expression" other colourful variation on an error.

i've also has a stab in the dark with SQL in VBA...

Dim strSQL As String

strSQL = "SELECT Max(tblRLBtargets.DateModified) AS MaxDate FROM tblRLBtargets;"

DoCmd.RunSQL strSQL

Me.txtLastModified.Value =  '?? but how to get result into here ??

also, i copy/pasted the max sql from a query i made to generate the SQL (running the query returned the expected result), however, when i put the following into the immediate window

?SELECT Max(tblRLBtargets.DateModified) AS MaxDate FROM tblRLBtargets;

it gives me

compile error: expected expression

... so i don't know where to take it from here. any suggestions?

 

[vbaInet]

Here you go Agniesszka,

=DMax([COLOR=Red][B]"[/B][/COLOR]DateModified[COLOR=Red][B]"[/B][/COLOR],"tblRLBtargets")

You just needed to surround that field in quotes. Every argument in an aggregate function must be surrounded in quotes.

Here's a link on the function:

http://www.techonthenet.com/access/functions/domain/dmax.php

By the way, you can't use an sql statement like that in the immediate window.

 

[wiklendt]

Here you go Agniesszka,

=DMax([COLOR=Red][B]"[/B][/COLOR]DateModified[COLOR=Red][B]"[/B][/COLOR],"tblRLBtargets")

You just needed to surround that field in quotes. Every argument in an aggregate function must be surrounded in quotes.

Here's a link on the function:

http://www.techonthenet.com/access/functions/domain/dmax.php

By the way, you can't use an sql statement like that in the immediate window.

that did it, thank you vbaInet.

useful to know about that limitation in the immediate window...

 

[vbaInet]

You're welcome.

It's not really a limitation because a SELECT query returns a dataset and you need a view to display those records. This is where a query comes in because a query can interpret the records and present them in a view (per say), the view being a datasheet.

If you use the DMax() function in the immediate window it will return something, try it:
?DMax("DateModified","tblRLBtargets")

 

[wiklendt]

You're welcome.

It's not really a limitation because a SELECT query returns a dataset and you need a view to display those records. This is where a query comes in because a query can interpret the records and present them in a view (per say), the view being a datasheet.

If you use the DMax() function in the immediate window it will return something, try it:
?DMax("DateModified","tblRLBtargets")

ah, yes. that seems to be an important distinction.

as for Dmax in the immediate window - yes, it worked. not only returned something, but the right thing!

 

[vbaInet]

It always does

The immediate window is quite powerful, you can use it perform complex calculations that my brain can't comprehend with like:
?1 + 1


But on a serious note, it's useful for testing (global) functions, aggregate functions, calculations etc. Or for even printing a result from your routine Debug.Print

Happy coding.

 

[wiklendt]

It always does

The immediate window is quite powerful, you can use it perform complex calculations that my brain can't comprehend with like:
?1 + 1


But on a serious note, it's useful for testing (global) functions, aggregate functions, calculations etc. Or for even printing a result from your routine Debug.Print

Happy coding.

yes, i've used it lots before. comes in really handy, especially if you don't want to run a whole routine or refresh a form just to get something. it also helped i figuring out the discrepency between access and excel in executing a mod calculation.