I'm Losing Decimal Places on Currency Values

[MichealShinn]

We've just started letting people sign up to pay specific dollar amounts in our Green Pricing program. My Access queries that pull the wind subscription quantity amounts are losing the amounts after the decimal point if that amount isn't an even number of dollars. For example, if that field holds the value 3.75, it gets pulled into my query as 3. 7.50 becomes 7. Does anybody know how I can make these queries pull the amount that's actually in the data warehouse table, instad of rounding or truncating it to a whole number?

Thanks for your help.

select wind_subscription_qty
from ACCOUNT, SERVICE_POINT
where ACCOUNT.Service_Addr_Number = SERVICE_POINT.Service_Addr_Number and
Account_Number = DELETED By me

7.50

This will show up in my query results as 7.

[RuralGuy]

It sounds like your wind_subscription_qty field is an integer rather than a field type that supports a decimal value. Single, Double, Currency

[MichealShinn]

Rural Guy,

Thanks for your reply.

In the data warehouse, the field is defined as decimal (10,2). My query is a make table query. When I run the query and a new table gets made, this field shows up as Number Long Integer, Auto decimal places. If I go into the design of the new table and change this field to decimal, it just changes to 7.00. Too late. Access pulls 7.50 out of the data warehouse and changes it to 7. Any idea how I can get it to stop doing that?

[RuralGuy]

A Long Integer will not accept any decimal value. If you just use a SELECT query on the warehouse, what value do you get?

[MichealShinn]

That was in my first message. I get 7.50.

[RuralGuy]

Sorry, missed that. So it is the MakeTable query that is not working as expected.

[RuralGuy]

Can you post the SQL for your MakeTable query, please?

[MichealShinn]

SELECT dbo_ACCOUNT.Account_Type_Code, dbo_ACCOUNT.Account_Number, dbo_ACCOUNT.NonStandard_Energy_User_Code, dbo_SERVICE_ADDRESS.Service_Addr_Number, dbo_SERVICE_POINT.Service_Point_Sequence_Number, dbo_SERVICE_POINT.Meter_Number, dbo_SERVICE_POINT.Wind_Subscription_Method, dbo_SERVICE_POINT.Wind_Subscription_Qty INTO tblActiveResGPT
FROM (dbo_ACCOUNT INNER JOIN dbo_SERVICE_ADDRESS ON (dbo_ACCOUNT.Account_Number = dbo_SERVICE_ADDRESS.Account_Number) AND (dbo_ACCOUNT.Service_Addr_Number = dbo_SERVICE_ADDRESS.Service_Addr_Number)) INNER JOIN dbo_SERVICE_POINT ON dbo_SERVICE_ADDRESS.Service_Addr_Number = dbo_SERVICE_POINT.Service_Addr_Number
WHERE (((dbo_ACCOUNT.Account_Type_Code)=' ') AND ((dbo_ACCOUNT.NonStandard_Energy_User_Code)="A") AND ((dbo_SERVICE_POINT.Wind_Start_Date) Is Not Null) AND ((dbo_SERVICE_POINT.Wind_End_Date) Is Null) AND ((dbo_SERVICE_POINT.Service_Point_End_Date) Is Null));

[RuralGuy]

A work around for now might be creating the table in advance and just append from the storehouse. Obviously Access must determine what the field type is on the fly here and it does not seem to be doing a very good job of it. I'll have to do some research on MakeTable queries.

[RuralGuy]

Here's a link Google found for me that you might be interested in: http://stackoverflow.com/questions/1...ke-table-query

[MichealShinn]

Rural Guy,

Thanks. I'll try that. Thanks for the time you've spent on this.

[RuralGuy]

You're very welcome.

[MichealShinn]

Unfortunately, appending the records to an existing table didn't work either. Access is ignoring whatever is to the right of the decimal point. If the data warehouse has 7.50 in it, I get 7.00. If the data warehouse has 3.75, I get 3.00. I've tried making the field a Double, and a Decimal. I've tried telling it to have two decimal places, and I've tried leaving it at Auto. I don't think the table design is the problem, though. For some reason, the query just can't see past the decimal point.

[RuralGuy]

Are you just trying the one field that is giving you trouble?

[MichealShinn]

I don't know exactly what you're asking. I changed the make table query to an append query and I'm appending all the fields. There is only one that's giving me problems.