Solved

Querying a Decimal Field

Posted on 2003-11-26
7
589 Views
Last Modified: 2010-05-18
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.
0
Comment
Question by:trimactechie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 39

Expert Comment

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

steve
0
 

Author Comment

by:trimactechie
ID: 9827484
>=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
 
LVL 32

Expert Comment

by:jadedata
ID: 9827551
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:trimactechie
ID: 9827700
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
 

Author Comment

by:trimactechie
ID: 9828612
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
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 9842528
PAQed, with points refunded (500)

Computer101
E-E Admin
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question