• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • 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 Access MVP)Database ArchitectCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

mx
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
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 Access MVP)Database ArchitectCommented:
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 Access MVP)Database ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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