Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108

    Braking first and last name from a name field...

    Hello,


    I am trying to split the name field SupervisorName (Bob Smith) into separate fields so I would have "Bob" by itself and "Smith" by itself. here is a pic of my query, I have tryied typing it in, copying it from different websites and nothing is working but I cant see why not... I'm sure its going to be something stupid... but its starting to make me really mad here is what all the examples ive seen are saying the syntax should be
    "Left([strName],InStr([strName],” “)-1)"
    Click image for larger version. 

Name:	name split.png 
Views:	23 
Size:	17.3 KB 
ID:	26127

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You might try Chr(32): "Left([strName],InStr([strName], Chr(32))-1)"

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you have names like Jean Michel Rousseau or Hendrik vant Goor or Pedro de la Hoya,
    then you may want to design your function/expression accordingly.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    That syntax looks right. Take out that table name. If still broken, test the parts of the statement. Just do the Instr line and see if it returns the right number. Or do the Left and take out the INstr and just put in a number. Do things to see which part is having the issue. Do you have more columns in that query that could be the issue and not this column?

  5. #5
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    well I was able to get it to work finally... but now I have another issue... The code below works, but I need to group by so I do not show 20 duplicate names when I run the report but when I switch on the totals option and set the group by option I get an "Invalid Procedure Call" error. I also need to have it filtered by a date range using "Between [Start of PP] And [End of PP]" in the Criteria section. anyone have any ideas why I get the error?

    Here is the code I got to format the name
    UpdatedSupervisorName: Trim(Right([SupervisorName],Len([SupervisorName])-InStr([SupervisorName]," ")) & ", " & Left([SupervisorName],InStr([SupervisorName]," ")-1))

  6. #6
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Even tried turning off the group by function and tried using the properties set to "Unique Values" to Yes but I get the same error...

  7. #7
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Try Expression instead of Group By for UpdatedSupervisorName.

    For the date criteria use Where

  8. #8
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    I tried what you said Aytee, the query ran but it did not get rid of the duplicate names.

  9. #9
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Please post your whole SQL statement.

  10. #10
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Here is my query set up both ways:


    group by turned off - This works, but I cannot have duplicate names listed


    Click image for larger version. 

Name:	name format  not sorted.jpg 
Views:	10 
Size:	144.0 KB 
ID:	26169



    Group by turned on w/ error msg


    Click image for larger version. 

Name:	name format group by.jpg 
Views:	10 
Size:	162.3 KB 
ID:	26170

  11. #11
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    To get to the SQL of the query, click on View on the top left, select SQL and copy/paste it here. The query works for me (using Expression) so maybe the problem is elsewhere in the query.

  12. #12
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    I have tried making a new query and I still get the same error... if it helps the supervisor name is entered FN LN and I am converting it to LN, FN this part is working, its when I try to group by I get the error, or even if I try to sort by alpha.


    SQL with group by on:

    SELECT Trim(Right([SupervisorName],Len([SupervisorName])-InStr([SupervisorName]," ")) & ", " & Left([SupervisorName],InStr([SupervisorName]," ")-1)) AS UpdatedSupervisorName
    FROM tbl_EmployeeData_EODinfo
    WHERE (((tbl_EmployeeData_EODinfo.OrientationDate) Between [Start of PP] And [End of PP]))
    GROUP BY Trim(Right([SupervisorName],Len([SupervisorName])-InStr([SupervisorName]," ")) & ", " & Left([SupervisorName],InStr([SupervisorName]," ")-1));

    Group By turned off:

    SELECT Trim(Right([SupervisorName],Len([SupervisorName])-InStr([SupervisorName]," ")) & ", " & Left([SupervisorName],InStr([SupervisorName]," ")-1)) AS UpdatedSupervisorName
    FROM tbl_EmployeeData_EODinfo
    WHERE (((tbl_EmployeeData_EODinfo.OrientationDate) Between [Start of PP] And [End of PP]));

  13. #13
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I have to admit defeat, I'm afraid, this works for me no matter what I do.

    Try starting a new query (grouped) - first with just the supervisor name, then adding the Left, the adding the Right. two fields, one field etc. Bit by bit. Not sure if it will help, but maybe!

  14. #14
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Thanks Aytee, I appreciate your help Ill give that a try and see what happens.

  15. #15
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Let us know!

Page 1 of 3 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