How to show Autonumber in a Combobox with no decimal places?

[VivDenham]

Hi there

I have a table - Projects - with 2 fields: Project ID (autonumber) and Project Name.

I then have a 2nd table - Decorations Used - which contains 3 fields - Decorations Used Id; Project ID, and Decorations Name. The Project ID in this 2nd table is a Lookup field back to the table Projects.

However, in the 2nd table, when I drop down the Combo list, the IDs have 2 decimal places, even though it is formatted to a Long Integer with 0 decimal places in both tables.

Can anyone tell me how to display this Project ID without any decimal places please.

Many thanks

[zmbd]

Welcome to one of the reasons you should not use lookup fields at the table level. This has to do with the formatting within the lookup field the wizard created and it can be a pain to track down.

I understand that you may have done this; however, please do the following again.


Go back to the design view of the table you are pulling data from.
Select the field you are "linking to" with the lookup field.
Double check that it is still set to autonumber

For the following: Don't just do by looking... really, you have to actually click in to the field and reset everything.

- DOUBLE check that the format field is blank. I mean, click in, get a cursor, press home, hold shift, press the right arrow key, press the delete. Don't know why; however, the wizard will hide stuff here.
---- SAVE!


Go back to the design view of the table with the lookup.
Select the field that has your lookup

For all of the following: don't just do by looking... really, you have to actually click in to the field and reset everything.

First look at bottom of the design grid:
In the general tab:
- Re-select long integer
---- SAVE!
- DOUBLE check that the format field is blank. I mean, click in, get a cursor, press home, hold shift, press the right arrow key, press the delete. Don't know why; however, the wizard will hide stuff here.
---- SAVE!
- In the decimal places field, if set to anything except zero or "auto" change it to zero.
---- SAVE!

Close all tables and re-open the table with the lookup.
This should be fixed.

If not, then go back in and if you had "Auto" in the decimal places, set to zero and save
Close all tables and re-open the table with the lookup.
This should be fixed.

If not (I know... one more time), then
Open your web browser and come back to this thread.
G to the design view again of the table with the lookup field.
Click on the "lookup" tab
in the grid, the recordsource field.
Select every thing in it and <ctrl><C> to copy.

Come back to this thread,
In the post box
Click the [CODE/] button on the format bar.
you will see two [code][/code] tags inserted in the message body. Paste the SQL from the record source between the two tags.
(( >> Before Posting (VBA or SQL) Code. ))

-

[[[ Z's Rant ]]]
Get rid of those lookup fields.

---- Lookup fields at table level ---
Using lookups at the table level lead to up-sizing issues and can make VBA and Macro programing very difficult:
The Evils of Lookup Fields in Tables

There is one and only one reason to use lookup fields at the table level, when publishing to a share point site as linked tables are not supported directly.
If you are using linked tables and attempt to publish to a share-point site you will get: ACCWeb105016 Relationships that are not associated with a valid lookup field are incompatible with the Web.
In that case, and only in that case, create a lookup field between the related tables. Easiest way is to use the Lookup Wizard to create the lookup. http://office.microsoft.com/en-us/ac...BMaccweb105016

If you're not using share point then lookup fields at the query level and the form level are preferred and lead to many fewer issues.
If you need help creating a lookup field at these levels, post a new thread.

[VivDenham]

Hi zmbd

That has worked perfectly! Thank you so much. I will now go away and read all the links you have given me.

Once again, thanks.
Viv

[zmbd]

thank you.... every once in awhile I hit the answer the first time ;-)