Expression field name 00 switching to 0

[meagain]

Why is a query expression field name of "00-Sales" changing to "0-Sales" when I set up a report based on the query?

This field together with "70-Sales" and "20-Sales" are totalled and used to calculate other fields.
The query opens fine with correct results, but when I create and open a report based on the query,
I am prompted to "Enter parameter value 00-Sales". When I go back into the query design, the field name has
now changed to "0-Sales". Does anyone know why this is happening?

Thanks so much

 

[SkipVought]

Hi,

It all depends how you assign your variables.

Please post your code.

Skip,

_[red][/red]

 

[dhookom]

Why do your field names begin with numbers? It looks to me like you are attempting to subtract [Sales] from 0.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[meagain]

Thank you both for your responses.

Duane, the field represents sales for a particular warehouse. There are three warehouses 00, 70 and 20, but I'm only encountering a problem with the 00 changing 0. Even when I move the "00" to suffix the "Sales", I run into the same situation.

Skip, Here is the query code........
SELECT [INVENTORY DAYS OUT].Type, [INVENTORY DAYS OUT].I_PROD_CODE, [INVENTORY DAYS OUT].I_PART_NO, [INVENTORY DAYS OUT].I_DESCRIPTION, [INVENTORY DAYS OUT]![00-11 SALES] AS [00-SALES], [INVENTORY DAYS OUT]![70-11 SALES] AS [70-SALES], [INVENTORY DAYS OUT]![20-11 SALES] AS [20-SALES], [00-SALES]+[70-SALES]+[20-SALES] AS [Total Daily Sales], [INVENTORY DAYS OUT].[00-OH], [INVENTORY DAYS OUT].[70-OH], [INVENTORY DAYS OUT].[20-OH], [00-OH]+[70-OH]+[20-OH] AS [Total Inventory OH], Format(IIf([00-OH]=0,"None OH",IIf([00-SALES]=0,"No Sales",[00-OH]/[00-SALES])),"0") AS [00-DO], Format(IIf([70-OH]=0,"None OH",IIf([70-SALES]=0,"No Sales",[70-OH]/[70-SALES])),"0") AS [70-DO], Format(IIf([20-OH]=0,"None OH",IIf([20-SALES]=0,"No Sales",[20-OH]/[20-SALES])),"0") AS [20-DO], Format(IIf([Total Inventory OH]=0,"None OH",IIf([Total Daily Sales]=0,"No Sales",[Total Inventory OH]/[Total Daily Sales])),"0") AS [TOTAL-DO]
FROM [INVENTORY DAYS OUT]
ORDER BY [INVENTORY DAYS OUT].Type, [INVENTORY DAYS OUT].I_PROD_CODE;

Let me know if you see anything. Much appreciated!!

Lori

 

[dhookom]

A couple observations that I have learned from experience:
1) Normalize. Don't store data values (warehouse codes) in field names. A warehouse code should only be stored as a value in a field.
2) I never use a derived field/column name in another calculation in a query. For instance, I don't use [blue][00-SALES][/blue] when I should be using [blue][00-11 SALES][/blue]. The same goes for the totals.
3) I don't apply formats intend for numbers to text values.
4) I never begin and names with numbers.

I have attempted to re-write the SQL with some line breaks added for clarity.

SELECT Type, I_PROD_CODE, I_PART_NO, I_DESCRIPTION, 
[00-11 SALES] AS [00-SALES], 
[70-11 SALES] AS [70-SALES], 
[20-11 SALES] AS [20-SALES], 
[00-11 SALES]+[70-11 SALES]+[20-11 SALES] AS [Total Daily Sales], 
[00-OH], [70-OH], [20-OH], 
[00-OH]+[70-OH]+[20-OH] AS [Total Inventory OH], 
IIf([00-OH]=0,"None OH",
  IIf([00-11 SALES]=0,"No Sales",
    Format([00-OH]/[00-11 SALES],"0"))) AS [00-DO], 
IIf([70-11 SALES]=0,"None OH",
  IIf([70-11 SALES]=0,"No Sales",
    Format([70-OH]/[70-11 SALES],"0"))) AS [70-DO], 
IIf([20-11 SALES]=0,"None OH",
  IIf([20-11 SALES]=0,"No Sales",
    Format([20-OH]/[20-11 SALES],"0"))) AS [20-DO], 
IIf([00-OH]+[70-OH]+[20-OH]=0,"None OH",
  IIf([00-11 SALES]+[70-11 SALES]+[20-11 SALES]=0,"No Sales",
    Format(([00-OH]+[70-OH]+[20-OH])/([00-11 SALES]+[70-11 SALES]+[20-11 SALES]),"0"))) AS [TOTAL-DO]
FROM [INVENTORY DAYS OUT]
ORDER BY Type, I_PROD_CODE;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]