MAX SQL for Alpha character

  • Thread starter Thread starter Rob Thompson
  • Start date Start date
R

Rob Thompson

Hello all,

I'm using MS Access and I need to formulate an SQL statement to query
the MAX alpha character in a text field.
I've tried:

SELECT * FROM tblTable WHERE DwgRev = _
(SELECT MAX(DWGREV))

also

SELECT * FROM tblTable WHERE DwgRev = _
(SELECT CHAR(MAX(ASCII(DWGREV))))

neither will work

Any ideas?

Thanks,
Rob

Example of table:

Given:
Table with 3 fields: (all field types are "text")

DwgNo DwgRev DwgTitle

12345 - Widget #1
12345 A Widget #1
12345 B Widget #1
12345 C Widget #1
23456 - Widget #2
23456 A Widget #2
34567 - Widget #3


Needed:
An SQL query that will return: The latest revision of each drawing.
Where "-" is the initial revision and A, B, C... are subsequent
revisions.

DwgNo DwgRev DwgTitle

12345 C Widget #1
23456 A Widget #2
34567 - Widget #3
 
Hello all,

I'm using MS Access and I need to formulate an SQL statement to query
the MAX alpha character in a text field.
I've tried:

SELECT * FROM tblTable WHERE DwgRev = _
(SELECT MAX(DWGREV))
Click to expand...

Well, this won't work because your Subquery doesn't specify what table
you want to select *FROM*. It seems from your example that you want to
select the Max value of DWGREV from tblTable for each DwgNo; you need
to specify this in your Subquery. The Subquery within the parentheses
is a full Query which should return just one record and one field:

SELECT * FROM tblTable
WHERE DwgRev = (SELECT Max(DwgRev) FROM tblTable AS X
WHERE X.DwgNo = tblTable.DwgNo);

This subquery will search tblTable, under the alias X, for the largest
DwgRev for the current record's DwgNo.
 
Thanks John,
I tried what you recommended and was unsuccessful. I am somewhat green
at this.
What I'm doing is creating the SQL statement on the fly using visual
basic.

Here is the statement that was created when I tried your suggestion
and the snippet of code that created the statement.

SELECT * FROM XATAP_Query WHERE SERIALNO LIKE '*' AND PARTNO LIKE '*'
AND PARTDESC LIKE '*' AND SYSTEMNO LIKE '*' AND ACFTTYPE LIKE '*' AND
FileName LIKE '*' AND DWGNO LIKE '*' AND DWGREV = (SELECT Max(DwgRev)
FROM XATAP_Query AS X WHERE X.DWGREV = XATAP_Query.DWGREV) AND CAGENO
LIKE '*' AND DWNDATE LIKE '*' AND QTY LIKE '*' AND DWGTITLE LIKE '*'
AND ENGINEER LIKE '*' AND FullName LIKE '*'

the code snippet:
**********************
'generate the SQL statement
strTmp = "SELECT * FROM " & DBQueryTableName
For i = 0 To 13
If Trim(DBCombo1(i).BoundText) = "" Then
strCmbo(i) = ""
strExact(i) = "LIKE '*"
Else
If Check1(i).Value = 1 Then
strExact(i) = "= '"
strCmbo(i) = DBCombo1(i).BoundText
Else
strExact(i) = "LIKE '*"
strCmbo(i) = DBCombo1(i).BoundText & "*"
End If
End If
If i > 0 Then
If i <> 7 Then
strSql(i) = " AND " & strField(i) & " " & strExact(i) & _
strCmbo(i) & "'"
Else 'field 7 is the drawing revision field
If Trim(DBCombo1(i).BoundText) = "" Then
strSql(i) = " AND " & strField(i) & " = _
(SELECT Max(DwgRev) FROM " & DBQueryTableName & " AS X WHERE X.DWGREV
= " & DBQueryTableName & ".DWGREV)"
Else
strSql(i) = " AND " & strField(i) & " " _
& strExact(i) & strCmbo(i) & "'"
End If
End If
Else
strSql(i) = " WHERE " & strField(i) & " " & strExact(i) &
_
strCmbo(i) & "'"
End If
strTmp = strTmp & strSql(i)
Next i

Debug.Print strTmp
'set the data source for the grid
Set Data1.Recordset = dbsObj.OpenRecordset(strTmp, dbOpenDynaset)
***************************
I thought that I would need to use CHR(MAX(ASC(field))) to return the
max alpha character.
Any additional help is appreciated.
 
I thought that I would need to use CHR(MAX(ASC(field))) to return the
max alpha character.
Click to expand...

ummmm... no. Max([field]) will select the largest value for the
sorting appropriate to that field - numeric for number fields,
alphabetic for text fields (i.e. the Max() of "a", "x", "z" is "z").

I'd STRONGLY suggest revising your code, though; all of these LIKE "*"
criteria will be a HUGE drag on performance. If you have a null search
criterion, just don't include that field in the WHERE clause at all!
 
strSql(i) = " AND " & strField(i) & " = _
(SELECT Max(DwgRev) FROM " & DBQueryTableName & " AS X WHERE X.DWGREV
= " & DBQueryTableName & ".DWGREV)"
Click to expand...

ah. Also, you are searching for the largest value of dwgrev among the
current value of dwgrev - they will always be the same! The criterion
in the subquery should be the *drawing number*, not the *revision
number*.
 
John,
disregard my last post. I had used the wrong field.

this works!
strSql(i) = " AND " & strField(i) & " = (SELECT Max(DWGREV) FROM " &
DBQueryTableName & " AS X WHERE X.DWGNO = " & DBQueryTableName &
".DWGNO)"

Thanks again,
Robert
 
Back
Top