We help IT Professionals succeed at work.

MS Access SQL Max Spit text value

Medium Priority
372 Views
Last Modified: 2012-05-11
Hello,
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.

Thanks!
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Commented:
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

Commented:
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

Commented:
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!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.