asked on

Access MAX function only finds single digit numbers

Hi experts
Using vb.net 2008 i am trying to select the highest record number in a table in my access database, the problem is that the MAX function only finds single digit record numbers when they are the highest record number, when the record number reaches 10 and I run the code again the MAX function only finds number 9 as the highest record number, any ideas anyone ?

Dim dbadp As New System.Data.OleDb.OleDbDataAdapter("SELECT MAX(RecordNumber) as TopRecord FROM InvoiceDetails where Invoicedetails.ID = ? ", "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = C:\myDB.accdb")
        Dim TopRecord As Integer

        dbadp.SelectCommand.Parameters.AddWithValue("InvoiceDetails.ID", txtSearchInvoice.Text) 
        


        dbadp.Fill(ds, "InvoiceDetails")

        For Each myDataRow In ds.Tables("InvoiceDetails").Rows
            

            TopRecord = myDataRow.Item("TopRecord")

            

        Next
        txtRecordNo.Text = (TopRecord + 1)

Open in new window

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Well, Max() does not have any such limitation, so ... something else is going on, possibly with the criteria. As a test, remove the WHERE clause and see what you get.

"SELECT MAX(RecordNumber) as TopRecord FROM InvoiceDetails where Invoicedetails.ID = ? ",

HainKurt

run a query to get the max

select max(TopRecord) as NewInvoiceNumber from InvoiceDetails

dizzycat

ASKER

I have removed the where clause and I am still getting 9 as the MAX record number when it should be 10

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

What is the Data Type of TopRecord? If ... it's Text as opposed to Numeric, that might be the problem.

mx

Aaron Tomosky

It should be int. It's probably char or varchar.

HainKurt

look at your code

dbadp.Fill(ds, "InvoiceDetails")
For Each myDataRow In ds.Tables("InvoiceDetails").Rows
TopRecord = myDataRow.Item("TopRecord")
Next

it gets the last TopRecord value, where as you want max right?

so try this:

dbadp.Fill(ds, "InvoiceDetails")
TopRecord=0
For Each myDataRow In ds.Tables("InvoiceDetails").Rows
TopRecord = iif(TopRecord < myDataRow.Item("TopRecord"), myDataRow.Item("TopRecord"), TopRecord)
Next
txtRecordNo.Text = (TopRecord + 1)

ASKER CERTIFIED SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

DMax("[RecordNumber]","Table5") Returns 9 ... from that table in the image:

Capture1.gif

dizzycat

ASKER

With the select clause removed and the data-type changed to integer instead of number, it works.
Thanks for your help.