?
Solved

Querying a Decimal Field

Posted on 2003-11-26
7
Medium Priority
?
596 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

800 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