• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

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!
0
AronMcD
Asked:
AronMcD
  • 4
1 Solution
 
sshah254Commented:
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
else
  give me the quote number

Ss
0
 
AronMcDAuthor 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)))"

0
 
AronMcDAuthor 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"
0
 
AronMcDAuthor 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"
0
 
AronMcDAuthor 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!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now