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.
Criteria expressions
- Thread starter catlady
- Start date
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.
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.
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
Many thanks
catlady
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
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
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 
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
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?
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
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
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
Similar threads
