#Num! Error

SpikeyA

New member
Local time
Today, 11:29
Joined
Feb 11, 2015
Messages
7
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.
 
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
 
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.
 
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
 
I've attached what I've written and the Error report, not sure if this helps at all.
 

Attachments

  • Image 1.PNG
    Image 1.PNG
    5.9 KB · Views: 233
  • Image 2.PNG
    Image 2.PNG
    12.9 KB · Views: 244
you should not have Data in the table row for the free stock column
 

Users who are viewing this thread

Back
Top Bottom