Share via

Group only on first word

Anonymous
2021-03-27T05:32:33+00:00

Hi,

I have a control called packaging which has information such as:

  • LARGE TUNDRA BOX
  • STONZ MEDIUM BOX
  • LARGE ARTIC TRACKS BOX
  • SMALL ARTIC TRACKS BOX
  • SMALL GREY WOLF BOX

I need to be able to group on "large", "medium" or "small" regardless of what other words are used in the packaging name.

Can this be done and if so, how

thanks

Jeanne

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2021-03-27T06:57:16+00:00

I need to be able to group on "large", "medium" or "small" regardless of what other words are used in the packaging name.

Hi CoteJ,

One way you could do is to pick up the first word with:

    Split(packaging_name," ")(0)

The packaging_name is split using the space as separator, and in this split_array you take the first element.

However, I would work in a different way. In a Packaging table I would use a separate field with the "size" of the packaging, to prevent all kind of mistypes.

Imb.

Was this answer helpful?

1 person found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-03-27T13:27:33+00:00

    I talked to the user and a different field will be used to store the information

    Thank you

    Was this answer helpful?

    0 comments
  2. George Hepworth 22,855 Reputation points Volunteer Moderator
    2021-03-27T12:14:45+00:00

    IMB is right. The reason you have this problem at all is the table design is inappropriate. 

    Size is an attribute of the  items. It belongs in a separate field called "Size". 

    In fact, I'm not sure that "box" is not also a separate attribute, "Packaging".

    Do you sell "Tundra" items ONLY in boxes, or do some of them sell in other types of packaging?

    This table of products should have (at least) these three fields:

    ProductName

    ProductPackaging

    ProductSize

    Your query then groups on "ProductSize" and is, therefore, relatively straightforward.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-03-27T11:57:41+00:00

    It the size was always the first word, such as:

    • LARGE TUNDRA BOX
    • MEDIUM STONZ BOX
    • LARGE ARTIC TRACKS BOX
    • SMALL ARTIC TRACKS BOX
    • SMALL GREY WOLF BOX

    Would it be possible to pick up the first word?

    Thanks

    Jeanne

    Was this answer helpful?

    0 comments