Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Joining of fields failing on Report level (Access20)

    I want to add value of the field CORP to the Report title.
    So I am taking textbox and writitng "Report Title" & " - " & [CORP] and it wouldn't take it.
    I had also tried to add 2 fields like [LAST_NAME] & ", " &[FIRST_NAME] and it did not work.

    I went and wrote the same things in Make_table query and it went just fine.
    I am still want to have it done on Report level rather then Access/Oracle level.
    I can not also fint TrimRight function. I need to trim the end of the "Report Title" & " - " & [CORP] if there is some characters like defis -.

    Please, help.

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

    Re: Joining of fields failing on Report level (Access20)

    1) You can set the Control Source of a text box in the Report Header (or anywhere you want it) to a formula beginning with =, for example

    ="Report Title" & " - " & [CORP]

    or

    =[LAST_NAME] & ", " &[FIRST_NAME]

    2) Access uses VBA functions. There are 3 functions available to trim strings:
    LTrim removes leading spaces
    RTrim removes trailing spaces
    Trim removes leading and trailing spaces

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Joining of fields failing on Report level (Access20)

    THANKS

  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Joining of fields failing on Report level (Access20)

    ....however I see in HELP example: MyAddress: Trim([Address])
    When I am tryint to do this for my field it gives me syntax error.
    Also everything else. This Access unlike any other I ever dealt with...

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

    Re: Joining of fields failing on Report level (Access20)

    Something like

    MyAddress: Trim([Address])

    is meant for use in a query. It creates a new column in the query whose name (column header) is MyAddress that displays the address with leading and trailing spaces removed. You cannot use the above in the Control Source of a text box on a form or report.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Joining of fields failing on Report level (Access20)

    What is wrong with me?
    =[LAST_NAME] & ", " &[FIRST_NAME] gives me an error...again!

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

    Re: Joining of fields failing on Report level (Access20)

    You'll have to provide (a lot) more information. Otherwise it is impossible to help you.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Joining of fields failing on Report level (Access20)

    It is the same as in my OP.
    Have Report. Want to join LN and FN together and getting an error.

    Last time I wasn't able to (even after your helping) and I did that on query level.

    Now I do not have an option to do it on quesry so I want to use Report text box! No luck - Error!

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

    Re: Joining of fields failing on Report level (Access20)

    Are you absolutely sure that LAST_NAME and FIRST_NAME are fields in the record source of the report, and that they are spelled exactly that way?

  10. #10
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Joining of fields failing on Report level (Access20)

    I am copy/pasting :
    FIRST_NAME
    LAST_NAME

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

    Re: Joining of fields failing on Report level (Access20)

    What is the Record Source of the report?

    a) The name of a table
    [img]/forums/images/smilies/cool.gif[/img] The name of a query
    c) An SQL string SELECT ...

  12. #12
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Joining of fields failing on Report level (Access20)

    FIRST_NAME and LAST_NAME are coming from a quesry that based off of 2 tables (Oracle ODBC connection) where FIRST_NAME and LAST_NAME are text fields.
    Later query becomes part of another query where FIRST_NAME and LAST_NAME are group by fields.
    Is this is why?
    Should I make table and then use it?

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

    Re: Joining of fields failing on Report level (Access20)

    You haven't answered my question directly, but I assume that you meant option [img]/forums/images/smilies/cool.gif[/img]: the record source is the name of the query.
    Could you do the following:

    - Open this query in design view.
    - Select View | SQL.
    - Copy the text that you see (it should begin with SELECT) and paste it into a reply.

    Thank you.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Joining of fields failing on Report level (Access20)

    Oh, sorry...
    SELECT DISTINCT [4-qry_Active_Report_1st].T9_LOB, [4-qry_Active_Report_1st].BUSINESS_UNIT, [4-qry_Active_Report_1st].DESCR, [4-qry_Active_Report_1st].LAST_NAME, [4-qry_Active_Report_1st].FIRST_NAME, [4-qry_Active_Report_1st].RECRUITER_ID, [4-qry_Active_Report_1st].NAME_DISPLAY, [4-qry_Active_Report_1st].ENDING_TITLE, [4-qry_Active_Report_1st].HRS_PERSON_ID, SYSADM_PS_HRS_APP_DEG_VW.SCHOOL, [4-qry_MAX_INTRV_DT].HRS_INT_DT AS MaxDT, INTRV_TYPE_DESCR.TYPEDESCRIPTION, [4-qry_Active_Report_1st].HRS_JOB_OPENING_ID, [4-qry_Active_Report_1st].HRS_PRM_LOCATION
    FROM [4-qry_Active_Report_1st] INNER JOIN ((SYSADM_PS_HRS_APP_DEG_VW LEFT JOIN [4-qry_MAX_INTRV_DT] ON SYSADM_PS_HRS_APP_DEG_VW.HRS_PERSON_ID = [4-qry_MAX_INTRV_DT].HRS_PERSON_ID) LEFT JOIN INTRV_TYPE_DESCR ON [4-qry_MAX_INTRV_DT].HRS_INTVW_LEVEL = INTRV_TYPE_DESCR.HRS_INTVW_LEVEL) ON [4-qry_Active_Report_1st].HRS_PERSON_ID = SYSADM_PS_HRS_APP_DEG_VW.HRS_PERSON_ID
    GROUP BY [4-qry_Active_Report_1st].T9_LOB, [4-qry_Active_Report_1st].BUSINESS_UNIT, [4-qry_Active_Report_1st].DESCR, [4-qry_Active_Report_1st].LAST_NAME, [4-qry_Active_Report_1st].FIRST_NAME, [4-qry_Active_Report_1st].RECRUITER_ID, [4-qry_Active_Report_1st].NAME_DISPLAY, [4-qry_Active_Report_1st].ENDING_TITLE, [4-qry_Active_Report_1st].HRS_PERSON_ID, SYSADM_PS_HRS_APP_DEG_VW.SCHOOL, [4-qry_MAX_INTRV_DT].HRS_INT_DT, INTRV_TYPE_DESCR.TYPEDESCRIPTION, [4-qry_Active_Report_1st].HRS_JOB_OPENING_ID, [4-qry_Active_Report_1st].HRS_PRM_LOCATION
    ORDER BY SYSADM_PS_HRS_APP_DEG_VW.SCHOOL;

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

    Re: Joining of fields failing on Report level (Access20)

    Thanks.

    Open your report in design view.
    Select the text box whose control source is =[LAST_NAME] & ", " &[FIRST_NAME]
    Make sure that the name of this text box is neither FIRST_NAME nor LAST_NAME, but something else, for example FullName.
    Does that help?

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
  •