MAX Value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that had 30 records but now has only 1. When I try to identify the 'Max' value of the primary key in the table the recordset returns 30 even though when I check the table there is only one. Oddly enough, when I create a query in Access to return the 'Max' value it returns the correct result?! The code I'm using is

SELECT Max(MyTable.MyField) AS MaxOfMyField FROM MyTabl

The primary key type is 'Number
 
Dear David:

Why not open the datasheed and look at the data. What is the value of
MyField in the one and only row you see? I'll bet it is 30. So, what
did you expect. You may have had rows with 1 to 29, but you deleted
them. This is the one that remains.

Please explain what you were expecting.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom

When I open the query in datasheet mode it displays 1. This is the bizarre thing, the table no longer has 30 records, it has one record and that one record has its MyField's value as 1. Even if I delete all records in the table it still returns 30.
 
Dear David:

I was suggesting you open the TABLE in datasheet view, not the query.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Dear David:

When you open the table in datasheet view, only 1 row is displayed,
and the value in the column MyField is 1. Is that correct?

Then, when you run this query:

SELECT Max(MyTable.MyField) AS MaxOfMyField FROM MyTable

it returns 1 row with the value 20 in the column MaxOfMyField. Is
that correct?

Sorry, I just want to be quite certain of our communications.

If the answer to both questions is 'yes' then you have a serious
problem. Either there is some obvious bug in Access which no one else
has uncovered, there's something badly wrong with the install of
Access on your system, or the database has some nasty corruption
(possibly an index, which you should drop and recreate). Can you try
the same thing on another system to eliminate the install question?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom

Yes that is correct, however, if the query is run using the Access query UI it returns the correct value. It is only when the query is run from code that the error occurs e.g

dim strSQL as Strin
dim rst as ADODB.Recordse

strSQL = "SELECT Max(MyTable.MyField) AS MaxOfMyField FROM MyTable
rst.Open etc....

Msgbox rst(0) or rst(0).Value - tried both
 
Dear David:

Since the error occurs in this code, please show more of the code.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top