asked on

MS Access SQL Max Spit text value

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!

ASKER CERTIFIED SOLUTION

sshah254

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AronMcD

ASKER

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

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

AronMcD

ASKER

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"

AronMcD

ASKER

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"

AronMcD

ASKER

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!