WHY AM I GETTING FIXED 2 FORMAT WHEN THE SOURCE IS INTEGER?

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

In Access 2002 I have two tables. Table A's primary key is
an Integer column. Table B has a foreign key pointing to
Table A's primary key.

I set the lookup row source properties as a SQL select
statement. Anywhere I can set the format for the column, I
set it as Fixed 0 or leave the format and size properties
to nothing.

In any case when I'm viewing the lookup in a datasheet
view or in a dropdown combo box in a form, I do NOT see an
integer number. I see a fixed number with two decimal
places like 2003.00. This is not what I want to present to
users in my database.

What's happening here? How do I correct this anomaly? I
can find this exact trouble in Microsoft' Knowledge base
with the keys I'm searching.

Unless someone has a solution here, I'm changing the
column from an integer value to a Text 4 string. It makes
me take a few extra steps in code, but it will eliminate
the problem.

Howard
 
Howard-

Are you positive that Format is blank and Decimal Places is Auto in *both*
tables? What happens if you build a query on the lookup table instead of
using an SQL statement?

When you use a lookup (combo box or list box), the control displays all
values as text, so Access attempts to convert the number to a string based
on the default formatting values for that column in the table. Also, you
normally hide the looked up number value - for example, look up ProductID
but display Product Name - so the display doesn't normally matter.

If you're still stuck, please post the settings (properties) for the number
field in both TableA and TableB. Include the Lookup properties for the
foreign key in TableB. If these are set correctly, you should see an
integer text value with no decimal places.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
John,

You were right. I hadn't set both tables to Format nothing and decimal
places to Auto.

Thanks for taking the time to help me.

Howard
 
Back
Top