Finding ABC

[hawkesms]

Hi

I hope someone can help with this as I can't seem to get this query to work.

I am trying to use a query to search for the highest value in a column but based upon a specific criteria.

i.e. in the column will be ABC1234, ABC1235, ABC1236 and BBC1234 and I want to get the highes 'ABC' value

I looked on the forum and found this code

Dim strSQL As String
Dim MyValue As DataType 'Replace the proper datatype of field
Dim rs As ADODB.Recordset, Cn As ADODB.Connection
Set rs = New ADODB.Recordset
Set Cn = New ADODB.Connection
Set Cn = CurrentProject.Connection
strSQL = "SELECT tableName.ColumnName FROM tableName ORDER BY tableName.ColumnName DESC"
rs.Open strSQL, Cn, adOpenStatic, adLockReadOnly
MyValue = rs!ColumnName
rs.Close
Set rs = Nothing

I've modified the query to this

strSQL = "SELECT tableName.ColumnName FROM tableName WHERE tableName.ColumnName Like 'ABC*' ORDER BY tableName.ColumnName DESC"

This doesn't bring anything back but I was expecting 'ABC1236'

I'm new to using 'Like' so this could be the problem.

Your help is much appreciated.

 

[FoFa]

try:
strSQL = "SELECT max(tableName.ColumnName) FROM tableName WHERE tableName.ColumnName Like 'ABC*'"
If you only want ONE value returned

 

[hawkesms]

Hi

Thanks for the tip but unfortunately that didn't work either. I don't know if this is a problem but the format of the information is either:

ABC 1234
ABC 1235
different4
ABC 1236
different5

the original code meant MyValue = "different5" but I need "ABC 1236" or higher when a higher value is entered e.g. ABC 9999

I'm actually looking to get the value out of ABC i.e. just '1236' etc. Could this be done in the query also?

Sorry to be so dumb.

Many thanks!

 

[FoFa]

maybe I am not understanding. You want the highest value of the column where the value of that column starts with ABC? So ignore the different5 etc.?
Also VAL([columnName]) will return the numeric value of a column/string. So VAL("ABC 1236") should return 1236.
The example I gave you should have returned ABC1236 (using your example) as the largest value. How ever if these values you are using are not representative of the values on your table, that maybe the issue also.

 

[raskew]

Hi -

FoFa -- are you there?

? val("ABC123") will return 0 (give it a test--probably should have done that in the first place). The Val() function only works if the leftmost character in a string/substring is numeric.

You might try this:

Public Function GetNumer2(pstr As String) As Currency
'*******************************************
'Purpose:   Returns the first numerical
'           sequence in a string
'Coded by:  raskew
'Inputs:    ? getNumer2("ABC123")
'Output:    123
'*******************************************
Dim n       As Integer
Dim strHold As String
Dim strKeep As String

   strHold = Trim(pstr)
   n = Len(strHold)
   Do While n > 0
      If Val(strHold) > 0 Then
         strKeep = Val(strHold)
         n = 0
      Else
         strHold = Mid(strHold, 2)
         n = n - 1
      End If
   Loop
   
   GetNumer2 = strKeep
        
End Function

HTH - Bob

 

[hawkesms]

Hi FoFa and Raskew

Thanks for responding to my cry for help!

FoFa - the values are representative of what is in the column but to be completely accurate its for a quotes database. Therefore it will read as:

SSQ 1234
SSQ 1235
verbal4
SSQ 1236
verbal5

(Please note that there is a space between SSQ and 1234 etc. SSQ is a prefix we add to the number manually)

I am trying to get the query to only find the highest value against an SSQ i.e. in this example I would get 1236 because of SSQ 1236.

But, in the code origianlly shown I get verbal5 as the highest value.

The overall outcome I am looking for is to automatically increment the SSQ number e.g. from 1236 to 1237 when a new quote is added.(Basically to save time for those putting the information in.)

Unfortunately there is no way I can remove any of the "verbal's" so this is why I'm trying to use a query to find the vaule.

As soon as I try to modify the query to the one I tried or the one put forward I get nothing returned. i.e. MyValue = ""

Raskew - Thanks for the code and I will use this as soon as I can get my query to work.

Many thanks to you both.

 

[KeithG]

Is this what you are trying to do?