putting 0 where field is empty
- Thread starter wilkob
- Start date
Nz() works with both text fields and numeric fields.Bigmo2u said:
The following all return text:-
Nz([FieldName], "N/A")
Nz([FieldName], "Nil")
Nz([FieldName], "0")
Nz([FieldName], 0)
Nz([FieldName], "Nil")
Nz([FieldName], "0")
Nz([FieldName], 0)
The following return numeric zeroes:-
Nz([NumericField]) +0
Nz([NumericFieldA]) + Nz([NumericFieldB])
.Nz([NumericFieldA]) + Nz([NumericFieldB])
Last edited:
I tried these where I want the query to count the records. it looks like this:
Field: Countof SSN
TABLE: Disbo_dns
Totals: Count <====== is this where I put the Nz([FieldName], 0)
Sort:
Show:
Criteria:
or:
I am a little lost here.
I just want to count the records and if no records are resident then I want a zero return value.
Thanks for your help.
Field: Countof SSN
TABLE: Disbo_dns
Totals: Count <====== is this where I put the Nz([FieldName], 0)
Sort:
Show:
Criteria:
or:
I am a little lost here.
I just want to count the records and if no records are resident then I want a zero return value.
Thanks for your help.
I think that my problem is that this is a query on top of a union query that isn't returning any data to count. How would I correct this? union query is listed below:
This the query on that does the counting:
PHP:
SELECT SSN, [Disbo DN Date], [Order Type] FROM [M00091 Supplemental 1 Claims]
WHERE [DN Date] Between [Type the beginning date MM/DD/YYYY] And [Type the ending date MM/DD/YYYY]
UNION SELECT SSN, [Disbo DN Date], [Order Type] FROM [M00091 Supplemental 2 Claims]
WHERE [DN Date] Between [Type the beginning date MM/DD/YYYY] And [Type the ending date MM/DD/YYYY]
UNION SELECT SSN, [Disbo DN Date], [Order Type] FROM [M00091 Supplemental 3 Claims]
WHERE [DN Date] Between [Type the beginning date MM/DD/YYYY] And [Type the ending date MM/DD/YYYY]
UNION SELECT SSN, [Disbo DN Date], [Order Type] FROM [M00091 Supplemental 4 Claims]
WHERE [DN Date] Between [Type the beginning date MM/DD/YYYY] And [Type the ending date MM/DD/YYYY]
UNION SELECT SSN, [Disbo DN Date], [Order Type] FROM [M00091 Supplemental 5 Claims]
WHERE [DN Date] Between [Type the beginning date MM/DD/YYYY] And [Type the ending date MM/DD/YYYY]
ORDER BY SSN;This the query on that does the counting:
PHP:
SELECT Count([All Sup Claims MCRATS DN Monthly].SSN) AS CountOfSSN
FROM [All Sup Claims MCRATS DN Monthly]
GROUP BY [All Sup Claims MCRATS DN Monthly].[Order Type]
HAVING ((([All Sup Claims MCRATS DN Monthly].[Order Type])="PRC"));
neileg
AWF VIP
- Local time
- Today, 23:35
- Joined
- Dec 4, 2002
- Messages
- 5,975
A few points:
Your union query suggests that the basic design is flawed. You shouldn't need 5 claims tables. You should have one table with a field that indicates the claim number.
Like I said before, you are trying to count things that don't exist.
The technique that enables you to return null records is to have another table (or query) that has all the possible occurances of the identifyer you are trying to count on. Create a left join with this data and the data you are counting. This will then return a record for each occurance even if there is no record in the right hand table. Does this make sense?
And finally, it is better practice to have no spaces in your object names. Makes life easier in the long run.
Your union query suggests that the basic design is flawed. You shouldn't need 5 claims tables. You should have one table with a field that indicates the claim number.
Like I said before, you are trying to count things that don't exist.
The technique that enables you to return null records is to have another table (or query) that has all the possible occurances of the identifyer you are trying to count on. Create a left join with this data and the data you are counting. This will then return a record for each occurance even if there is no record in the right hand table. Does this make sense?
And finally, it is better practice to have no spaces in your object names. Makes life easier in the long run.
ColinEssex
Old registered user
- Local time
- Today, 23:35
- Joined
- Feb 22, 2002
- Messages
- 9,451
and the database structureBigmo2u said:
Col
I got the query to work with a little bit of rigging it.
As for the structure of the DB I am always working on it. As for the number of tables I have? I have yet to figure out a way to capture all the data, dates and other info that specific to a Single claim (members could submit up 48 seperate claims). If you would like to help with the structure of my DB please PM me and maybe I can explain it in detail and give you a sample of what it looks like as a whole and why I think (which isn't always correct) I need it setup this way.....
Thanks guys your help is greatly appreciated.
As for the structure of the DB I am always working on it. As for the number of tables I have? I have yet to figure out a way to capture all the data, dates and other info that specific to a Single claim (members could submit up 48 seperate claims). If you would like to help with the structure of my DB please PM me and maybe I can explain it in detail and give you a sample of what it looks like as a whole and why I think (which isn't always correct) I need it setup this way.....
Thanks guys your help is greatly appreciated.
Last edited:
neileg said:
Okay I have sat here tonight reading over this forum trying to digest all the info I can and the lite finally turn on. neileg your idea finally makes sense to me. Sometimes you have to hit me with a brick to make me understand what you mean and now i understand my flaw. Now I to figure out exactly how to extract all the data I currently have store in a ton of tables into just 4 tables. I am going to fix the spaces in my naming scheme as suggested.
Last edited:
neileg
AWF VIP
- Local time
- Today, 23:35
- Joined
- Dec 4, 2002
- Messages
- 5,975
I suspect you have a background in using spreadsheets where essentially, you store and view the data in the same place. In a database, tables are for storing data, queries for manipulating data and forms and reports for viewing data. How the tables look may be a long way from how the data is eventually displayed.
