[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 739
  • Last Modified:

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 : )
0
grmcra
Asked:
grmcra
  • 2
2 Solutions
 
mbizupCommented:
You can use DMAx:

Constant = dmax("yourdatefield" , "yourtable")
0
 
mbizupCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now