Querying a Decimal Field

I'm trying to query a Sales History Table linked to my access database through ODBC.

Tabe Fields:
Period (Decimal,2 precision, 0 scale)
Year (Decimal, 4 precision, 0 scale)
Customer (Text)
Item (Text)
...

When I query the table with no filters, I get all 42,000 records.  For 2002, there should be 7060 records.  When I specify 2002 for the Year, I only get 1590 records.  I'm thinking this has something to do with the decimal field type.  

SQL Statement:
SELECT OESLSHST.HIST_PERIOD, OESLSHST.HIST_YEAR, OESLSHST.CUST_NO, OESLSHST.ITEM_NO INTO tblSALES
FROM OESLSHST
WHERE (((OESLSHST.HIST_YEAR)=2002));

Creates a table to speed up access, which I delete later when it's no longer needed.

When I include a transfer spreadsheet command in the code I get all 42,000 records with no filter.  In excel, I can then filter on the Year field and I get 7060 records, so I know the records are in the table.  I just don't know why the above query isn't retrieving all 7060 records.
trimactechieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stevbeCommented:
can you look at what the actual stored value of Year is in your database?
perhaps >=2002 and < 2003 would work

steve
0
trimactechieAuthor Commented:
>=2002 and < 2003 still gives me 1590 records

As for the actual stored value, the table is in a pervasive.sql database used by our company's ERP system.  I definitely don't want to change the data type in this table.  I would like to stay within access for this particular project.  If there's some issue outside the relm of access, I'll have to look at doing something else.

Is there some way to change the field size of the table that my sql statement creates; using vba?
0
jadedataMS Access Systems CreatorCommented:
Don't confuse the way Excel filters records with the way Access filters records.  The scope of search in these two apps is way different.  Basically it is not a good comparison.

What is the exact datatype for OESLSHST.HIST_YEAR?

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

trimactechieAuthor Commented:
I'm using excel to show that there are in fact 7060 records with 2002 for the Year.  Far more than my query seems to find.

Data Type: Number
Field Size: Decimal
Precision: 2
Scale: 0
Decimal Places:  Auto
0
trimactechieAuthor Commented:
I figured it out.

I used the Cint formula in the query to change the value to an integer.

New SQL Statement:
SELECT OESLSHST.HIST_PERIOD, CInt([OESLSHST]![HIST_YEAR]) AS HIST_YEAR, OESLSHST.CUST_NO, OESLSHST.ITEM_NO INTO tblSALES
FROM OESLSHST
WHERE (((CInt([OESLSHST]![HIST_YEAR]))=2002));

This now returns the 7060 records I was looking for.
0
Computer101Commented:
PAQed, with points refunded (500)

Computer101
E-E Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.