:confused: can anyone pls help - I am trying to write a criteria expression in a query to add together the values of 2 fields giving the total in another one. I have been reading up on Nz and IIf, etc and have tried loads of different things but only end up with 0s or being told the expression is wrong. Pls help me understand how these Nz etc things work - it's driving me nuts and I'm wasting hrs and hrs trying to suss it out.

The Select statement of the query in SQL View should read like this (you can directly type/edit in SQL View of a query):

SELECT ...
FROM ...
WHERE nz(Field1) + nz(Field2) = Field3


The expression in the Where clause is the criteria.


nz() should be used directly on the sources of null values (here the field names), not on the calculated result:

so nz(Field1) + nz(Field2) is a correct use of nz(),
whereas nz(Field1 + Field2) is not.

You probably need to use the "full" syntax for the Nz() finction in a query:

nz(Field1,0)

NZ stands for null to zero. However, the data type of the field actually determines whether nulls will be replaced by zeros or zero length strings. You can also provide your own replacement value.

Nz(TextFld, "abc")

Nz(NumericFld, 123)

I personally, never omit parts of statements just to save typing. It is much better for people reading the code in the future to not have to figurer out what is happening.

Pat,

Thanks for the elaboration. You got a good point there about reading the code in the future.
Jon

Thanks for your replies, but I'm not sure if I am just being really thick or what but am having no joy. The fields I am working with are currency and I have tried = nz(MAT ENTR)+nz(MAT SAL) , nz([mat entr],0)+nz([mat sal],0) = mat total, with and without inverted commas around the 0 and god knows how many more combinations, but I only come up with the entries that are 0 totals. Any other suggestions - I suppose I can do totals with currency fields - reasonable no?

Many thanks

catlady

The SQL should look something like:

Select fld1, fld2, ...., nz([mat entr],0)+nz([mat sal],0) As [mat total]
From YourTable;

It is poor practice to use spaces or special characters in the names of fields/tables and other objects. When you do, it is imperative that you surround the offending name with square brackets.

Hi again - still not having any luck. I keep getting things like the following - "Expression contains invalid syntax" "You omitted an operand or operator, you entered an invalid character or comma,or you entered text without surrounding it in quote marks. I have tried all ways.

I have taken your advice and got rid of the spaces now in the names of the fields.

Can I start step by step to see if I am in the right place -
1. Open query in design view
2. In the space "criteria" that's where I have to put in the expression.
3. Under which field do I put the expression - the "total" field or under "MATENT" and/or "MATSAL".

4. The actual fields are currency and some have quantities and others 0,00€.

Could there be something I have missed putting into the fields?
Thus getting the error messages all the time?

I have tried using the "build button", typing it in directly with all different combinations as put above in your other notes but keep getting error, error, error. It's driving me nuts!!!!!!!!!

I must be missing out something somewhere surely- but what????

Thanks once again, regards

A very frustrated catlady

In QBE view, go to an empty column, and in the Field cell, enter something that looks like:

MatTotal:nz([matentr],0)+nz([matsal],0)

MatTotal is the name of the calculated field, the colon separates the field name from the calculation.

Hi again - still no luck, no matter what combination I put in I keep getting the warning messages that it is not right.

What is QBE - I assume that it was design view - expression building. Can calculations be put into all types of queries? - Maybe there is something wrong with the query itself altho' I can't see what.

If you have any other ideas they would be gratefully received. But pls put them in laymans terms as all I have learnt about Access I have picked up thro trial and effort and so don't understand much in the way of computer slang so to speak.


All you help is appreciated although I am not getting anywhere.

catlady:confused: :rolleyes:

Why don't you post the SQL.

How do I do that?

Open the query in SQL view. Copy the text and paste it here.

SELECT [MAT Cobros].NOMBRE, [MAT Cobros].[Nº PAX], [MAT Cobros].CUIDAD, [MAT Cobros].ENTRADA, [MAT Cobros].MATENT, [MAT Cobros].SALIDA, [MAT Cobros].MATSAL, [MAT Cobros].MATTOTAL
FROM [MAT Cobros];


Is this what you mean?

Yeah,

that's what Pat meant.

What happens when you run this:

SELECT [MAT Cobros].NOMBRE, [MAT Cobros].[Nº PAX], [MAT Cobros].CUIDAD, [MAT Cobros].ENTRADA, [MAT Cobros].MATENT, [MAT Cobros].SALIDA, [MAT Cobros].MATSAL, nz([matent],0)+nz([matsal],0) AS MATTOTAL
FROM [MAT Cobros];

RV

Hi RV

comes up: Syntax error en query expression nz([matent],0) etc etc -quoting all the changes I have made.

catlady

Open any code module. If you don't have one, create a new empty module. Go to Tools/References and look for a missing reference. There are many posts here regarding missing references.

:D :D

SUCCESS!!!!!!!

I have sussed out the problem at long last. It was all to do with the field MATTOTAL - I had put MATTOTAL in the table but with no values and so it could not do any calcualtion in the query. I now have put a field TOTAL into the query which doesn't relate to anything and then have put in the expression as stated by RV in the SQL and - voila - it has worked.

Thank you for all your help - all the hours spent on something so simple.

Gratefully - Catlady