using LIKE

[Alex]

I have a "text" field with the data such as 02, 02A, 03,
03A, 123, 123B. It can be only two or three digits number
with or without A or B.
Table1
NumberOf Qty
02 100
02A 150
03 20
03B 80
123 50
123A 100

I need to calculate the sum of the qty for the rows such
as 02 and 02A, 123 and 123B and so on. The charachter A or
B shoudn't be taken into consideration.
I think I should use LIKE but cannot figure out how to
handle with two and three numbers. (If it was only two or
only three numbers it would be easier.)

Could anybody help me with this?

Thanks

 

[Ken Snell [MVP]]

You could use Val function to "drop" off the letters and then use this
calculated field as the GROUP BY field:

JustTheNumbers: Val([NumberOf])

Then the query might be this:

SELECT Val([NumberOf]) AS JustTheNumbers, Sum([Qty])
FROM TableName
GROUP BY Val([NumberOf]);

 

[Kevin Sprinkel]

I think a better approach is to write a custom function
that strips a letter off, then using a Totals query
Grouped On a calculated field using the function, and
summing quantity.

Public Function StripLetter(strBase As String) As String
If Not IsNumeric(Right(strBase, 1)) Then
StripLetter = Left(strBase, Len(strBase) - 1)
Else
StripLetter = strBase
End If
End Function

To use in a query, type something like:

NumericOnly: StripLetter([yourdatafieldname]) in the

Name: row.

To calculate totals of quantity for each value of the
calculated field, select View, Totals, then GroupOn
NumericOnly, and Sum quantity.

HTH
Kevin Sprinkel

 

[Kevin Sprinkel]

Yes; use the built-in VAL function as Ken suggests--it's
free!

 

[Alex]

Thank you very much, guys.