Access 2010 - How to set a constant to a query result

Hi Experts,

I have a database with a table (t_datadate).  I have a query q_DataDate_MAX which uses Max to find the most recent date.  I often have to join this query to a date field in other queries to filter for one date, however, I was hoping I could just use something that acts like a global constant under Criteria in the query grid instead.  Since the max date often changes, I know I can't define a constant as a specific date (I've found VBA examples on this), but wondering how I could have the constant equal to the result of the Max date query?  Maybe I'm using the wrong concept to try and accomplish this, so please forgive my ignorance : )
grmcraAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
You can use DMAx:

Constant = dmax("yourdatefield" , "yourtable")
0
 
mbizupConnect With a Mentor Commented:
An alternative is to use DLookup to pull the MaxDate from your existing query:

x = DLookup("YourFieldName", "q_DataDate_MAX")


See Jim Dettman's article about DLookup and the Domain functions for a thorough explanation of this topic:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html
0
 
grmcraAuthor Commented:
Works great and thanks for the resource link!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.