MS Access SQL Max Spit text value

Posted on 2011-04-29
Last Modified: 2012-05-11
I have table with a field named "QuoteNumber" set as text to account for values such 128-1.

When adding a new record, I need to get the max QuoteNumber.  I'm using the following query but I'm getting a "Type mismatch" error.  I'm assuming it's because I'm attempting to get the Max value of a text variable. Here is the query string I'm using.  How do I get around this?

"SELECT Max(Split(QuoteNumber, " - ")(0)) FROM MFGMain"

I also tried:
strQuery = "SELECT Max(CInt((Split(QuoteNumber, " - ")(0)))) FROM MFGMain"

I get the same error for both.

Question by:AronMcD
    LVL 9

    Accepted Solution

    I believe Split returns an array ... hence the type mismatch ... you cannot convert an array to an Int.

    Use something like "iif(instr(1, QuoteNumber, "-") > 0, Left(QuoteNumber, InStr(1, QuoteNumber, "-")-1), QuoteNumber)

    The above IIf statement just does this

    if there is a "-" in the QuoteNumber then
      give me the left part of the quote number calculate the length from where the "-" occurs
      give me the quote number


    Author Comment

    I still get the "Type mismatch" error with this.

    "SELECT Max(Cint(iif(instr(1, QuoteNumber, " - ")>0, Left(QuoteNumber, InStr(1, QuoteNumber, " - ")-1), QuoteNumber)))"


    Author Comment

    Correction on the above.  I forgot the FROM MFGMain.  Still getting the same error.

    "SELECT Max(CInt(iif(InStr(1, QuoteNumber, " - ")>0, Left(QuoteNumber, InStr(1, QuoteNumber, " - ")-1), QuoteNumber))) FROM MFGMain"

    Author Comment

    I took the Max(CInt) part out and I'm still getting the "Type mismatch" error.

    "SELECT iif(InStr(1, QuoteNumber, " - ")>0, Left(QuoteNumber, InStr(1, QuoteNumber, " - ")-1), QuoteNumber) FROM MFGMain"

    Author Comment

    Ok.  It's working now.  I changed the double quotes to single quotes.  

    Here is what I used.

    Set rs = CurrentDb.OpenRecordset("SELECT Max(CInt(iif(InStr(1, QuoteNumber, '-')>0, Left(QuoteNumber, InStr(1, QuoteNumber, '-')-1), QuoteNumber))) AS QuoteNum FROM MFGMain_Orig")

     Thanks sshah!

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Pl/SQL Query 31 41
    Excel 2010 question 3 28
    dcount multiple criteria 19 25
    Query question 4 16
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now