MAX Value Using VBA

  • 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'
 
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.
Click to expand...
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:
 
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.
 
Not even near an expert but have you done a Compact/Repair after the
deletions?

Alp

DavidW said:
Hi Chris,

I think I worded my question incorrectly! MyField holds an integer value
Click to expand...
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.
 
Back
Top