How to use Nz

[CaliburBlade138]

Hey all,



Never used the Nz function before, but I need to show a 0 on the table from a query instead of it being blank, um how do I wrap it in this:

IIf(InStr([myBarcode],"Indirect")>0,"Indirect","Order")

[Micron]

maybe
Nz(IIf(InStr([myBarcode],"Indirect")>0,"Indirect","Order"),0) ?

There might be better alternatives, depending on what the data looks like. If myBarcode is a longer string that contains "Indirect" then Instr is probably the way to go. If the issue is that myBarcode can be Null, a simpler test for null might be better.

[ranman256]

i simplified mine to: NZZ(field)
rather than inserting zero everywhere.

Public Function Nzz(ByVal pvAmt)
On Error Resume Next
Nzz = Nz(pvAmt, 0)
End Function

[CaliburBlade138]

@Micron - No errors at all but does not return a column with 0
@ranman256 - Same thing, no errors and runs, but does not return a column with a 0

It's based off of what is in the column [myBarcode], if it has a keyword indirect in it or anything but indirect, the query will show the name, the date, and create the columns Indirect and Orders. Now that's fine and dandy if I have enough data in there but sometimes one or the other is present, such as if it is just finding indirect keywords in a column, the query would just show name, date, and an indirect column with how many. I think that's why I need a filler to put in so it knows to create the other column still... if that makes sense?

[Micron]

I'm not following your explanation.
The original expression (using any number (1) when Indirect is found) using English:

If 1 is greater than 0, return "Indirect", otherwise return "Order".

So the result of your IIF can never be null, which means Nz is useless there.
Are you saying the table field values can be "Indirect" or "Order" or null?
Maybe posting the sql and showing some data will help to clear things up.

[davegri]

If the problem is dealing with myBarcode being null or zls then

IIf(Len(MyBarcode & "")=0,0, IIF (InStr(myBarcode,"Indirect")>0,"Indirect","Order") )

[CaliburBlade138]

@Micron - You hit the nail on the head, from what I am gathering I don't have or use nulls that come up, so your right Nz is useless maybe?
@davegri - I'm not sure that would work if I am not pulling either of those?

Here is some info

PARAMETERS [Forms]![formChartByName]![Combo1] Text ( 255 ), [Forms]![formChartByName]![Text3] DateTime;
TRANSFORM Count(*) AS [Count]
SELECT tablemyTimeKeeper.myName, IIf(Not IsNull([myDateTime]),DateValue([myDateTime])) AS MyDateField
FROM tablemyTimeKeeper
WHERE (((tablemyTimeKeeper.myName)=[Forms]![formChartByName]![Combo1]) AND ((IIf(Not IsNull([myDateTime]),DateValue([myDateTime])))=[Forms]![formChartByName]![Text3]))
GROUP BY tablemyTimeKeeper.myName, IIf(Not IsNull([myDateTime]),DateValue([myDateTime]))
PIVOT IIf(InStr([myBarcode],"Indirect")>0,"Indirect","Order");

Attachment 47578

As you can see here, if there is data for order or indirect they will both show and count the values; however if there is no data say for indirect, it will only show orders, but I need it to show both whether there is no data in there or not with a 0 or N/A or something..

[Micron]

I was hoping for some data, but that is the result?
Thinking you need to add something like this to the end of your Select portion:

Sum(IIf([Indirect] Is Null, 1, 0)) AS NullIndirects, Sum(IIf([Order], 1, 0)) AS NullOrders

[Gicu]

Looks like a crosstab so would it be easier to just switch to fixed column headings using the query's Column Headings property?

https://www.quackit.com/microsoft_ac...ccess_2016.cfm
Cheers,

[CaliburBlade138]

Everyone,

Vlad's solution worked for me 100%, thank you so much!