Results 1 to 4 of 4
  1. #1
    cdominguez is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    3

    Finding highest value in a text field

    Hi all,
    I have a field containing order numbers, I had to make it a text field to allow for orders that have multiple job#'s which the company likes to add a letter at the end 1234A, 1234B, 1234C. The problem I'm having now is that I need to have the highest # to assign it to the next order created. I have the following.

    9587


    9588
    50501
    50502
    50503

    My query using max thinks that 9588 is the highest number since its a text field. How can I get a maketable query to look at my field which is called jobno, find the highest value, and create a table with only that jobno which should be 50503. Thanks a million.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Unless you will be using a number in some sort of calculation, it is almost *always* better that it is a text field. Format([YourField],"00000") should do the trick for you.

  3. #3
    cdominguez is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    3
    Thank you very much for your help.
    Let me ask you something else while I'm here. My database has been setup for multiple users to enter work orders in, my problem is with the scrolling wheel in the mouse. I have checked several macros and codes from other users with no luck, I finally found one that works(kind of). When the form opens up it loads the code (OnLoad)and the scroll wheel is disabled, the only problem I have is that if a user uses one of the check boxes in the form the scroll wheel is activated again allowing a user to scroll and could possibly endup in another order by accident. If the user clicks away from the checkbox taking the focus away from it by clicking in any other field, the scroll wheel is disabled again. How can I make this process stay disabled "always". here is the code I got from another user online. Thank you so much for your help by the way.
    ************************************************** *
    Option Compare Database
    Option Explicit
    Private Declare Function LoadLibrary Lib "kernel32" _
    Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Long
    Private Declare Function FreeLibrary Lib "kernel32" _
    (ByVal hLibModule As Long) As Long
    Private Declare Function StopMouseWheel Lib "MouseHook" _
    (ByVal hWnd As Long, ByVal AccessThreadID As Long, Optional ByVal blIsGlobal As Boolean = False) As Boolean
    Private Declare Function StartMouseWheel Lib "MouseHook" _
    (ByVal hWnd As Long) As Boolean
    Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long
    ' Instance returned from LoadLibrary call
    Private hLib As Long

    Public Function MouseWheelON() As Boolean
    MouseWheelON = StartMouseWheel(Application.hWndAccessApp)
    If hLib <> 0 Then
    hLib = FreeLibrary(hLib)
    End If
    End Function
    Public Function MouseWheelOFF(Optional GlobalHook As Boolean = False) As Boolean
    Dim s As String
    Dim blRet As Boolean
    Dim AccessThreadID As Long
    On Error Resume Next
    ' Our error string
    s = "Sorry...cannot find the MouseHook.dll file" & vbCrLf
    s = s & "Please copy the MouseHook.dll file to your Windows System folder or into the same folder as this Access MDB."
    ' OK Try to load the DLL assuming it is in the Window System folder
    hLib = LoadLibrary("MouseHook.dll")
    If hLib = 0 Then
    ' See if the DLL is in the same folder as this MDB
    ' CurrentDB works with both A97 and A2K or higher
    hLib = LoadLibrary(CurrentDBDir() & "MouseHook.dll")
    If hLib = 0 Then
    MsgBox s, vbOKOnly, "MISSING MOUSEHOOK.dll FILE"
    MouseWheelOFF = False
    Exit Function
    End If
    End If
    ' Get the ID for this thread
    AccessThreadID = GetCurrentThreadId()
    ' Call our MouseHook function in the MouseHook dll.
    ' Please not the Optional GlobalHook BOOLEAN parameter
    ' Several developers asked for the MouseHook to be able to work with
    ' multiple instances of Access. In order to accomodate this request I
    ' have modified the function to allow the caller to
    ' specify a thread specific(this current instance of Access only) or
    ' a global(all applications) MouseWheel Hook.
    ' Only use the GlobalHook if you will be running multiple instances of Access!
    MouseWheelOFF = StopMouseWheel(Application.hWndAccessApp, AccessThreadID, GlobalHook)
    End Function

    '******************** Code Begin ****************
    'Code courtesy of
    'Terry Kreft & Ken Getz
    '
    Function CurrentDBDir() As String
    Dim strDBPath As String
    Dim strDBFile As String
    strDBPath = CurrentDb.Name
    strDBFile = Dir(strDBPath)
    CurrentDBDir = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))
    End Function
    '******************** Code End ****************

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922

Please reply to this thread with any new information or opinions.

Similar Threads

  1. query to find the highest value in a field
    By jhjarvie in forum Queries
    Replies: 0
    Last Post: 09-02-2008, 02:27 PM
  2. Split text field into two text fields
    By Grant in forum Access
    Replies: 6
    Last Post: 01-31-2008, 05:52 AM
  3. Quick way to stuff field names into text field
    By kfinpgh in forum Programming
    Replies: 0
    Last Post: 01-04-2007, 01:13 PM
  4. Validation rule for a text field
    By wasim_sono in forum Forms
    Replies: 4
    Last Post: 03-14-2006, 11:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums