NZ function, please help 1

[snowyowl]

NZ Function?

I have created a query to count how many records i have that meets a certain criteria.

I have then created a report to look at this query, however, i am finding that if the query results in no records found i do not get a zero therefore my report does not work.

I've heard of the NZ function but i haven't got a clue how to use it, can anyone help???

 

[grnzbra]

NZ is a function that allows you to put in anything you want when a null is encountered. Usually, I've found, it is a 0, but it could be anything else.

What do you want to have happen when there are no records? Reports have an On No Data event. Could you use that?

 

[Bullschmidt]

And here is an example:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No records matched the criteria.", vbExclamation
Cancel = True
End Sub


Best regards,
J. Paul Schmidt - Freelance ASP Web Developer

http://www.Bullschmidt.com

- Creating "dynamic" Web pages that read and write from databases...

 

[KornGeek]

For strings, I commonly use Nz(strMyString, "&quot which gives me a zero-length string instead of a Null.

For numbers, I commonly use Nz(MyNum, 0) which gives me a zero instead of a Null.

Nz(value, valueifnull) is roughly equivalent to

MyFunction(value, valueifnull)
If value = Null
MyFunction = valueifnull
else
MyFunction = value
endif
End Function

Hope this helps explain it a bit.

 

[uscitizen]

interesting....

what if you have a table with a primary key comprising three controls, in this sequence:

a) Patient Number
b) Cycle
c) Test Number

what i'd like to do is generate an incremental value for 'Test Number' everytime the user creates a new record having some combination of 'Patient Number' and 'Cycle'.

my vba code below 'sort of' works, i.e. it doesn't do what i'd like when the value in 'Test Number' doesn't exist and i think i've seen something to the effect that a control that's a part of a primary key can not contain a null value:


Private Sub Cycle_AfterUpdate()
[Test Number] = IIf(IsNull("[Test Number]&quot, 1, 1 + DMax("[Test Number]", "Laboratory Data - Research Tests", "[Patient Number] = " & Me![Patient Number] & " AND [Cycle] = " & Me![Cycle]))
DoCmd.GoToControl ("[Date (Rsch Tst) Form Completed]&quot
If Me![Test Number] > 28 Then
RetValue = MsgBox("Delete any records exceeding the upper limit", vbInformation)
End If
End Sub

i tried using

Dim [Test Number] as Variant

in connection with replacing the IsNull test above, which seemed to be what the help documentation on 'Nz Function Example' is getting at in an effort to get around this or whatever's behind a worthwhile enhancement to the functionality the user has available to him/her, but i get an error message from the compiler.

 

[KornGeek]

uscitizen,
Can you provide us with any additional information here? You mention an error message from the compiler. What's the message and what line of code is it highlighting?

Just glancing through your code, I would try replacing [TestNumber] and Me![TestNumber] with Me!TestNumber (assuming that there is a control on your form named TestNumber).

Also, you are correct that any field that is a primary key or is a part of a primary key cannot be Null. This is a basic rule of database design. Think of Null as meaning "I don't know." If you allow this as part of your key, you cannot guarantee uniqueness anymore.

Good luck.

 

[uscitizen]

hi korngeek!

couple of things:

it's [Test Number] with a space between the two words -- aren't spaces allowed?

another idea i'm trying to work on to get around the "i don't know", "null" thing....

what about having a Default value of '0' (Zero) on 'Test Number'....

however, to the point, i modified the code to read as follows:

Private Sub Cycle_AfterUpdate()
[Test_Number] = IIf(IsNull("[Test_Number]&quot, 1, 1 + DMax(Test_Number, "Laboratory Data - Research Tests", "[Patient Number] = " & Me![Patient Number] & " AND [Cycle] = " & Me![Cycle]))
DoCmd.GoToControl ("[Date (Rsch Tst) Form Completed]&quot
If Me!Test_Number > 28 Then
RetValue = MsgBox("Delete any records exceeding the upper limit", vbInformation)
End If
End Sub

and jotted down the error message:

"Run Time Error: 94"
"Invalid use of Null"

hope this helps.

 

[uscitizen]

here's a variation on the other one........

i have a form called "Concomitant Medications" on which a 'Patient Number' (patient) can be recorded as having taken more than one, ergo there's a 'Medication Number' control (which is basically just an incremental counter).

so, i've placed on the 'Patient Number' control (After Update property) the following code:

Private Sub Patient_Number_AfterUpdate()
[Med Number] = IIf(IsNull("[Med Number]&quot, 1, 1 + DMax("[Med Number]", "Concomitant Medications", "[Patient Number] = " & Me![Patient Number]))
DoCmd.GoToControl ("[Medication]&quot
On Error GoTo ErrorHandler
If Me![Med Number] > 12 Then
RetValue = MsgBox("Delete any records exceeding the upper limit", vbInformation)
End If
ErrorHandler:
If [Med Number] = 1 Then
[Med Number] = 1 + DMax("[Med Number]", "Concomitant Medications", "[Patient Number] = " & Me![Patient Number])
DoCmd.GoToControl ("[Medication]&quot
End If
Resume Next
End Sub


i thought i'd try outsmarting this, so i tossed in an 'Error Handler' based loosely on what i've read in the doc'n. when 'Med Number' is '1' things go according to the book; when 'Med Number' is blank or null or however you call it, then i get an error message which i've jotted down below:

"Run-time error '3314'"

"The field 'Concomitant Medications.Med Number' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field."

now, there are three buttons, 'Continue', 'End','Debug, and 'Help' on this message box/window thing that shows up. Only 'End' and 'Debug' are enabled and i see that hitting 'End' brings you back to the Form where you can manually enter a value.

if this is the best it's going to get, i think i can live with that, but i'd like to disable the 'Debug' button if that's at all possible?

 

[KornGeek]

I just realized that you're doing the check in the AfterUpdate event. Because this field is linked directly to the table, it looks like it never gets successfully updated, so the AfterUpdate event does not fire.

A default value should help here. It would provide some value there and get you past that point. Give that a try and let us know how it worked out.

If that doesn't work, can you determine which line is causing the "Invalid use of Null" error? It would help to see which field is Null.

 

[uscitizen]

yes, i set zero ('0') which has no real meaning outside of working around the problem as the default and this was how it worked:

a) when creating a record which has no previous value in the 'Med Number' field, the code skips past the 'Med Number' control to the next control (the date field) and doesn't bother to populate the 'Med Number' with the value desired which would be '1'.

b) if you the user manually enter '1' into that field, then the next time and the time after, etc....., you create a new record the value of 'Med Number' seems to incrementally grow correctly.

so, i guess we're 1/2 way there.

 

[uscitizen]

whoops, i forgot to tell you that when it skips past the 'Med Number' control, the zero ('0') disappears and the value in there is to all appearances blank.