Help Query by date issue

[deresistance]

Hello everyone first off I am a noob with access

So heres my issue.... I have a table with the following clientname| accesscode | datetochange

The client name will have several entries lets say 1 for each month in the past and into the future. I want a query to show only a single code with the most recent date but NOT in the future.

In access I setup a query with the MAX parameter then did a <=date() as a Criteria. This returned nothing. I assume it grabs the max date THEN trys to find a date thats less then or equal todays date. Since I have codes that are in the future it will never return anything.

So how can I make a query to fetch the most recent code NOT in the future? Thanks in advance

 

[Beetle]

You're on the right track but you need to add the DateToChange field to the query a second time, use Where in the clause for the Total row, uncheck the Show box and put your criteria in that column.

 

[deresistance]

Ok that worked perfectly =) My issue is that if I put the "accesscode" field in that group it then returns past codes. For the query all I care about is the the date and client name. But for a form I want to return that code with the client name and date. Thoughts?

 

[Beetle]

1) Create another query (not a totals query - just a select query) based on the Totals query and join the original table (in other words, add the Totals query and the original table to the design grid and create a join between them). Retrieve the AccessCode field from the original table, then base your form on this new query.

2) Place an unbound text box on your form and use DLookup in its Control Source to return the AccessCode, using the ClientName and the DateToChange values (from the other text boxes on the form) in the criteria of the DLookup.

 

[deresistance]

Beetle you rocks thanks for the help. Took me a bit to figure out but i get it. That helps a ton the way you explained it. Trying to learn by direction rather then having some show me. Like all database/programing stuff I want to fully understand the logic. If I could send you a beer I would. take care thanks again!

 

[Beetle]

Glad I could help. I'll have a beer anyway and pretend like you sent it to me.