Calculated Field in Query does not work-Help!!!

[HelenLLL]

I have a
Order Table:
Order ID(Autonumber)
Client ID(Text)
Client Name(Short Text)
Install Gross Rate (Currency)
Install Discount Given in % (Number)
Install Discount Given in GBP (Currency)

Then I designed a Query to calculate the Install Net Rate
Query
I selected
Client ID
Client Name
Order ID
Expression: [Install Gross Rate]*(1-[Install Discount Given in %]/100)-[Install Discount Given in GBP]

I run the query, but only ONE order calculated correctly, rest of Orders returned blank row.:banghead:

 

[Mihail]

The setup of your DB is wrong.
Read this: Database Normalization and Table Structures - Microsoft Access / VBA

 

[plog]

Are any of the fields that comprise the Expression Null?

 

[HelenLLL]

If no discount given:
Order Table left blank(unfilled), Default is as NIL.

 

[plog]

There you go.

Look into the NZ function (http://www.techonthenet.com/access/functions/advanced/nz.php) to force it to a value. Otherwise the function doesn't have any idea how to process it.

 

[HelenLLL]

Thank you. I now understand the reason.
The database is open to everyone to use. What do I do to force Data Entry person to input Zero value, rather then leave it blank?

 

[plog]

You can make it a required field in the table and you can also set the default value there to 0.

 

[HelenLLL]

I try to avoid setting up these fields as 'Required' as this might be asking too much for some people.
I have set the 'Default Value=0', but still did not work. - Any Idea?

 

[bob fitz]

I have set the 'Default Value=0', but still did not work. - Any Idea?

Setting the default value property will only have an affect on new records. For existing records you will need to run an update query to change the value of all records where the value is Null to 0 or use the Nz() function as plog recommended in post #5

 

[HelenLLL]

Hi Bob,

I am a learner for Access. I could not understand Nz() function by reading the formula on line. Could you write it for me? - I want to set up this in a field called 'Install Discount'.

Many thanks

 

[bob fitz]

Read this link: http://office.microsoft.com/en-gb/access-help/nz-function-HA001228890.aspx
Which part of
[Install Gross Rate]*(1-[Install Discount Given in %]/100)-[Install Discount Given in GBP]
is returning Null values.