Results 1 to 9 of 9
  1. #1
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108

    query prompting for a value that is already defined

    Hello,
    In our DB an employee's supervisor's name is entered into one field ex. "Bob Smith" (it was requested to be set up this way). Now I need to format the names in the query to "Last, First". in my query I have:

    Space: InStr([supervisorname]," ") to find the space between the FN and LN


    Length: Trim(Len([SupervisorName])) to find out the length of the field
    First Name: Trim(Left([SupervisorName],InStr([SupervisorName]," "))) finds the first name

    the issue is when I try to get the last name, I have it coded as:
    Last Name: Trim(Right([SupervisorName],([Length]-[Space])))

    When I run the query it prompts me for the value of the Length and Space. If I take the [Last Name] part out of the query it runs fine and displays the correct values for Length and Space. I didn't know if there is something I am missing or what is going on. I even tried calculating Length - Space in a separate field but I still get the same thing.

    Thanks,
    Will

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you don't need to be as complex as that

    First Name: Left([SupervisorName],InStr([SupervisorName]," ")-1)
    Last Name: mid([SupervisorName],InStr([SupervisorName]," ")+1)

    note you code will fail if there is no space and only deals with a simple situation - what if the name is Bobby P. Smith?

  3. #3
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Thanks Ajax, your suggestion worked The names are entered w/o middle names ect we only get the first and last names of the supervisors, but I understand what you mean.

  4. #4
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    now that we got that fixed... Could anyone tell me why when I try to join the names together it still prompts me for a value for the FN & LN? see below

    This is what I have to join the names together:
    Supervisor Name: [First Name] & ", " & [Last Name]

    this is the code I used to get [First Name] & [Last Name]:
    First Name: Left([SupervisorName],InStr([SupervisorName]," ")-1)
    Last Name: mid([SupervisorName],InStr([SupervisorName]," ")+1)

    I have done this same thing in other parts of our DB, but I have never had this issue...

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    depends on your query. If it is a select query, then it should work, but my guess is you have an update or insert query - in which case it won't, you will need to use

    Supervisor Name:Left([SupervisorName],InStr([SupervisorName]," ")-1) & "," & mid([SupervisorName],InStr([SupervisorName]," ")+1)

    Note that spaces in field names is never a good idea (if it is because that is what you want to see on a form or report, use the field caption property - that is what it is for). VBA for example will substitute a space with an underscore so you are then not comparing like for like

    Also Name is a reserved word which means you will need to use square brackets to avoid confusion, and even then you will still hit unexplained problems on occasions.

  6. #6
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Ajax,
    It is a select query, I used your suggested code to concatenate the names together and it worked. I didn't know I could enter the property sheet in a query, I found how to do it. your just teaching me all kinds of things Do you know of any reason why my original formula wouldn't work even when I took out the spaces SupName: [FirstName] & ", " & [LastName] it still prompts me for the values for FN & LN. Everything is working, im just curious what is wrong with the syntax. If it matters the FN & LN fields are entered before the SupName field. Thanks again for all your help!!

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK - so it is a select query - is it also a group by query? if so, you will get the same problem. If this is not the case, post the entire sql to the query you have a problem with

  8. #8
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Yes it is a group by query. That is good to know, thanks again for all your help Ajax!!!
    Will

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Yes it is a group by query
    if you are only grouping and not summing, counting etc, remove the group by and try using SELECT DISTINCT instead (or in the query window, right click on the top part, select properties and change Unique Values to Yes

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Join query is prompting for parameter?
    By tb1150 in forum Access
    Replies: 11
    Last Post: 06-19-2015, 08:36 AM
  2. Prompting for user input in query
    By sherrygroves in forum Access
    Replies: 7
    Last Post: 08-31-2014, 09:32 PM
  3. Crosstab Query Prompting twice for data?
    By AccessFreak in forum Queries
    Replies: 5
    Last Post: 01-07-2011, 10:38 AM
  4. Query prompting for values?? Why?
    By Schon731 in forum Queries
    Replies: 2
    Last Post: 12-23-2010, 01:19 PM
  5. Replies: 0
    Last Post: 04-26-2008, 09:59 AM

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