Basic numeric property questions

[Skip Bisconer]

I have some calculated fields in a make table query and one field allows me to set the decimal places and the other doesn't. I have both fields set to General Number format. This has come up in different queries and I don't understand the dynamics. Thanks for looking.

This one does SP: Round([LP]+[EOQ],0)
This one doesn't SA: 0

 

[RainLover]

In your Table design the Field size should be Double or Decimal.

 

[Skip Bisconer]

I created the query to build and update a table so the query was made first Are what you telling me that if I run the query to make the table would I then change the number format in the table structure? But if so wouldn't the table format change each time I run the make table query?

 

[RainLover]

What I am saying is that you need to make the field Double.

Can you post your SQL

 

[Skip Bisconer]

Thanks for working with me Ned. Here is my query SQL:

SELECT [IN Part Master].INPM_PART AS Part, [IN Part Master].INPM_DESC AS Description, Left([IN Part Master]![INPM_PART],3) AS VCode, [IN Part Master].[INPM_1U/M] AS UOM, [IN Whse Quantities].INPM_BQOH AS QtyOnHand, [IN Whse Quantities].INPM_BQOO AS QtyOnOrder, [IN Whse Quantities].INPM_BQMN AS MinStock, [IN Whse Quantities].INPM_BQMX AS MaxStock, [IN Part Master].INPM_MBPR AS List, [IN Part Master].INPM_MBCS AS Cost, IIf(nz(

• =0),0,(
  • -[Cost])/
    • ) AS ListMargin, 0 AS AvgMonth, 0.35 AS LeadTime, 0.5 AS ReviewCycle, 0 AS SA, 0 AS SAPercent, 0.25 AS CarryingCost, 4 AS ReplenishmentCost, Round([LP]+[EOQ],0) AS SP, IIf([LP]<1,0,Round(Sqr([OP]),1)) AS EOQ, Round(24*[ReplenishmentCost]*[AvgMonth],1) AS Calc1, Round(0.25*[cost],1) AS Calc2, IIf([Calc2]<>0,Round([Calc1]/[Calc2],1),0) AS Calc3, Round(([OP]+[AvgMonth])*[ReviewCycle],1) AS LP, Round(([AvgMonth]*[LeadTime])+[SA],1) AS OP, [SP]-([QtyOnHand]+[QtyOnOrder]) AS RecBuy INTO InventoryBuyerAssistanceLoc1
      FROM [IN Part Master] INNER JOIN [IN Whse Quantities] ON [IN Part Master].INPM_PART = [IN Whse Quantities].INPM_PART
      WHERE ((([IN Part Master].INPM_PCLS) Between "199" And "965") AND (([IN Whse Quantities].INPM_BWID)="1") AND (([IN Part Master].INPM_ACIN)="Y"))
      ORDER BY [IN Part Master].INPM_PART;

 

[RainLover]

Don't thank me yet. I havn't solved your problem as yet.

I am not an expert at make table so let me do some research and I will get back to you shortly.

 

[Skip Bisconer]

Thanks for your help

 

[RainLover]

Skip

Access will not accept that 0 is anything but an Integer. If it was 0.01 it would create a Field of Decimal.

I have seen similar problems in queries before.

What you have to do is to trick access into thinking that this field is a decimal.

I created an IIF statement. IIF([PickaField] = [PickAnotherField],1.23,0)

The fields that you select cannot be equal so it always returns 0, but as access thinks the answer could be 1.23 it will make the field Double.

The other choice is make the Field SA:1.23 then run an update query to change the value to 0

Maybe someone else has a better solution

 

[Skip Bisconer]

Thanks Ned, I will give that a try.

 

[RainLover]

This would be a better statement.

IIF("A" = "B",1.23,0)

 

[RainLover]

Skip,

try this one.

SA: CDbl(Nz(0,0))

 

[gemma-the-husky]

try explicitly 0.00, rather than just 0

see if that coerces the field to a real number format.

--------
alternatively after you make the table, just alter the field to make it a double