Null field kills the row on a form

[burrina]

Yes, it should have been something like:

If Len(Nz(Me!SomeField,"")) = 0 Then

Hmm,I must not be perfect after all!

[Thompyt]

OK Gents, I have minimized the amount of record that must be stored. 5 each to keep the report open and all 0's. Thanks for the clarification on where to put the NZ function.

Now how would I set the Closed_Date to set the 5 records to current date. I want to set a date limter for a week, such as:
Between DateSerial(Year(Date()),Month(Date())+1,-7) And DateSerial(Year(Date()),Month(Date())+1,0)

Basically I think it is an IIF function: IIf([Change Request].[Change_Requested]="Do not delete", [Change request].Date_Closed]=Date(), [Date_Closed])

I have this in a combined query for the report:
SELECT CR_Crosstab.LEVEL1, [CR_Crosstab].[HB Totals]-1 AS [Total HB], [CR_Crosstab].[Approved]-1 AS Approved, [CR_Crosstab].[Deferred]-1 AS [Deferred], [CR_Crosstab].[Denied]-1 AS Denied, [CR_Crosstab].[Pending]-1 AS Pending, [CR_Crosstab].[Withdrawn]-1 AS Withdrawn, [Total CRS].[CountOfLEVEL1]-5 AS [Total CRs], [Total HB].[CountOfLEVEL1]-4 AS HBTotal
FROM CR_Crosstab, [Total CRS], [Total HB]
GROUP BY CR_Crosstab.LEVEL1, [CR_Crosstab].[HB Totals]-1, [CR_Crosstab].[Approved]-1, [CR_Crosstab].[Deferred]-1, [CR_Crosstab].[Denied]-1, [CR_Crosstab].[Pending]-1, [CR_Crosstab].[Withdrawn]-1, [Total CRS].[CountOfLEVEL1]-5, [Total HB].[CountOfLEVEL1]-4;

[qa12dx]

dont use null fields. have a default value. if you update the default value now, it will affect new recs only. so, run a update query on null fields to default value.

null is very hard to work with, in access

[June7]

I don't always use default value because no data has meaning to me. My database is to store laboratory test data. I can't store a default value because a value means data was obtained and if data really was not obtained, that is misleading. Even a 0 or a negative sometimes can be a valid test result. So I allow nulls but I don't allow empty strings.

However, Thompyt, I don't really understand your last post.

CROSSTAB handles filter parameters different from other queries. Review Allen Browne article again.

[Thompyt]

I've found the work around for the null field issue with the NZ function in the report. That's all good. The date issue with the Date_Closed field, I think I have found a work around on that as well. I can run an update query through a macro to run it when opening the report. It will update those specific rows that meet certain criteria. I do not want to set a default value for the Date_Closed field. This happens when the action is closed by the user.

[Thompyt]

I fixed the date with Between Date()-7 And Now(). I thought I had the Sum of the fields fixed, but I can't seem to get this done. Any suggestions? It is in the Page Header and not the report footer.

[June7]

I think aggregate calc must be in a group or report header/footer. Seems to #Error in page header/footer.

[Thompyt]

Got it all figured out, To this point LOL. How do we mark this as solved?

Thanks

[June7]

Thread Tools dropdown above first post.