|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
iif statementseperate 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. 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])) -- Show quoteHide quote_________ Sean Bailey "Suzanne" wrote: > 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. > > Thank you this has fixed my problem.
Show quoteHide quote "Beetle" wrote: > 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 > > > "Suzanne" wrote: > > > 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. > > > > "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. -- Show quoteHide quoteJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Suzanne" wrote: > 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. > > On Tue, 2 Jun 2009 12:12:03 -0700, Suzanne <Suza***@discussions.microsoft.com>
wrote: Show quoteHide quote >I created a new field "2008CensusRemarks" in my query which combines two Well, unless you've (unwisely!) set the Allow Zero Length properties of the>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. > 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. -- John W. Vinson [MVP]  
Other interesting topics
make table query corrupts hyperlink field
Long Integer field size Problems inserting records Calculations between records. TransferSpreadsheet question Undefined function IF in Expression Printing a report Billing SubReport Hours Not Displaying on Invoices What is the Best Way to Insert a Large Nbr of Recs With No Message How Do I Populate Body of Invoice? |
|||||||||||||||||||||||
