I have a query that calculates a number of percentages based on data input into a table and i need to set a default value for these calculated values so that i dont get a #Num! or #Error thing if a proper value cannot be calculated. The SQL is kind of ugly but here it is:

SELECT [CRM2 Information].[CRM#2_Date], [CRM2 Information].[CRM#2_Defects], [CRM2 Information].[CRM#2_Admin], [CRM2 Information].[CRM#2_?s], [CRM#2_Defects]+[CRM#2_Admin]+[CRM#2_?s] AS [CRM#2_TotalSev], [CRM2 Information].[CRM#2_Comments], [Inspection Table].[Page Count], [CRM#2_Defects]/[Page Count] AS [%CRM#2_Def], [CRM#2_Admin]/[Page Count] AS [%CRM#2_Adm], [CRM#2_?s]/[Page Count] AS [%CRM#2_No Error], [CRM#2_Defects]/[CRM#2_TotalSev] AS [%CRM#2_DefType], [CRM#2_Admin]/[CRM#2_TotalSev] AS [%CRM#2_AdmType], [CRM2 Information].[CDRL ID]
FROM (Delivery INNER JOIN [Inspection Table] ON Delivery.[CDRL ID]=[Inspection Table].[CDRL ID]) INNER JOIN ([CRM1 Information] INNER JOIN [CRM2 Information] ON [CRM1 Information].[CDRL ID]=[CRM2 Information].[CDRL ID]) ON [Inspection Table].[CDRL ID]=[CRM1 Information].[CDRL ID];

i think if you use a nz function you will not get the #error

Use the NZ function to deal with null values. Plus, do not use special characters (% #, etc) in field or object names. That will only cause suffering to occur as Access will sometimes act funny because those have special meaning to Access. So, you really should rename your fields as soon as possible.

See here for more about special characters:
http://support.microsoft.com/?id=826763

how would one go about implementing an nz function? (i am an Access Novice)

I'd test each dividend, testing for Null and >0, using:

Iif(NZ([PAGE_COUNT])=0,-10000,[CRM#2_Defects]/[Page Count]) AS [%CRM#2_Def]

Or course, you'll want to put your own "custom" value in for -10000

I'd test each dividend, testing for Null and >0, using:

Iif(NZ([PAGE_COUNT])=0,-10000,[CRM#2_Defects]/[Page Count]) AS [%CRM#2_Def]

Or course, you'll want to put your own "custom" value in for -10000


How do i input my "custom" value as a percentage?

I followed the Nz([calculated expression], default value) format, but when i type in Nz([Calculated expression], 0.00%), i get an error due to the the 0.00%, and i want to input my default as a percentage.

How do i input my "custom" value as a percentage?

I followed the Nz([calculated expression], default value) format, but when i type in Nz([Calculated expression], 0.00%), i get an error due to the the 0.00%, and i want to input my default as a percentage.

It would be just Nz([Calculated expression], 0.00)

You aren't formatting any of the other values with the % sign in this query, so why are you going to do it for this one? However the others are being displayed, this would follow suit.

How do i input my "custom" value as a percentage?

I followed the Nz([calculated expression], default value) format, but when i type in Nz([Calculated expression], 0.00%), i get an error due to the the 0.00%, and i want to input my default as a percentage.

What do you want it to display when the dividend is a zero or a null?

It would be just Nz([Calculated expression], 0.00)

You aren't formatting any of the other values with the % sign in this query, so why are you going to do it for this one? However the others are being displayed, this would follow suit.


but i am trying to display these calculated values in a form formatted as percentages, and when i put in the Nz function, all my percent styles in my form turn into decimal style now

What do you want it to display when the dividend is a zero or a null?


0.00% in my form would be nice

set the format of the text box, or use the format function

Format ('your field you want formatted','Percent')

0.00% in my form would be nice

So, instead of -10000, use "0.00%" (including the double quotes)

So, instead of -10000, use "0.00%" (including the double quotes)


this works until i actually set another user entered piece of data in the same query as the value the caculated value is from.

The moment i do that within my form, i get the #Num! thing again in the calculated spot on the form.

ahh. i think i have found the problem.

When the value of the field used to determine the calculated value in the query is 0 (not a default null, but rather "automatically" set to 0) it tries to do 0/0 and thus the #Null! is produced.

ahh. i think i have found the problem.

When the value of the field used to determine the calculated value in the query is 0 (not a default null, but rather "automatically" set to 0) it tries to do 0/0 and thus the #Null! is produced.

That's what I've been trying to tell you.;)

That's what I've been trying to tell you.;)

ahh...yes that does make more sense going back and looking at your first comment :D


quick question...how does one make an "and" statement in SQL?

Thanks again!!

ahh...yes that does make more sense going back and looking at your first comment :D


quick question...how does one make an "and" statement in SQL?

Thanks again!!

Select *
From Mytable
WHERE FIELD1="FOO" AND FIELD2="BAR"

Select *
From Mytable
WHERE FIELD1="FOO" AND FIELD2="BAR"

Thanks again.

One more question...Exactly where in the SQL would i put the Format function you described earlier? Thanks

In design view, I think you can right click on the field and choose the format there or:

Format ([WhateverFieldorExpr], "Percent")

You can wrap the format() function around anything you want displayed in a certain manner. So, any field you want as percent you would do as the above example.