Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Unanswered: vba: extracting values for new tables from 'paths'

    Hi, I have a table with a memo field from which I want to extract/create categories onto a new table. Example field value:

    '/Agriculture/Agricultural Supplies/Machinery & Equipment/'

    All fields start and end with the slash character, all values are separated by the slash character.

    What I want to do is regard the first word/phrase as category level 1, the next after the second slash as catetory level 2, etc., so:

    Category 1/Level 1:
    Agriculture

    Category 2/Level 2:
    Agricultural Supplies

    I want to extract all level 1 values to a new table, all level 2 categories to another new table, etc.

    Can anyone please help me with some VBA code for this?

    There are lots of records, some share the same level one, level 2, but have different values further in the path, and some is the same. I only want the distinct values, of course. I think I can convert the field to Text, if none of the fields exceed 255 characters, I am checking that now, as it with text fields is possible to use 'distinct' in the query.

    Thanks,
    kedaniel

  2. #2
    Join Date
    Mar 2003
    Location
    Romania
    Posts
    32
    Hi,

    Attached is a database that may help you.

    You have to run function "CreateCategories" and then open query "qDistinctCategories".

    I assume that there is no category with a description longer than 255 chars.

    I don't see any reason why to create a table for each level.

    If you still need this please let me know.

    Florin
    Attached Files Attached Files
    Florin Profeanu
    floreanuprofin@yahoo.com

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by florin.profeanu
    You have to run function "CreateCategories" and then open query "qDistinctCategories".

    I assume that there is no category with a description longer than 255 chars.
    Thanks! That extracted the categories just fine :-)
    (correct, no category is more than 255 chars)

    The goal is to be able to show the content of another table based on the selection of a level/sublevel category. (list of organisations that are registered on a certain level)

    I attach the code as it looks now, needing a little advice on how to put the path referring to the parent of the current item into the Parent text field.

    Some more background info:
    In the further usage of the categories, I need to keep track of which subcategories belong to which parent levels, as there are some similar sub category names used for several different parent categories.
    So, I have 3 additional fields in the categories table:
    CatID, a unique number whose number series should reflect the level of the actual category. I choose 1000 for the first level, 20000 for the secoond, and so forth, so the first main category item will be numbered 1001, the first sub category item 2001, and the first item at level 3 will be 3001, etc. There are only 6 levels, and never as many as 1000 items in any (sub)category

    Then, I choose to preserve the original full path in a 255 char Text field named FullPath.

    The third field a 50 char text field named Parent. This is where I cannot seem to proceed now: I need either to extract the whole parent path of the current item onto this text field, or to use more fields to store each level of the path, which is probably not very elegant. I am not sure if the storing of the full parent path is the best solution either). I have trouble selecting only the path without the current item:

    rstCategories!Parent = Mid(rstPaths!Path, prevSlash + 1, crtSlash - prevSlash - 1)
    should be selecting all text from the beginning of the field to (and including) the slash before the current item's level description.

    Example:
    If the current item is
    /Computers & Internet/Hardware/Notebook/

    Then I want to store
    /Computers & Internet/Hardware/
    in the Parent field

    The goal is to be able to show the content based on the selection of a level/sublevel. (list of organisations that are registered on a certain level)

    I attach the code as it looks now, needing a little advice on how to put the path referring to the parent of the current item into the Parent text field.
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2003
    Location
    Romania
    Posts
    32
    Hello,

    Attached you can find two more functions, use the one that complies with your needs.

    Florin Profeanu
    Attached Files Attached Files
    Florin Profeanu
    floreanuprofin@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •