Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Unanswered: Setting a single text box with only a Specific value

    Hi I have a list of names in the "details" position in a report. I have gotten the team lead out of the list, but now I need to put her in the Page Header Section. I first tried to just put it in the control source, and got nothing. Then I tried a little VBA.

    I tried
    Code:
    txtChief.Value = DLookup([FirstName] + " " 
    + [LastName] + [HomePhone], Employees, [LastName] = "abba")
    and it just put #NAME? for each employee.
    Ryan
    My Blog

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    As far as I'm aware you can only return one field at a time using DLookUp. You might need to use mulitple DLookUp's and then concatenate them together to place in the text box.

    Canupus


    Quote Originally Posted by rguy84
    Hi I have a list of names in the "details" position in a report. I have gotten the team lead out of the list, but now I need to put her in the Page Header Section. I first tried to just put it in the control source, and got nothing. Then I tried a little VBA.

    I tried
    Code:
    txtChief.Value = DLookup([FirstName] + " " 
    + [LastName] + [HomePhone], Employees, [LastName] = "abba")
    and it just put #NAME? for each employee.

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    HI

    I think canupus is correct. Also your syntax is incorrect in the DLookUp.

    Try this

    DlookUp(“FistName”,”Employeer”,”LastName = ‘abba’”) & “ “ & DlookUp(“LastName”,”Employeer”,”LastName = ‘abba’”)

    You will need apostrophes round the criteria ‘TEXT’ (bus not if the criteria is numeric).


    Please not the above will always return the same name!!

    I assume LastName is a unique field?

    I think the criteria part of the DLookUp should be bases on a control or field from the report !

    ie “LastName = ‘” & [Report Field/TextBox Name] & “’”


    Also I believe is should be possible to provide this info in the query that the report is bases on and using a bound control, but without knowing the DB structure etc. it is not possible to be sure.


    MTB

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Might I suggest that your schema may be slightly flawed? If you need to track team leaders in a hierachy-style fashion, I submit you consider defining a one->many relationship at the table level.

    It would really make your life easier when it comes to creating reports such as this one.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Quote Originally Posted by Teddy
    Might I suggest that your schema may be slightly flawed? If you need to track team leaders in a hierachy-style fashion, I submit you consider defining a one->many relationship at the table level.

    It would really make your life easier when it comes to creating reports such as this one.
    What do you mean?
    Ryan
    My Blog

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Quote Originally Posted by canupus
    As far as I'm aware you can only return one field at a time using DLookUp. You might need to use mulitple DLookUp's and then concatenate them together to place in the text box.

    Canupus
    Nope,
    Example 5 - a DLookup which concatenates fields

    =DLookUp([EmployeeSurname] & ' ' & [EmployeeForenames], tblEmployees, [EmployeeID] = 1234567)

    If there is a record in the table with:

    EmployeeID = 1234567
    EmployeeSurname = Wilson
    EmployeeForenames = John

    the DLookup function will return:

    John Wilson

    Note the use of & ' ' & to insert a space between the two parts of the name

    From: http://www.tek-tips.com/gfaqs.cfm/le...d/705/fid/4978
    Ryan
    My Blog

  7. #7
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Now I am just using a plan txtbox with the source of:
    Code:
    =IIf([lastname]="abba",[firstname]+" "+[lastname]+[homephone],"")
    and still no hits
    Ryan
    My Blog

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I mean you should have a table of team leaders referencing the employee. Then base your report's grouping levels on that information.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    So have tblTeamLeads, have all the junk from tblEmployees with a teamLead field and have the initals of the lead as the key?
    Ryan
    My Blog

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm not sure what your exact schema is right now, I assume you have something like tblTeam?

    You would still use the teamID from tblTeam as your foriegn key in tblEmployees, you would just add another table, tblTeamLeads and attachit to tblTeam. If there is only one lead per team, then you could actually forgo this and add teamLead to tblTeams. If there is more then one, then you make entries into tblTeamLeads of the format: teamID, employeeID.

    If there is only one team leader, then you can use the teamleader value as a grouping level in the report. If there is more then one, then you could use a subreport in the detail header based on the teamID.

    edit: You should hit me up on AIM and I could flesh this idea out a little more.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Ok I did what Teddy said, made a grouping. Now I get a syntax error on:

    ="Team Lead:" iif([teamlead]="true",[firstname]&" "&[middleinitial]&" "&[lastname]&" "&[homephone],"")

    The error says I entered a operand such as + in an expression without a corresponding one...
    Ryan
    My Blog

  12. #12
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    If it's a field definitian in query designer then try this

    Team Lead:iif([teamlead]="true",[firstname]&" "&[middleinitial]&" "&[lastname]&" "&[homephone],"")

    If in VB code then try this

    iif([teamlead]="true",[firstname]&" "&[middleinitial]&" "&[lastname]&" "&[homephone],"") as [Team Lead]


    MTB

  13. #13
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    No I am using that as the source...
    Ryan
    My Blog

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Did you setup a team leader table?

    If not, what did you group on?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  15. #15
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Nope, just added a TeamLead field (yes/no)...
    Ryan
    My Blog

Posting Permissions

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