Problem with Select MAX query

[Leo Hamal]

Hello I am a newbie in Access 97.

When I run the query :
SELECT DISTINCTROW MAX (TbTrans1.Date)
FROM TbTrans1
WHERE (((TbTrans1.Date) BETWEEN #01-jan-07# AND #31-jan-07#);
I get the correct date.

When I run the query :
SELECT DISTINCTROW MAX (TbTrans1.Date), (TbTrans1.NewBal)
FROM TbTrans1
WHERE (((TbTrans1.Date) BETWEEN #01-jan-07# AND #31-jan-07#);

I get error message : You tried to execute query that doesn't include the
specified expression 'NewBal' as part of an aggregate function.

When I take out MAX , I do get the full list of all transactions of January
correctly.

Any help would be much appreciated,
Leo.

 

[Allen Browne]

In query design, depress the Total button on the toolbar.
Access adds a Total row to the grid, and puts Group By under NewBal.

If you prefer to work with the SQL statement, add:
GROUP BY TbTrans1.NewBal

 

[Leo Hamal]

Allen,

when I add this I get the full list of all transactions of the month
correctly.
But, probably this was not clear in my initial message, I only need the
data for the last date of the month.
That is why I am experimenting with the MAX.

Thanks,
Leo.

 

[Allen Browne]

If you only want the last date for the month, which record do you expect to
be inluded for the NewBal?

If you want the newbal for that date, see:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
for 4 ways to approach this.

 

[Leo Hamal]

Thanks a lot Allen,
This is exactly what I needed,
Leo.