#Num! Error (1 Viewer)

[SpikeyA]

Hi,

I am in the process of developing a stock related database. I have imported the data as a linked table. (The bank end of the Excel table is a CSV File which comes from a further back end system).

From the linked table I am running query but have been looking to add manual formulas.

Having found the "Field" that I want to create I am using the following :

Free Stock: [Free Stk Qty]/[Min]

The problem is, on items where stock is 0 it details #Num!

Is there a way to set the field as above but detail the #Num! field data as a "0"?

Thanks for your help in advance.

Happy to provide more details/information if required.

 

[CJ_London]

try

Free Stock: iif(nz([Min],0)=0,0,[Free Stk Qty]/[Min])

Note that min is a reserved word and its use can have unexpected consequences so recommend you rename it to something else like MinStock

 

[SpikeyA]

Thanks CJ, however I think what you've stated is my problem, one of my fields is set to min from a linked table. Is there anyway to Edit the Linked Table or append the field names of the data imported?

The following comes up at the moment when I click datasheet view:

Syntax Error (Comma) in query expression 'Data.[Iif(Nz([Min],0=0,0,[Free Stk Qty]/[Min])]

Have entered exactly as you detailed.

 

[CJ_London]

Have entered exactly as you detailed.

don't think so - you've got Data rather than FreeStock, a dot rather than a colon and square brackets where I have none.

You can't edit the linked table - although you can go into the source file and edit there.

if importing you can then change the name in the destination table

 

[SpikeyA]

I've attached what I've written and the Error report, not sure if this helps at all.

 

[CJ_London]

you should not have Data in the table row for the free stock column

 

[SpikeyA]

Perfect thank you!