Change Field Name in Report (Access 2003)

Topic

New Reply

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

Reply | Quote

Replies

Kerry,

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

Reply | Quote

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;

Reply | Quote

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”))

Reply | Quote

Thanks for this but I get this error,

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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”))

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Now ya talkin!

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

Thanks

Reply | Quote

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

Cheers!

Reply | Quote

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.

Reply | Quote