Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Change Field Name in Report (Access 2003)

    I want to change the fields in a report to read something different when they appear on my report.

    So the field "Criteria" has two options - Local Inventory and Central Office Registered. If it is Local Inventory I want it to output as Minor Asset and Central Office Registered to Major Asset.

    This is the query to I have built so far.

    SELECT tbl_CurrentAssets.Item, tbl_CurrentAssets.Criteria, IIf(IsNull([COAssetNo]),"No Number",[COAssetNo]) AS COAN, IIf(IsNull([RecordID]),"No Number",[RecordID]) AS RID, IIf([Criteria]="Local Inventory",[RID],[COAN]) AS RecNum
    FROM tbl_CurrentAssets;

    Kerry

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Field Name in Report (Access 2003)

    Kerry,

    What issue are you having with what you have so far?

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Change Field Name in Report (Access 2003)

    I have cut the query down to make it clearer.

    Criteria has two options - Local Inventory or Central Office Registered. These are too long for my labels I am printing so I want Local Inventory to change to Minor Asset and Central Office Registered to change to Major Asset.

    I hope this is clearer.

    SELECT tbl_CurrentAssets.Item, tbl_CurrentAssets.Criteria
    FROM tbl_CurrentAssets;

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Field Name in Report (Access 2003)

    Now I think I have it. You could leave the query as is and in the Control Source of the text box used in the labels report, you could change the source from [Criteria] to:

    IIf([Criteria]="Local Inventory","Minor Asset",IIf([Criteria]="Central Office Registered","Major Asset","Unknown"))

  5. #5
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Change Field Name in Report (Access 2003)

    Thanks for this but I get this error,
    Attached Images Attached Images

  6. #6
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Field Name in Report (Access 2003)

    Take the beginning [ off the calculation and you should be all set.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Field Name in Report (Access 2003)

    I didn;t notice the last ] at the end of the calculation but remove that as well. The IIF should be the first item and the ) should be the last item.

  8. #8
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Change Field Name in Report (Access 2003)

    sorry I am really confused now. Can you just type out what I should put inthe Control please?

  9. #9
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Field Name in Report (Access 2003)

    No problem. Delete everything that is there and paste the following:

    IIf([Criteria]="Local Inventory","Minor Asset",IIf([Criteria]="Central Office Registered","Major Asset","Unknown"))

  10. #10
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Change Field Name in Report (Access 2003)

    Sorry to be a pain but I did exactly that but still have an error.

    I think the key is to look at the part that says syntax error comma in query expression.

  11. #11
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Field Name in Report (Access 2003)

    Hmmm...could you send a screen shot of where you're entering the control source. In the error you sent, it appeared as though the issue was that the IIF( statement was preceeded by [ to appear as [IIF( which would cause the error you are seeing.

  12. #12
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Change Field Name in Report (Access 2003)

    The screen dump shows [ 's that arent really there. So the dump I sent before is accurate, just disregard the [ ] at the beginning and end.

  13. #13
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Field Name in Report (Access 2003)

    You could also load this as a new field in your query as such:

    MyName: IIf([Criteria]="Local Inventory","Minor Asset",IIf([Criteria]="Central Office Registered","Major Asset","Unknown"))

    Then you could just reference MyName in the label control.

  14. #14
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Change Field Name in Report (Access 2003)

    Now ya talkin!

    I also looked at the switch function -- with some work that perhaps would have done the trick too.

    Thanks

  15. #15
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Field Name in Report (Access 2003)

    Outstanding! Off to slumber so I'm glad I could help before cashing in.

    Cheers!

Posting Permissions

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