Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2007
    Posts
    4

    DMax returning wrong value


    Hi,
    I'm using Dmax to return the next number in customer reference field. The field's data type is text. My problem is that the data currently in the table includes for example: 890, 891, 1002, [...], 2368, 2369,... and I was rather hoping that the next number returned by DMax would be 2370, it's not, it returns 892. Help! Thanks.

  2. #2
    Join Date
    Oct 2007
    Posts
    2
    Hey,

    I just tried it myself and Dmax works perfectly but the type of the field was a number and not text you should try to change the definition of the field first.

  3. #3
    Join Date
    Oct 2007
    Posts
    4
    Thanks for the reply. The thing is I need it to be left as a string. Can I change it temporarily to a number to use DMax? I'm not very clear on this. Thanks

  4. #4
    Join Date
    Oct 2007
    Posts
    2
    I'afraid that this will be the only solution if you want to use Dmax unless you can insert an autonumber field on which one you can perform the Dmax function and afterwards find the value of your field with a Dlookup function. If you are able to change the type of your field is depending on a lot of other items like relations in the database or keyfields etc...

  5. #5
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109
    Have you tried using CInt?

    i.e. DMax("Cint(fieldname)","tablename")

  6. #6
    Join Date
    Oct 2007
    Posts
    4
    I tried this:

    Code:
    Me!Booking_RefNum= Nz(Dmax(CLng("[Booking_RefNum]"),"[Booking_Table]"))+1
    I get a type mismatch error though...

  7. #7
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109
    I need to read more carefully next time. Not completely sure what you are trying to do, but would this work?

    Me!Booking_RefNum= CStr(Nz(Dmax("CLng([Booking_RefNum])","[Booking_Table]"))+1)

  8. #8
    Join Date
    Oct 2007
    Posts
    4
    Thanks for your continued help. I did try that, but it still gave me a type mismatch error. :x

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

Similar Threads

  1. What am I doing wrong?
    By brandon in forum Access
    Replies: 2
    Last Post: 08-03-2008, 10:26 AM
  2. Calling a function and returning a value
    By 3dmgirl in forum Programming
    Replies: 0
    Last Post: 04-23-2007, 02:20 PM
  3. What I am doing wrong? (caption of report)
    By swampdonkey in forum Reports
    Replies: 0
    Last Post: 12-07-2006, 11:07 AM
  4. Email created from Access is in the wrong format
    By Stick in forum Programming
    Replies: 0
    Last Post: 09-25-2006, 12:48 PM
  5. Week number wrong
    By Nikki17 in forum Queries
    Replies: 6
    Last Post: 03-18-2006, 10:01 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