MS Access SQL Max Spit text value
Posted on 2011-04-29
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.