Open Query

[Gustavo]

Hi Everyone,
I have a query in Access 2000 with only three fields:
Year Issue Date
2003 0010 6/5/03
2003 0010 7/7/03
2003 0010 8/2/03
2003 0012 4/4/03
2003 0012 6/7/03
2003 0016 5/5/03
2003 0018 8/1/03

etc...up to 3500 records

I need to be able to read the last record's date for
every issue and take it as a value to use in a procedure.
(for example when I am dealing with issue 0012 I need to
get the date 6/7/03 as my value)
I am not sure what method I need to use to do that. I
read about the open recordset method but I don't fully
understand how it works.
I thank in advance any help that I can get
Thanks again
Gustavo

 

[JohnFol]

SELECT MyTable.Issue, Max(MyTable.Date) AS MaxOfDate
FROM MyTable
GROUP BY MyTable.Issue;

 

[Gustavo]

Thanks for the prompt reply John but what do you mean?
Where or how do I use that?
I probably did not explain myself clearly. I do not want
to create a query, I want to open the query with code and
retrieve the specific date.
Thanks again
Gustavo

 

[Van T. Dinh]

Check Access VB Help on the DMax() function.

DMax("[Date]", "YourTable", "[Issue] = 0012")

should return the max. date for issue 0012.

I assumed [Issue] is a Numeric Field. If it is Text, use:

DMax("[Date]", "YourTable", "[Issue] = '0012'")

BTW, both Year and Date are bad choices for Field Names as they are names of
inbuilt functions in VBA.

 

[Gustavo]

That works...thanks for the help and the advice Van
Gustavo

-----Original Message-----
Check Access VB Help on the DMax() function.

DMax("[Date]", "YourTable", "[Issue] = 0012")

should return the max. date for issue 0012.

I assumed [Issue] is a Numeric Field. If it is Text, use:

DMax("[Date]", "YourTable", "[Issue] = '0012'")

BTW, both Year and Date are bad choices for Field Names as they are names of
inbuilt functions in VBA.
--
HTH
Van T. Dinh
MVP (Access)

Thanks for the prompt reply John but what do you mean?
Where or how do I use that?
I probably did not explain myself clearly. I do not want
to create a query, I want to open the query with code and
retrieve the specific date.
Thanks again
Gustavo

.