Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 31
  1. #16
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    I tried braking it down
    1. I started with the unmodified supervisor name - it worked


    2. displayed original supervisor name fields and extracted LN with group by and it worked
    3. added first name to the query (original sup name, last name extracted) and this is when I get the error. I tried just running the query with the first name extracted and I still get the error. here is the code if it helps any:
    SELECT Left([SupervisorName],InStr([SupervisorName]," ")-1) AS FN
    FROM tbl_EmployeeData_EODinfo
    GROUP BY Left([SupervisorName],InStr([SupervisorName]," ")-1);

  2. #17
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I wonder if it is data related? If you can, zip and post your database - just that one table and query would suffice.

  3. #18
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    I don't really feel comfortable posting my DB since it is a live DB and there is a lot of personal data of our employee's.

  4. #19
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Did you try post #2?

  5. #20
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create a query with this: InStr([SupervisorName]," "). For your FN you are subtracting 1, if this field isn't numeric then you may have a problem.

    So it would be: IIf(InStr([SupervisorName]," ")>0,Left(.......))

  6. #21
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Yes I tried using CHR(32) idea from post 2, and get an Invalid procedure call error... the instr function returns a numeric value of the location of the blank space so I though you had to put the -1 so it wouldn't include the blank in the returned results.

    I tried using the IIF function, but I must have a syntax error somewhere: FN: IIf(InStr([SupervisorName]," ")>0,Left([SupervisorName],InStr([SupervisorName]," "))-1,"XXX")

  7. #22
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Bracket in wrong place: ....," ")-1),"XXX")

  8. #23
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    OK, that worked, and it worked with the grouping!! here is the code I put in:

    UpdatedName: IIf(InStr([SupervisorName]," ")>0,Trim(Right([SupervisorName],Len([SupervisorName])-InStr([SupervisorName]," ")) & ", " & Left([SupervisorName],InStr([SupervisorName]," ")-1)),"XXX")

    Any idea why putting in the iff statement would make a difference?

  9. #24
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Don't stop here! Keep going until you have found the answer, never leave it unknown as there is something here which you must repeat in any future development with this table. Or you may have data corruption which points to an issue somewhere else in the database.

    What do you know so far:
    1 - it is data-related
    2 - InStr(SupervisorName," ") does not return a numeric value
    3 - why not? It does not find " " in the field value

    In post #20 I suggested you do the Instr as a field in you query, this could point you to errors (such as display Supervisor name plus the instr sorted asc)

  10. #25
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    I did test: InStr([SupervisorName]," ") and it returns a numeric value for the location of the " " in the name.

  11. #26
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Did you check every record in the table?

  12. #27
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    I ran it for the whole table, I didn't check every line but I did check about 10 random fields and they were all correct.

  13. #28
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not good enough! One (or more) of the records are causing the problem. Sort on the field so that the non-numeric will be either at the beginning or at the end.

  14. #29
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    I have to work on some other projects for the rest of the day, but ill try tomorrow. I just though of something that might be causing the issue... the field is a free field that the user can enter in the name however (there is to much movement to make a dropdown list to eliminate that) but I remember seeing "Vacant" entered on a few so I wonder if the lack of a blank space is causing it to give me the error...

  15. #30
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    No guessing allowed! Test it by putting it in as a criteria - where name = Vacant, and see if that is true. Otherwise keep hunting.

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 06-26-2015, 08:55 PM
  2. Replies: 17
    Last Post: 03-16-2014, 10:52 PM
  3. Replies: 3
    Last Post: 12-27-2013, 02:33 PM
  4. Replies: 1
    Last Post: 03-03-2012, 10:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums