• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 523
  • Last Modified:

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

0
dizzycat
Asked:
dizzycat
  • 4
  • 2
  • 2
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 = ? ",
     
0
 
HainKurtSr. System AnalystCommented:
run a query to get the max

select max(TopRecord) as NewInvoiceNumber from InvoiceDetails
0
 
dizzycatAuthor Commented:
I have removed the where clause and I am still getting 9 as the MAX record number when it should be 10
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What is the Data Type of TopRecord?  If ... it's Text as opposed to Numeric, that might be the problem.

mx
0
 
Aaron TomoskyTechnology ConsultantCommented:
It should be int. It's probably char or varchar.
0
 
HainKurtSr. System AnalystCommented:
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)
 
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I just confirmed that if ... You have a Text data type, then 9 will be returned instead of 10 ... using Max()

So, pretty sure that is your issue.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
DMax("[RecordNumber]","Table5")  Returns 9 ... from that table in the image:


Capture1.gif
0
 
dizzycatAuthor Commented:
With the select clause removed and the data-type changed to integer instead of number, it works.
Thanks for your help.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now