query from null values in table.. problem

[Nvijayk]

I have a table and one of its fields( account heads) is populated through a form which has a combobox from which a selection is made.

I have a select query (No:1)working on a parameter input by the user. (Which account head?). Whenthe query is run for a particular account head and if the account head has not been selected in the form combobox so far, then obviously the field in the table does not have this account head and therefore the query does not return any records.

My problem starts here. I have a query(No.2)which sums the account head field in query No.1 and becomes the underlying query for a report.In situations of null as explained in para 2, query does not function and error messages appear in the report.

I understand there is this Nz function or similar but is this useable in this situation.If so, how? Please help!

regards

nvk

 

[JPeters]

Nz is a great function, probably one of the nicest little features that MS added into Access. This is an example of its use straight from the help file:

Sub CheckValue()
Dim frm As Form, ctl As Control
Dim varResult As Variant

' Return Form object variable pointing to Orders form.
Set frm = Forms!Orders
' Return Control object variable pointing to ShipRegion.
Set ctl = frm!ShipRegion
' Choose result based on value of control.
varResult = IIf(Nz(ctl.Value) = "", "No value", "Value is " & ctl.Value)
' Display result.
MsgBox varResult
End Sub

Here's more info from the help file:

You can use the Nz function to return zero, a zero-length string (" &quot, or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

Syntax

Nz(variant[, valueifnull])

The Nz function has the following arguments.

Argument Description
variant A variable of data type Variant.
valueifnull Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string. If you use the Nz function in an expression in a query without using the ValueIfNull argument, the results will be empty in the fields that contain null values
If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string, depending on whether the context indicates the value should be a number or a string. If the optional valueifnull argument is included, then the Nz function will return the value specified by that argument if the variant argument is Null.
If the value of variant isn't Null, then the Nz function returns the value of variant.

Remarks

The Nz function is useful for expressions that may include Null values. To force an expression to evaluate to a non-Null value even when it contains a Null value, use the Nz function to return a zero, a zero-length string, or a custom return value.
For example, the expression 2 + varX will always return a Null value when the Variant varX is Null. However, 2 + Nz(varX) returns 2.
You can often use the Nz function as an alternative to the IIf function. For example, in the following code, two expressions including the IIf function are necessary to return the desired result. The first expression including the IIf function is used to check the value of a variable and convert it to zero if it is Null.

varTemp = IIf(IsNull(varFreight), 0, varFreight)
varResult = IIf(varTemp > 50, "High", "Low&quot

In the next example, the Nz function provides the same functionality as the first expression, and the desired result is achieved in one step rather than two.

varResult = IIf(Nz(varFreight) > 50, "High", "Low&quot

If you supply a value for the optional argument valueifnull, that value will be returned when variant is Null. By including this optional argument, you may be able to avoid the use of an expression containing the IIf function. For example, the following expression uses the IIf function to return a string if the value of varFreight is Null.

varResult = IIf(IsNull(varFreight), "No Freight Charge", varFreight)

In the next example, the optional argument supplied to the Nz function provides the string to be returned if varFreight is Null.

varResult = Nz(varFreight, "No Freight Charge&quot

Yeah I know it's lame to just post from the help file, but I find that sometimes it's so well documented that they say it better than even I can.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[Nvijayk]

Hi Josh

Many thanks for the response. Incidentally I had already had a look at the help but was still unable to figure out what exactly was to be done in query No.2. I tried a few things but nothing worked.
Could you please let me know what exactly I should do in query No.2 in either QBE or SQL ?
Regards

Vijay

 

[JPeters]

Try

NZ(Accounthead, "&quot

Check out these two threads. They have lots of good info.

Null Values in a query.
thread701-231449

&

Selecting Query criteria from forms
thread701-304412

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------