Null field kills the row on a form

[Thompyt]

Hi, I am new to Access and have been making up a dB for work. I developed a form for a single layer dB where the form is as:




Design View



The Title columns are text boxes. The left is the category row title and the right parts of the rows ate the totals of the Text box headers. If I have all the categorys filled in you will get the complete design. Where I am missing one total in one of the categories, the complete row id deleted.

This is an example of one query in SQL format I use to break down the data:

SELECT Count([Change Request].[Final VOTE]) AS [CountOfFinal VOTE], [Change Request].[Final VOTE]
FROM [Change Request]
WHERE ((([Change Request].Type)<>"Software") AND (([Change Request].[Action Complete])=Yes) AND (([Change Request].Date_Closed) Between DateSerial(Year(Date()),Month(Date())+1,-7) And DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY [Change Request].[Final VOTE], [Change Request].[CATEGORY2 (LEVEL 3 ONLY)]
HAVING ((([Change Request].[CATEGORY2 (LEVEL 3 ONLY)])=1));

I have added an extra 30 records to make the dB do what I want. Each record sets up the form, and I subtract 1 from each during the process with an expression in a rollup query of the 6 queries.

Example: Count LVL1: [Count Level 1].[CountOfFinal VOTE]-1

I also have 6 other queries where I get the end sum for the footer row rolled into one query.

Example: TTL Lvl 2: CountOfFinal VOTE

Secondary Issue

At this point if I use the date resriction I get the same output on the form - it starts missing rows. This is due to the Date_Closed being null and I cannot figure out how to do an if then macro or SQL or VBA to have it filled in. IE: IIF([Change Request].[Change_Requested]="Do not delete") THEN ([Change Request].[Date_Closed]=Date().

That would fix the issue. BUT

Primary Issue

If this issue is fixed then the Secondary issue is moot. The real issue for me is where the count causes the row to be null in that query.

It is where [Change Request].[Final VOTE] is null and the rest of the SQL is true:

WHERE ((([Change Request].Type)<>"Software") AND (([Change Request].[Action Complete])=Yes) AND (([Change Request].Date_Closed) Between DateSerial(Year(Date()),Month(Date())+1,-7) And DateSerial(Year(Date()),Month(Date())+1,0)))


Countof becomes null or Final Vote becomes null. What can I do to correct this?

Any assistance would be appreciated.

Thanks
Thompyt

[Thompyt]

For your edification. Remember I'm a novice, don't tear me up too baldly :P If you see a quicker way please show me.

Thanks

[Thompyt]

I gather no one has an idea?

HICON approach:
I have 6 rows od string data, each row is different. I count the rows by the string data. Where one string data is missing the count =0, but the string data name is missing. How can I avaoid this null in the string data name?

Thanks

[burrina]

Good Luck With Your Project.

[June7]

Burrina, you reference Nz but show IsNull and don't think the If Then syntax quite right.

Nz() function to handle nulls:

Nz([fieldname],0)

[Thompyt]

All I changed the dB where the query is cross tabbed. SQL is:
TRANSFORM Count([Change Request].CR_ID) AS CountOfCR_ID
SELECT [Change Request].LEVEL1
FROM [Change Request]
WHERE ((([Change Request].[Final VOTE])=Nz([Final Vote],0)))
GROUP BY [Change Request].LEVEL1
PIVOT [Change Request].[Final VOTE];


I get what I want except where the field equals null I was at first getting all the columns plus one called <>. This would put a 1 in the row where the field was null in the other columns. 1=1record. I added NZ([Final Vote], 0) and I get rid of the column <>. The field remains a blank though and does not change to 0. What would be the next step?

Also this crosstab removed 5 other queries. (positive)

Is there a way to sum the totals? I tried to by changing "count" to "sum", but funky numbers.

thanks

[June7]

Query in datasheet view, click Totals from the ribbon Home tab. Select Sum on the Totals row under column you want to sum.

I don't understand null issue. None of the records have null in [Final VOTE] field.

[Thompyt]

I understand the sum in the datasheet view, but would like to have that sum in the report. I didn't want to set up 5 or 6 more queries. I took a Level 3 Cat 1 Deferred out. Look at the Change Request Rollup Report, it shows a blank field. Don't worry about the numbers tallying. They are sort of place holders for now.


Change Requests V5.zip

[June7]

Textboxes in report footer section with expression: =Sum([Approved])

So what do you want to do with records with null value - exclude them or show alternative value such as 0?

[Thompyt]

I want the value to equal 0. The =Sum([Approved]) gives an error#

[June7]

The expression works for me. Did you put textbox in report footer, not in page footer?

Try Nz() function in textboxes in Detail section: =Nz([Deferred],0)

[Thompyt]

It worked by putting the NZ function in the Reports field control source. BUT if I take out all the records, I get the error "MS Access does not recognize CR-Crosstab as a valid Field name or expression. I need to be able to have all zeros without having to put in a math function. Later I am going to use the Date_Closed Field to limit the records to a weeks time.

I was putting the totals in the page header, thus why the =Sum() didn't work. I have that organized now.

[June7]

Building a report to run perpetually based on a CROSSTAB is difficult because of their dynamic nature. No records means no fields are generated and the report fails.

Review http://allenbrowne.com/ser-67.html

[Thompyt]

Building a report to run perpetually based on a CROSSTAB is difficult because of their dynamic nature. No records means no fields are generated and the report fails.

Review http://allenbrowne.com/ser-67.html

That's sucks, I tried this method because I can't get the string to appear for Final Vote through regular queries. Although the crosstab does cause me to have 10 less queries.

I tried =NZ([fieldname], [fieldname]) to get the lost value, but that doesn't phase it at all.

[June7]

The Nz() would not reference the same fieldname as alternate value

=Nz([fieldname],0)

But again, if there are no records in the dataset, the CROSSTAB cannot generate the fields, unless maybe the Specify Column Headings example in Allen Browne's article will force columns to generate even if there are no records. I've never tested it.

You might find this thread of interest and the example db in post 60 https://www.accessforums.net/forms/g...tml#post239648

Also, http://forums.aspfree.com/microsoft-...ry-322123.html