Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query with trimmed names (Access 2000)

    CAn i build a query with trimmed names? For example of all sorts of apples to choose to show only apples not nothing else.For all sorts of nuts to show only nuts.Perhaps to stop on the first record and show it.I need this in order to cerate labels.The labels are one and the same for all the apples, so it doesnt matter which exactly grade i will choose.The point is to have a query that shows only apples, and then nuts.I am sending an example

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: query with trimmed names (Access 2000)

    Yes you can do it. The function Instr will find the position of one string inside another so:

    Instr([grade]," ") will tell you the place of the space.

    You can then use the Left function to extra the left hand end , using the result of the instr to say how much to get.

    But I think you would be better to just split the field into two separate fields. Product Category and a separate one for Grade. it would be more flexible in the long run.
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query with trimmed names (Access 2000)

    I copied your suggestion in the query in the place of grade but what i gor is the productid number and not the global name of the product. What is not in order with my actions ?

    regards

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: query with trimmed names (Access 2000)

    This query gives you what I think you want.
    Regards
    John



  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: query with trimmed names (Access 2000)

    Try the following in your query. This works as long as the first word before the space describes the object such as Nuts, apples, etc.:

    IIf(InStr([GRADE]," ")>0,Left$([GRADE],InStr([GRADE]," ")-1))
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: query with trimmed names (Access 2000)

    Or this:

    global: IIf(InStr([grade]," ")>0,Left([grade],InStr([grade]," ")-1),[grade])
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query with trimmed names (Access 2000)

    Thank very much both of you.The query now does show the results you have suggested.I have now in my query three lines where apple stands and also three lines where nut stands.I need now to select only the first grade and to show only this grade.
    In my case this means that the query will consist of two lines :
    productid grade
    3 apple
    7 nuts
    It means that i will put the labels for the apples only in product number 3 and 7 for the nuts.Is it possible ?

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: query with trimmed names (Access 2000)

    Is it important to have the productid included in the query?

    If all you really need is the names for the labels:
    apples
    nuts

    then remove Productid from the query, and Grade if it is still there, then display the Properties Box ( view..properties)

    If the top of the properties box says "field properties" , click in the grey area in the top half of the query grid, so that the properties box display "query properties"
    then set the "Unique Values" property to "yes".
    Regards
    John



Posting Permissions

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