split a field containing multiple values

[pbuethe]

One of my tables, tblRemitMaster, has a field called txtError. I would like to do a distribution of the error codes in this field. However each record can have up to 5 error codes in the txtError field. There are hundreds of possible error codes. The error codes are concatenated in a 25-digit field (each code has 5 digits). If there are less than 5 errors on the record the remaining positions are zero filled. Examples:

0017500000000000000000000
0003300061000000000000000
0006100000000000000000000

(note that the position of a particular error code can vary depending on what other errors are in the same record)

I want to have a report listing each individual error code, the number of records with that error code, and the description of the error code (table refEditTable has the error code as txtEditNbr and the description as txtEditMsg).

Thanks for any help!

 

[Mile-O]

You could make 5 queries based on the table - each one selecting a different set of characters from the error code: 1 - 5, 6 - 10, ..., 21 - 25

Then, use a UNION query to bind them all together. Not to sure about union queries though as I've never used them.


By using code you could do something to split the code, although I wouldn't know what you wanted to do with it:

i.e.

Dim intCounter As Integer
Dim strCodes() As String

For intCounter = 5 To 25 Step 5
   ReDim Preserve strCodes(intCounter / 5)
   strCodes(intCounter Mod 5) = Mid(strFullCode, intCounter - 4, 5)
Next intCounter

Sorry, I can't be of more help...

 

[pbuethe]

Thanks Mile-O-Phile, I followed your suggestions and it is working this far: I have created 5 queries each of which extracts a different 5 characters from the error code string, and a Union query which puts them all together. Then I put the query into another query, and displaying the error code expression and the count of invoice numbers, I have a distribution.

However, I can't get it to display the description. It gives me a "type mismatch in expression" when I add the refEditTable.
This is because the edit code is a number in refEditTable, and text in the query. I tried changing it to text in refEditTable, but then there were no results since as text there are no leading zeroes.

Is there a way to add leading zeroes to the refEditTable value when it is text (to make it 5 characters), or to change the expression to a number?

 

[Mile-O]

I suppose you could use the Str() function to convert values.

 

[neileg]

Came across this recently and use Value() to change the hacked up fields back to number. E.g:

FirstError: Value(Left(ErrCode,5))

 

[pbuethe]

Thanks neileg, but where would I put this?

 

[neileg]

You have created the five queries and I iassumed you have used string chopping functions like Left, Mid or Right to extract the codes. I suspect, on reflection, that you might have have used Mile's code to do this.

If you used string chopping so that you have a clause in your SQL like this:
Left([ErrCode],5) AS FirstErr
the you need to change this to
Value((Left([ErrCode],5)) AS FirstErr

The same thing in the QBE grid would look like this:
FirstErr:Left([ErrCode],5)
So this would become
FirstErr: Value(Left([ErrCode],5))

or tell me how you have done what you have done!

 

[pbuethe]

I finally figured it out using the Val() function, with some help from a co-worker. It works well now.

Thanks for your help, Mile-O-Phile and neileg.