iif statement

[Suzanne]

I created a new field "2008CensusRemarks" in my query which combines two
seperate fields "Special Remarks" & "2008 Annual Grid.Remarks with a ;.
Here's what i've come up with:

2008CensusRemarks: IIf([Special Remarks]<>"" And [2008 Annual
Grid.Remarks]<>"",[Special Remarks] & "; " & [2008 Annual Grid.Remarks],
IIf([Special Remarks]<>â€â€ And [2008 Annual Grid.Remarks]=â€â€, [Special
Remarks], [2008 Annual Grid.Remarks]))

This does not work...

What i'm trying to say is this:
1. If [Special Remarks] and [2008 Annual Grid.Remarks] are not blank display
with a ; between them, or
2. If [Special Remarks] is not blank and [2008 Annual Grid.Remarks] is blank
display [Special Remarks], or
3. If [Special Remarks] is blank and [2008 Annual Grid.Remarks] is not blank
display [2008 Annual Grid.Remarks]

Any help would be greatly appreciated.

 

[Beetle]

Not sure what you mean by "it does not work", but you
may need to convert nulls to a zero length string using the
Nz function;

IIf(Nz([Special Remarks],"")<>"" And Nz([2008 Annual
GridRemarks],"")<>"",[Special Remarks] & " ; " & [2008 Annual
GridRemarks],IIf(Nz([Special Remarks],"")<>"" And Nz([2008 Annual
GridRemarks],"")="",[Special Remarks],[2008 Annual GridRemarks]))

 

[Jerry Whittle]

"This does not work..." Precisely what is the problem?

I noticed that you're statement has some smart quotes instead of straight
quotes. That can sometimes cause problems. See if they are really there or
just something Windows added when you cut and pasted here.

Also "" only finds empty strings. It does not find nulls or non-printing
characters such as spaces.

 

[John W. Vinson]

I created a new field "2008CensusRemarks" in my query which combines two
seperate fields "Special Remarks" & "2008 Annual Grid.Remarks with a ;.
Here's what i've come up with:

2008CensusRemarks: IIf([Special Remarks]<>"" And [2008 Annual
Grid.Remarks]<>"",[Special Remarks] & "; " & [2008 Annual Grid.Remarks],
IIf([Special Remarks]<>”” And [2008 Annual Grid.Remarks]=””, [Special
Remarks], [2008 Annual Grid.Remarks]))

This does not work...

What i'm trying to say is this:
1. If [Special Remarks] and [2008 Annual Grid.Remarks] are not blank display
with a ; between them, or
2. If [Special Remarks] is not blank and [2008 Annual Grid.Remarks] is blank
display [Special Remarks], or
3. If [Special Remarks] is blank and [2008 Annual Grid.Remarks] is not blank
display [2008 Annual Grid.Remarks]

Any help would be greatly appreciated.

Well, unless you've (unwisely!) set the Allow Zero Length properties of the
two fields, they'll never be equal to "".

I'd suggest:

[Special Remarks] & IIF(IsNull([Special Remarks] OR IsNull([2008 Annual
Grid].[Remarks]), "", "; ") & [2008 Annual Grid].[Remarks])

This assumes that Remarks is the fieldname and 2008 Annual Grid is the
tablename - note that the table is in brackets, as is the field, rather than
the combination. If you actually have a period in the fieldname use your
bracketing (and I'd really recommend that you DON'T use periods in
fieldnames!!).

My expression concatenates the two fields, with a semicolon followed by a
blank between them only if both are non-NULL.

 

[Suzanne]

Thank you this has fixed my problem.

Not sure what you mean by "it does not work", but you
may need to convert nulls to a zero length string using the
Nz function;

IIf(Nz([Special Remarks],"")<>"" And Nz([2008 Annual
GridRemarks],"")<>"",[Special Remarks] & " ; " & [2008 Annual
GridRemarks],IIf(Nz([Special Remarks],"")<>"" And Nz([2008 Annual
GridRemarks],"")="",[Special Remarks],[2008 Annual GridRemarks]))


--
_________

Sean Bailey

I created a new field "2008CensusRemarks" in my query which combines two
seperate fields "Special Remarks" & "2008 Annual Grid.Remarks with a ;.
Here's what i've come up with:

2008CensusRemarks: IIf([Special Remarks]<>"" And [2008 Annual
Grid.Remarks]<>"",[Special Remarks] & "; " & [2008 Annual Grid.Remarks],
IIf([Special Remarks]<>â€â€ And [2008 Annual Grid.Remarks]=â€â€, [Special
Remarks], [2008 Annual Grid.Remarks]))

This does not work...

What i'm trying to say is this:
1. If [Special Remarks] and [2008 Annual Grid.Remarks] are not blank display
with a ; between them, or
2. If [Special Remarks] is not blank and [2008 Annual Grid.Remarks] is blank
display [Special Remarks], or
3. If [Special Remarks] is blank and [2008 Annual Grid.Remarks] is not blank
display [2008 Annual Grid.Remarks]

Any help would be greatly appreciated.