Formatting with Lookup Fields

[SourceOfAccess]

If I've got an AutoNumber field formatted as: \C0000 000 in Table A. If I attempt to create a Lookup field in Table B that targets the formatted table in Table A, 2 issues arise:

1) If the Lookup field does NOT allow multiple values: The formatting carries over and is treated as Text, hence creating an error as it does not meet the data type of the Lookup field (which is always Number)

2) If the Lookup field does allow multiple values: The formatting does not carry over (which is good). But MS Access does not allow it to be re-formatted.

I've done some digging around on the web but couldn't find anything on this. Any ideas?

[mbizup]

I'd avoid formatting your numeric fields in the table's design. Instead, format the *display* to the user on an as-needed basis. Numbers can be formatted on the fly through code, queries or properties in your forms' design so that they can be displayed to the user in the format you want, without affecting their storage as *numbers* in the underlying tables.

[zmbd]

SourceOfAccess

IMNHO, and the opinion of many others, unless you are developing a sharepoint/Webapp application, the table-level lookup fields should be avoided like the plague.
See a recent discussion here:
Do Multivalue fields in Access, break normalization rules.
Therein is also a link to:
The Evils of Lookup Fields in Tables


mbizup suggestion about the general numeric formatting is something I personally do at the table level. I tend to do the formatting either in the query, form, and/or report.

As to what is happening... as you've ran into, the actual value shown in the LUF/MVF isn't actually what is being stored for that field... several of my posts in the aforementioned link cover that in detail.

My final suggestion, IMHO, it is best practice not to use the autonumber field for anything else than as a unique, one use, record id / primary key and rarely if ever for anything meaningful for the end user. I have done that in the past and been "burnt" rebuilding tables when the "lot" numbering scheme had to change from a simple system to one that had to be "special." If you need a "serial number" or "Sequence number" please use the search bar at the top of the page. We have had some really good coverage of these topics and it's a perennial favorite :)

[SourceOfAccess]

Thank you so much for your responses.

I was able to resolve the first problem by formatting the fields in the form, as both of you suggested.

However, I'm still unsure how I should go about solving the second issue, where I'm unable to format the values if multiple values are selected through Lookup - even if I try doing so in the forms/reports.

Ideas?

[jforbes]

I probably would have responded to this earlier, but don't fully understand the question. How is your Lookup implemented? Is it in the Table Definition in Access? Or is it a ComboBox on a Form that is allowing you to select from Table A and putting the Value into Table B? Either way, I don't understand how you can store multiple values in your lookup without resorting to multiple ComboBoxes or some code in the AfterUpdate Event of a ComboBox. It could be that Access allows this to happen in the Table Definition, but I've never used Table lookups as that method is specific to Access and isn't implemented in other database systems and languages.

[zmbd]

SourceOfAccess:

You will have to expand your MVF by query, that is your only hope. MS>Guide to multivalued fields
The sections: "Modify the design of a multivalued field" and "How to employ a multivalued field in a query" will be your starting point and then there is the information to be found here Using multivalued fields in queries

IMHO: unless you are using SharePoint, you would be much better off to move to a 1:M or M:M type table relationships which will give you easier control over how you present the data to your end-users.