Grouping by first word of field 1

[lruthig]

I'd like to group my report not by the entire value of a field, or by the first letter or letters, but by the first word. The field has plant names, in the format:
Pansy "Ultima Morpho" Petunia "Purple Wave" etc.
I need to group all the pansies together, all the petunias together, and sum the orders for each group without seeing all the variety names. Since the number of characters of the first word varies, I can't specify by that.
I could put a field in the PlantNames table that has just Pansy or Petunia, but is there another way to do it?

Thanks,
Lisa

 

[danvlas]

Paste this in a module:
'========START============
Function FirstWord(Expression As String) As String
If InStr(Expression, " &quot = 0 Then
FirstWord = Expression
Else
FirstWord = Left(Expression, InStr(Expression, " &quot - 1)
End If
End Function
'========END==============

and use it in the query:

Select FirstWord([FieldName]) As Whatever From [YourTable] Group By FirstWord([FieldName]);

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[lruthig]

Thanks for your quick response! I'm new to this, and I've been working on it for a while with little success.
I'll tell you what I'm doing, and maybe you can find my error(s):

I'm running the report from a query, qryComparison. The fields are [PlantName] [ShipDate] [NumberTrays] and [PlugSize]. PlantName is the field on which I'd like to group by first word.
I pasted your code (minus start and end) into a module, as is.
I tried using your statement within a select query, in the criteria field of PlantName. One issue I had was what to subsitute for "Whatever." Is this what I want the resultant group to be called, or is there more to it? The other parts I substituted as follows:

(Select FirstWord([PlantName])As Whatever From [qryComparisonJanuary03]Group By FirstWord([PlantName])

Either I got an error stating that a subquery could only return one record (that I can't reproduce now), or I'd get a datasheet with no records. Am I putting the statement in the wrong place entirely?

Thanks again,
Lisa

 

[danvlas]

This should work:

Select FirstWord([PlantName]As Whatever From [qryComparisonJanuary03] Group By FirstWord([PlantName]);

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html