Usinf iif in expression builder

[Homegrownandy]

Hello.

I'm using this in a text box on a form to tell the user some information about the order they are invoicing against. This part works;

="Line numer " & [line number] & " has been invoiced against " & [countofline id] & " times. The remaining line quantity is " & [remaining quantity] & ". Which equates to £" & [remaining value] & "."

But when there is no previous invoices its a bit messy with blank spaces in the text string. I've attempted to use the iif function to display a different message if there are no records. But I cant get it working. There is no error message it just behaves as it did with the first code.

Here is what I have written;

[=IIf([CountOfLine ID]=Null,"This has not been invoiced against","Line numer " & [line number] & " has been invoiced against " & [countofline id] & " times. The remaining line quantity is " & [remaining quantity] & ". Which equates to £" & [remaining value] & ".")

If someone could tell me where this is going wrong that would be appreciated.

[aytee111]

Put it into VBA, it is a lot easier to work with. Then you can set up two label fields and make them visible/invisible with an If statement in an AfterUpdate event. Or keep the textboxes but handle them in VBA.

[Homegrownandy]

Good idea. Ill be back shortly when I cant get it to work.

[Homegrownandy]

Right, some issues I can't figure out. This is based on information that already exists. When a user is inputting a new invoice this just tells them a brief history of it. So, after update is no good.

In fact, thinking about it. (unless you can tell me otherwise) I believe it would have to be in the expression builder. This is on a continuous form. So the visibility of a text box would apply throughout.

I want the right text to appear on each record. What do you think?

[aytee111]

If this is a display-only subform then you can create a query. The AfterUpdate I was thinking about would be after the initial selection was made, after they had selected which order.

Another option would be to turn them all into their own individual textboxes, which it sounds like already exist anyway. Remove the message, I don't think users need it, they can look at the values for themselves and put it into a nice English sentence.

[Homegrownandy]

I cant use a subform on a continuous form. This data can not be part of the main forms record source due to how its grouped to create the count and the sum. I have no problem displaying the data in text boxes individually but again, this would need to be via an expression wouldn't it? as the data there can not be in the forms record source.

The information relating to each line will be used for validation purposes, the sentence is so the validation makes sense to the user. Also our accountant would get a good idea of the information (quantity or value) that they should have.

[aytee111]

In your first post, there is a "[" at the beginning of the line, maybe this is the error? Also, are you sure that [CountOfLine ID] is null, not "" or 0? That is why I suggested using VBA, at least to get it working and then put it back into the control source. Useful for debugging and seeing what exactly each value contains.

(I assumed this was a subform, my mistake.)

[ssanfu]

I agree with aytee111.... get the expression working first. There shouldn't be a reason for the IIF() function (immediate If) to throw an error.

The IIf function has 3 parts: the condition, the value if true and the value if false.

1) The condition..
You are using "[CountOfLine ID]=Null". Since this looks like a numeric type field, I would use "NZ([CountOfLine ID],0) = 0"

2) The value if TRUE.
You have "This has not been invoiced against". I don't understand... is this is the full expression????

3) The value if FALSE
You have "Line numer " & [line number] & " has been invoiced against " & [countofline id] & " times. The remaining line quantity is " & [remaining quantity] & ". Which equates to £" & [remaining value] & "."
This is the original expression the you say works.

Putting it together:
function =IIF(
condition NZ([CountOfLine ID],0) = 0,
value if TRUE "This has not been invoiced against",
value if FALSE "Line numer " & [line number] & " has been invoiced against " & [countofline id] & " times. The remaining line quantity is " & [remaining quantity] & ". Which equates to £" & [remaining value] & "."
)

=IIf(NZ([CountOfLine ID],0) = 0,"This has not been invoiced against", "Line numer " & [line number] & " has been invoiced against " & [countofline id] & " times. The remaining line quantity is " & [remaining quantity] & ". Which equates to £" & [remaining value] & ".")

Maybe you are trying to concatenate the string "This has not been invoiced against" to the beginning of the original expression if the value of [CountOfLine ID] is zero.
That would be (I hope):

=IIf(NZ([CountOfLine ID],0) = 0,"This has not been invoiced against ", "") & "Line numer " & [line number] & " has been invoiced against " & [countofline id] & " times. The remaining line quantity is " & [remaining quantity] & ". Which equates to £" & [remaining value] & "."

[Homegrownandy]

It seems a count does return a 0 not a null when there are none. So that's good. but thanks for the effort with the NZ function I didn't even think of using it. I'm not concatenating the two strings, but I'm having issue with the record source at the moment. Ill sort that and this should be fine. Thanks both.

Edit: That works perfect. Also. The vba suggestion was misunderstood by myself. Using it to debug then move to an expression is a good idea. Thanks both.

[Homegrownandy]

I have this form working perfect when its opening all records. Each expression relates to the row that it is on fine. But when I open the form via the order number. Only showing specific records. I get #type!.

I'm looking online and doing some trouble shooting. but does anyone know why this happens?

[Homegrownandy]

I just kept deleting part of the string to find the culprit. It was a reference to a table which was correct but wasn't needed. Removed that and it works fine. Strange how this would be an issue though.