MAX Value Using VBA

[Davidw]

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'

 

[Chris]

Are you confusing "Max" with "Count"

"Count" returns the number of records (1)
"Max" will return the highest number in "MyField".

-----Original Message-----
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:

 

[DavidW]

Hi Chris

I think I worded my question incorrectly! MyField holds an integer value that increments each time a record is added, it is also a primary key field. I added 30 records to MyTable hence MyField's MAX value at that point was 30. I then deleted all of the records in the table. I added another record using 1 as the value for MyField. Here is where the problem occurs, when I use a SQL statement in code to return the MAX value from the table I expect it to return the value 1 because there is only one record in the table and its MyField value is 1. What I get is the value 30 in the recordset object but if I copy the SQL statement and paste it into a query it returns the correct value, 1.

 

[Alp Bekisoglu]

Not even near an expert but have you done a Compact/Repair after the
deletions?

Alp

Hi Chris,

I think I worded my question incorrectly! MyField holds an integer value

that increments each time a record is added, it is also a primary key field.
I added 30 records to MyTable hence MyField's MAX value at that point was
30. I then deleted all of the records in the table. I added another record
using 1 as the value for MyField. Here is where the problem occurs, when I
use a SQL statement in code to return the MAX value from the table I expect
it to return the value 1 because there is only one record in the table and
its MyField value is 1. What I get is the value 30 in the recordset object
but if I copy the SQL statement and paste it into a query it returns the
correct value, 1.

 

[DavidW]

Hi Alp

No, I shall try that now.