Query Text Help

[tmcrouse]

I have a table that has the following information:

ID templatename countoftemplate
1 bill001 45
2 cob001 12
3 recon001 17
4 bill002 19


5 bill005 20
6 recon002 22

Etc...The templatename has various character lengths but the numbers are always the same. I can't do a right or left or mid and can't find a query to just get the text. I don't want the numbers. The reason is because I want to groupby templatename without the numbers. I have to do a chart that just shows all templates that are bill. All templates that are recon....cob...info...etc. The table is huge because of the letters we send out so manually doing this would be way too much. There has to be a query that you can extract characters or letters only and vice versa with extract numeric only besides doing the right, left or mid stuff. Thanks!!!!

[dcrake]

You will need a function to do that:

 
Public Function ExtractTextPart(AnyString As String) As String
 
Dim i As Integer
Dim tmpStr As String
For i = 1 To Len(AnyString)
   If IsNumeric(Mid(AnyString,i,1)) Then
      tmpStr = Left(AnyString,i-1)
      Exit For
   End If
Next i
 
ExtractTextPart = tmpStr
 
End Function

Next Create a new query and in the first column enter

TextOnly:ExtractTextPart([TemplateName])

This will then only list the characters upto the first encounter of a numeric digit. What you have to be careful of is that if the template has a name like Bill2Me0001 you will only get Bill. Not Bill2Me.

David

[tmcrouse]

This doesn't work because I am using Access not VB. So it is in either design mode or the SQL query mode. I don't have time to wait anymore for a response and will just groupby my template names and just manually remove the numbers cuz this report is due by end of day tomm and I am behind.

[tmcrouse]

Well the only way I can think of to do this is this SQL query:

select aug08aug09prv.id, mid([templatename],1,5) as t, sum(aug08aug09prov.sumofcountoftemplatename) as sumofsumofcountoftemplatename
from aug08aug09prov
group by aug08aug09prov.id, mid([templatename],1,5)
order by mid([templatename],1,5);

[dcrake]

Excuse me, But the solution I gave you was totally accurate for Access. For a start you would need to create a standard module and then within that module you would save the function I provided.

Then If you follow the instructions given you would have go the desired results.

I suggest you do not jump to conclusions and expect immediate replies. What is provided on the forum is free. If your deadlines are so tight I would have suggested you asked the question earlier.

David