View Text rather than ID in calculated field

[InspiredGrowth]

Hey guys, hoping somebody can point me in the direction of showing the Activity field TEXT rather than ID for my calculated "File As" field?

Cant show a picture :-/ but here is my formula:
[Activity] & " - " & [Task Title]

This currently outputs something like:
"100 - Advertising Logo" instead of "Advertising - Advertising Logo"

Many thanks in advance,
Mikey.

 

[NickHa]

How is 'Advertising' related to '100'? Is the former a text field in a table whose key is 'Activity'? If so, give us details of the table and we can help with the relevant query.

 

[InspiredGrowth]

sorry, 100 is the ID in the Activities table. I would like my auto calculated field to show the Activity.text rather than id.text

 

[boblarson]

If you are using lookups at table level, then you need to not use them. See here for more about that:

http://access.mvps.org/access/lookupfields.htm

That will cause problems like this as well as other things down the line.

 

[InspiredGrowth]

I have no idea how to build a relational DB without using lookup fields, its all i know!

Back to square one I guess I go.

 

[boblarson]

I have no idea how to build a relational DB without using lookup fields, its all i know!

Umm, if you would pay closer attention to what I wrote you would see that I did NOT say that lookups are not to be used at all. What I wrote is DO NOT USE LOOKUPS DIRECTLY AT TABLE LEVEL. What that means is that you don't go into your table and into your field and then go to the lookup tab and add it there. That is the surest way to get yourself having issues.

For lookups you use your lookup tables but you use them with FORMS when entering the data (using combo boxes or list boxes) but you do not define them at table level.

Read the article again and it will confirm what I said.

 

[InspiredGrowth]

is their a solution to my opening question?

 

[boblarson]

is their a solution to my opening question?

Yes, remove the lookup at table level and then use a query with both tables in it to display the text.

 

[InspiredGrowth]

thanks for your help