Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Criteria Choice 2 (2003)

    I had problems with a database in a previous post <post#=455626>post 455626</post#> which, thanks to you all, I got sorted. As usual, I've been asked for more. I need to produce a report which will give me the number of times a user has actions and how many are completed. The problem lies with the fact that User(now changed to Actionee) could contain 2 lots of initials. So if user LG, had 5 actions but also one which was LG/AB, I want the count to be 6. The result I get, which is based on the original query, with the Sum of the User in User field, I get a 5 and a 1. How can I get the query to recognise the first pair of letters only

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria Choice 2 (2003)

    If users can ALWAYS be identified by the first two characters, you can create a calculated field in your query:

    Abbr: Left([User],2)

    and group on this field instead of on User (or Actionee). If LG could be the same user as LG/AB, but also the same as AB/LG, it becomes more complicated.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Criteria Choice 2 (2003)

    In my previous post, I wanted to be prompted for User, so if the User was LG, if it had been entered as AB/LG, it would still find it. For this report, if LG was the second User, he would be ignored. I thought that the formula left([User],2), would be the answer, but when prompted for User, if we put LG, any with 2 characters before LG (eg AB/LG) are coming up.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria Choice 2 (2003)

    In your post from February, you had criteria of the form

    Like "*" & [User ] & "*" Or [User ] Is Null

    If you want only the first user, omit the first *:

    Like [User ] & "*" Or [User ] Is Null

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Criteria Choice 2 (2003)

    Still giving me AB/LG

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria Choice 2 (2003)

    Can you post a stripped down copy of the database? Leave enough for us to be able to see the problem.

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Criteria Choice 2 (2003)

    Stripped it down as far as I can without loosing all the data, and it's still too big even when zipped (202k)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria Choice 2 (2003)

    See <post#=401925>post 401925</post#> for instructions on how to reduce the size.

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Criteria Choice 2 (2003)

    Thanks Hans. Here it is. What I'm trying to do is basically create a report showing how many actions people had, and how many they had completed. This is why I want the query to give me just the first name in Actionee (this could be a problem because some are 'Production'. If I can sort the search for the Actionee out, I will do the same for Raised By Whom

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria Choice 2 (2003)

    You have used Like "*" & [Enter Actionee] instead of Like [Enter Actionee] & "*", meaning you'll get anything FOLLOWED by the initials you entered instead of anything STARTING with the initials.
    And you entered this condition only once. If you study the way the criteria are expanded by Access, it's clear that the conditions have to be repeated following a fixed pattern.

    See attached version. I had to import the tables and query into a new database, for the one you attached cannot be opened ("The Visual Basic for Applications project in the database is corrupt").

  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Criteria Choice 2 (2003)

    That's working fine now thanksHans, but for one minor thing. In some cases it's picking the backslash up. I've created another field to create a name using :-

    Name: IIf(Left([Actionee],3)="/",Left([Actionee],2),Left([Actionee],3))

    So if the query found LG or LG/ABC, it would give the Name: LG. But in some cases, I'm still getting the / after the second letter. I've had to change this, as looking at the table, some of the Actionees use 3 or 4 letters, and not just two.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria Choice 2 (2003)

    The condition Left([Actionee],3)="/" will never be true, for Left([Actionee],3) returns the first 3 characters of Actionee. This cannot be equal to the single character "/". Try

    Name: IIf(Mid([Actionee],3,1)="/",Left([Actionee],2),Left([Actionee],3))

    or

    Name: IIf(InStr([Actionee],"/"),Left([Actionee],2),Left([Actionee],3))

  13. #13
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Criteria Choice 2 (2003)

    Thanks Hans, thats working fine. Thanks again.

  14. #14
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Criteria Choice 2 (2003)

    Is it possible to create a formula using this created field? I created a formula in another field using this field, and now when I run the query, it asks for name.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria Choice 2 (2003)

    If you use [Name] in a column to the right of the Name column, it should work OK. If you use it in a column to the left of the Name column, you'll be prompted for Name.

Page 1 of 2 12 LastLast

Posting Permissions

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