getting decimal places with a make table query

[DJWheezyWeez]

I'm very new to Access but I'm using Access 2007. Maybe I'm doing it wrong but for some reason I can't get decimals in my tables unless I add them manually. In my make table query, I change the Format for the field properties to either Currency or Percent (whatever I'm trying to do). When I run the query, the data I tried to make for Currency format doesn't come out right. For example, the number -22,052.6 in the currency format should come out ($22,052.60) but doesn't change. And for the Percent, it comes out as an interger and doesn't have hidden places. When I use that field to do calculations, It screws up all my data.

Is there any way to get the correct data type when running the query rather than having to manually change the data types in each table? I don't know if I accurately explained my problem but if it makes sense, any suggestions are welcome.

-DJWW

 

[genomon]

In the QBE, have you tried MyFieldName:Format([field name], "$##,###.##") ?

Illegitimi non carborundum.

 

[DJWheezyWeez]

Could you be more specific? As in what does QBE mean and where would that be in Access? As I said I'm very new to this.

 

[genomon]

QBE is Qery Builder Environment. To get there, open your query in design view. Highlight the field name you want to format. Then make it look like this:
FieldName:Format([FieldName],"$##,###.##")

You may have to add # signs if your number is > 99,999.99.
Also I forget if the last two # might have to be zeroes, in case your field value has no decimals.

Illegitimi non carborundum.

 

[PHV]

What is your actual SQL code and which fields in that make table query exhibit the mentioned behaviour ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DJWheezyWeez]

genomon, I thank you for your help but am having some trouble. I'm probably doing something wrong but I'm getting the error "Data type mismatch in criteria expression." I'm assuming there's a data type somewhere that needs to be changed but I don't know where. Or maybe I'm doing this all wrong. In the field name I put: TxnValue:Format([PUB_PV_InvHistory].[TxnValue],"$##,###.##"). For some reason when I try to run it, it changes $##,###.## to $#,###.## taking out the ten thousands place '#'. I don't know if that's relavent but I thought I would bring it up. Any thoughts?



PHV, here is my SQL code before genomon's suggestion:
SELECT PUB_PV_Customer.CustName, PUB_PV_InvHistory.SOrderNum, PUB_PV_InvHistory.SOrderLineNum, Sum(PUB_PV_InvHistory.TxnValue) AS SumOfTxnValue, PUB_PM_Item.ItemTypeCode, PUB_PM_Item.BrandCode INTO costs
FROM (PUB_PV_InvHistory INNER JOIN PUB_PM_Item ON (PUB_PV_InvHistory.ItemCode = PUB_PM_Item.ItemCode) AND (PUB_PV_InvHistory.CompNum = PUB_PM_Item.CompNum)) INNER JOIN PUB_PV_Customer ON (PUB_PM_Item.CompNum = PUB_PV_Customer.CompNum) AND (PUB_PM_Item.CustCode = PUB_PV_Customer.CustCode)
WHERE (((PUB_PM_Item.ProdGroupCode)="Flexo" Or (PUB_PM_Item.ProdGroupCode)="WebOff" Or (PUB_PM_Item.ProdGroupCode)="Offset") AND ((PUB_PV_InvHistory.TxnDate) Between #5/1/2009# And #5/31/2009#) AND ((PUB_PV_InvHistory.TxnType)=8 Or (PUB_PV_InvHistory.TxnType)=9))
GROUP BY PUB_PV_Customer.CustName, PUB_PV_InvHistory.SOrderNum, PUB_PV_InvHistory.SOrderLineNum, PUB_PM_Item.ItemTypeCode, PUB_PM_Item.BrandCode;

And if it could help, here it is after when I get the data type error:
SELECT PUB_PV_Customer.CustName, PUB_PV_InvHistory.SOrderNum, PUB_PV_InvHistory.SOrderLineNum, Sum(Format([PUB_PV_InvHistory].[TxnValue],"$#,###.##")) AS TxnValue, PUB_PM_Item.ItemTypeCode, PUB_PM_Item.BrandCode INTO costs
FROM (PUB_PV_InvHistory INNER JOIN PUB_PM_Item ON (PUB_PV_InvHistory.ItemCode = PUB_PM_Item.ItemCode) AND (PUB_PV_InvHistory.CompNum = PUB_PM_Item.CompNum)) INNER JOIN PUB_PV_Customer ON (PUB_PM_Item.CompNum = PUB_PV_Customer.CompNum) AND (PUB_PM_Item.CustCode = PUB_PV_Customer.CustCode)
WHERE (((PUB_PM_Item.ProdGroupCode)="Flexo" Or (PUB_PM_Item.ProdGroupCode)="WebOff" Or (PUB_PM_Item.ProdGroupCode)="Offset") AND ((PUB_PV_InvHistory.TxnDate) Between #5/1/2009# And #5/31/2009#) AND ((PUB_PV_InvHistory.TxnType)=8 Or (PUB_PV_InvHistory.TxnType)=9))
GROUP BY PUB_PV_Customer.CustName, PUB_PV_InvHistory.SOrderNum, PUB_PV_InvHistory.SOrderLineNum, PUB_PM_Item.ItemTypeCode, PUB_PM_Item.BrandCode;

In this particular make table query, the only field I'm trying to format is the TxnValue field.

 

[PHV]

The Format function returns a string and thus I'd don't follow genomon's suggestion.
I'd replace this:
Sum(PUB_PV_InvHistory.TxnValue) AS SumOfTxnValue
with this:
CCur(Sum(PUB_PV_InvHistory.TxnValue)) AS SumOfTxnValue

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DJWheezyWeez]

Thank you PHV! Your idea worked. I applied your method to my other 4 tables as well but I have one last thing to deal with - the percentage. Since there is no CPer, I used CDbl because that's what I used when I changed them all manually. When I did it manually, after I changed the Field Size to Double, I changed the Format to Percent. I'm assuming there's some way to do this in the SQL code and I just don't know the syntax of it. Right now with CDbl, I'm getting something like a 15 decimal place number when what I really want is something like ##.##%, only showing two decimal places but still have all 15 places for calculations so it doesn't round. Any of this possible?

-DJWW