Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Querying a Decimal Field

Posted on 2003-11-26
7
Medium Priority
?
607 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

886 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