Sum similar invoice numbers.

misternumbertwo

Registered User.
Local time
, 07:26
Joined
Dec 29, 2008
Messages
11
Hi everyone,

I'm currently working on a database that handles invoices. In a certain month, around 25,000 invoices are processed. Some of them are repeating but with different amounts that I need to get the total.

It shouldn't be a problem right? I just need to do a summation and group it by invoice numbers. However, here's my problem.

191992 - $20
191992A - $62
191992B - $32
191992C - $12
191992D - $55

The query should produce the sum of the invoices above as well. The suffix letter should be disregarded.

Can anyone figure out how i can do this? If i remove every record's last character, the first record in my example would not be counted. I also can't do a len() function because the invoice numbers vary in length.
 
You should be able to test if the last charactor is an alpha, if so drop it. I'd do it in a function...

Edit:

Code:
Public Function fncMasterInvoiceNumber(strInv As String) As String

    If Asc(Right(strInv, 1)) < 48 Or Asc(Right(strInv, 1)) > 57 Then
        fncMasterInvoiceNumber = Left(strInv, Len(strInv) - 1)
    Else
        fncMasterInvoiceNumber = strInv
    End If

End Function
 
Last edited:

Users who are viewing this thread

Back
Top Bottom