IIf function

[darinmc]

Hi
I have a query and am trying to replace the Nulls with Nulls, and place a comment "Order Number" but it is currently placing the comment in every record.
please can you help, what have I done?

IIf(IsNull([InvOrderNo])=Null,Null,"Order Number" & [InvOrderNo])

Thx
Darin

 

[SkipVought]

Hi,

Is Function are boolean...

IIf(IsNull([InvOrderNo]),"","Order Number" & [InvOrderNo])

Null and "" are not the same. You're returning a STRING in the IIF()

Skip,

_[red][/red]

 

[Crowley16]

isnull function evaluates to a yes or no, not yes/no/null, therefore your test criteria will evaluate to false for everything...

--------------------
Procrastinate Now!

 

[darinmc]

Thx for quick replies...

Is there a way to make it work...
Basically I need the null values to remain null and the fields that have an order number to show BUT concatenated with "Order Number"

Thx
Darin

 

[SkipVought]

IIf(IsNull([InvOrderNo]),[InvOrderNo],"Order Number" & [InvOrderNo])

Skip,

_[red][/red]

 

[darinmc]

IIf(IsNull([InvOrderNo]),[InvOrderNo],"Order Number" & [InvOrderNo])
That code still places the word Order Number[/] in every field.

If I replace the IsNill with nz, then the Order numbers appear BUT not the word "Order Number"

Thx
Darin

 

[darinmc]

Do I not need to convert the last part to a string?

 

[Crowley16]

are you sure the fields are null and not 0 length? or just space?

--------------------
Procrastinate Now!

 

[darinmc]

Order Number is a text field, no default value...
I use the insert command when creating new records, I assume when inserting, that the text field will be entered as a null??
Darin

 

[Crowley16]

a good way to test for existence of some data is:

len(nz(fieldName) & "") > 0

--------------------
Procrastinate Now!

 

[darinmc]

Com2: Len(nz([InvOrderNo]) & "")>0

If I do this it doesn't show any data, all are blank. I tried a few variations using null... I'm not sure what I should be getting or doing...
Darin

 

[PHV]

Why not simply this ?
Com2: 'Order Number' [!]+[/!] [InvOrderNo]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Crowley16]

nz(blah,"")

--------------------
Procrastinate Now!

 

[darinmc]

Sorry PHV, BUT I dont want it show the word Order Number in Blank fields, but ONLY where there actually is an Order Number.
Any Ideas, surely it should be fairly easy?

Thx
Darin

 

[darinmc]

Thanks Guys, got it to work... used this

Com2: IIf(nz([InvOrderNo])="","",'Order Number '+[InvOrderNo])

Darin

 

[PHV]

darinmc, just to know, are you sure that InvOrderNo is Null instead of ZeroLength ?
If it is null, then my suggestion should have worked ...