Solved

Querying a Decimal Field

Posted on 2003-11-26
7
585 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
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

860 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