IIF statements?

[staceyo]

Hi,
I have biological data in a query and need a column which has brings all the data together. But not all the cells have information in them.

So I would like one column (called Taxa) which combines all the data together.
So far I have tried using an IIF statement, but where some of the data is null, I get an extra space, and can't work out how best to combine these columns into one.

This is the IIF statement I have so far:

Taxa: IIf([SUBGENUS] Is Not Null,[GENUS] & " " & "(" & [SUBGENUS] & ")" & " " & [SPECIES] & " " & [AUTHORITY],[GENUS] & " " & [SPECIES] & " " & [AUTHORITY])

Oh, and should subgenus be present, it needs to have brackets around it...!
Any help would be very much appreciated!


Thanks!

[HiTechCoach]

Hi,
I have biological data in a query and need a column which has brings all the data together. But not all the cells have information in them.

So I would like one column (called Taxa) which combines all the data together.
So far I have tried using an IIF statement, but where some of the data is null, I get an extra space, and can't work out how best to combine these columns into one.

This is the IIF statement I have so far:

Taxa: IIf([SUBGENUS] Is Not Null,[GENUS] & " " & "(" & [SUBGENUS] & ")" & " " & [SPECIES] & " " & [AUTHORITY],[GENUS] & " " & [SPECIES] & " " & [AUTHORITY])

Oh, and should subgenus be present, it needs to have brackets around it...!
Any help would be very much appreciated!
Thanks!

If you want brackets try this:

Taxa: [GENUS] & IIf(IsNull([SUBGENUS], " " , " [" &  [SUBGENUS] & "]  ") & [SPECIES] & " " &  [AUTHORITY],

If you want Parentheses try this:

Taxa: [GENUS] & IIf(IsNull([SUBGENUS], " " , " (" &  [SUBGENUS] & ")  ") & [SPECIES] & " " &  [AUTHORITY],

[staceyo]

Thanks HiTechCoach,

I tried that with various variations, but keeps telling me I have an invalid syntax! And I'm not even sure what that means, so I'm not doing well to solve the problem.

As not all the columns are always used (subgenus/subspecies0, I guess to avoid extra spaces, the query should be something along the lines of:

Taxa: [GENUS]&" "&IIF(IsNull [SUBGENUS] ," ","("[SUBGENUS] ")") & [SPECIES] &" "& IIF(IsNull [SUBSPECIES] ," ", [SUBSPECIES]) &" "& [AUTHORITY]

Correct?
Except for the invalid syntax it keeps telling me I have...

[weekend00]

Staceyo:
remove the space in " " in you original query, then it will work.

[HiTechCoach]

Thanks HiTechCoach,

I tried that with various variations, but keeps telling me I have an invalid syntax! And I'm not even sure what that means, so I'm not doing well to solve the problem.

As not all the columns are always used (subgenus/subspecies0, I guess to avoid extra spaces, the query should be something along the lines of:

Taxa: [GENUS]&" "&IIF(IsNull [SUBGENUS] ," ","("[SUBGENUS] ")") & [SPECIES] &" "& IIF(IsNull [SUBSPECIES] ," ", [SUBSPECIES]) &" "& [AUTHORITY]

Correct?
Except for the invalid syntax it keeps telling me I have...

Try:

Taxa: [GENUS]&" "&IIF(IsNull [SUBGENUS] ," ","(" & [SUBGENUS] & ")") & [SPECIES] &" "& IIF(IsNull [SUBSPECIES] ," ", [SUBSPECIES]) &" "& [AUTHORITY]

Boyd Trimmell aka Hitechcoach
Microsoft Access MVP
Started with Access 2.0. Now using the latest version of Access.

[HiTechCoach]

Staceyo:
remove the space in " " in you original query, then it will work.

Which one?

Boyd Trimmell aka Hitechcoach
Microsoft Access MVP
Started with Access 2.0. Now using the latest version of Access.

[weekend00]

Which one?

Taxa: IIf([SUBGENUS] Is Not Null,[GENUS] & " " & "(" & [SUBGENUS] & ")" & " " & [SPECIES] & " " & [AUTHORITY],[GENUS] & " " & [SPECIES] & " " & [AUTHORITY])

[staceyo]

Weekend00 - that query does work, except if I have something in the subgenus/subspecies column, it gets missed as that query only works if both are filled in, not if only one is filled in.

HiTechCoach - I tried the query you suggested, and still says I have invalid syntax...?

I will keep trying...!

[HiTechCoach]

Will you post the current SQL you are using?

Boyd Trimmell aka Hitechcoach
Microsoft Access MVP
Started with Access 2.0. Now using the latest version of Access.

[staceyo]

I am not an administrator for the database, so cant figure out how to get the SQL info, but using SQL Server 2000, I believe, if that is any help?

Also, a co-worker has suggested I paste the SQL view of the query I am working on as it may help?

SELECT Crustacea_InWS4_Yes.CLASS, Crustacea_InWS4_Yes.ORDER, Crustacea_InWS4_Yes.SUBORDER, Crustacea_InWS4_Yes.INFRAORDER, Crustacea_InWS4_Yes.FAMILY, IIf([Crustacea_InWS4_Yes]![SUBGENUS] And [Crustacea_InWS4_Yes]![SUBSPECIES] Is Not Null,[Crustacea_InWS4_Yes]![GENUS] & " " & "(" & [Crustacea_InWS4_Yes]![SUBGENUS] & ")" & " " & [Crustacea_InWS4_Yes]![SPECIES] & " " & [Crustacea_InWS4_Yes]![SUBSPECIES] & " " & [Crustacea_InWS4_Yes]![AUTHORITY],[Crustacea_InWS4_Yes]![GENUS] & " " & [Crustacea_InWS4_Yes]![SPECIES] & " " & [Crustacea_InWS4_Yes]![AUTHORITY]) AS Taxa, Crustacea_InWS4_Yes.NEAREST, Count(Crustacea_InWS4_Yes.DB_ID) AS CountOfDB_ID
FROM Crustacea_InWS4_Yes
GROUP BY Crustacea_InWS4_Yes.CLASS, Crustacea_InWS4_Yes.ORDER, Crustacea_InWS4_Yes.SUBORDER, Crustacea_InWS4_Yes.INFRAORDER, Crustacea_InWS4_Yes.FAMILY, IIf([Crustacea_InWS4_Yes]![SUBGENUS] And [Crustacea_InWS4_Yes]![SUBSPECIES] Is Not Null,[Crustacea_InWS4_Yes]![GENUS] & " " & "(" & [Crustacea_InWS4_Yes]![SUBGENUS] & ")" & " " & [Crustacea_InWS4_Yes]![SPECIES] & " " & [Crustacea_InWS4_Yes]![SUBSPECIES] & " " & [Crustacea_InWS4_Yes]![AUTHORITY],[Crustacea_InWS4_Yes]![GENUS] & " " & [Crustacea_InWS4_Yes]![SPECIES] & " " & [Crustacea_InWS4_Yes]![AUTHORITY]), Crustacea_InWS4_Yes.NEAREST
ORDER BY Crustacea_InWS4_Yes.CLASS, Crustacea_InWS4_Yes.ORDER, Crustacea_InWS4_Yes.SUBORDER, Crustacea_InWS4_Yes.INFRAORDER, Crustacea_InWS4_Yes.FAMILY, IIf([Crustacea_InWS4_Yes]![SUBGENUS] And [Crustacea_InWS4_Yes]![SUBSPECIES] Is Not Null,[Crustacea_InWS4_Yes]![GENUS] & " " & "(" & [Crustacea_InWS4_Yes]![SUBGENUS] & ")" & " " & [Crustacea_InWS4_Yes]![SPECIES] & " " & [Crustacea_InWS4_Yes]![SUBSPECIES] & " " & [Crustacea_InWS4_Yes]![AUTHORITY],[Crustacea_InWS4_Yes]![GENUS] & " " & [Crustacea_InWS4_Yes]![SPECIES] & " " & [Crustacea_InWS4_Yes]![AUTHORITY]);

It is that last bit that I am having trouble with.

[HiTechCoach]

Try this:

 [Crustacea_InWS4_Yes]![GENUS] & " " & "("+[Crustacea_InWS4_Yes]![SUBGENUS]+") " & [Crustacea_InWS4_Yes]![SPECIES] & " " & [Crustacea_InWS4_Yes]![SUBSPECIES]+" " & [Crustacea_InWS4_Yes]![AUTHORITY] AS Taxa

I have attached my test database

Boyd Trimmell aka Hitechcoach
Microsoft Access MVP
Started with Access 2.0. Now using the latest version of Access.

[staceyo]

Thanks so much HiTechCoach!
I don't understand how, but that seems to be doing the trick!!!

Just one more thing, if the authority column is also blank, how would I ammend the query so that it doesn't end up with a space at the end?

[HiTechCoach]

Try changing the last & to a + like this:

[Crustacea_InWS4_Yes]![GENUS] & " " & "("+[Crustacea_InWS4_Yes]![SUBGENUS]+") " & [Crustacea_InWS4_Yes]![SPECIES] & " " & [Crustacea_InWS4_Yes]![SUBSPECIES]+" " + [Crustacea_InWS4_Yes]![AUTHORITY] AS Taxa

or use Trim() this:

Trim([Crustacea_InWS4_Yes]![GENUS] & " " & "("+[Crustacea_InWS4_Yes]![SUBGENUS]+") " & [Crustacea_InWS4_Yes]![SPECIES] & " " & [Crustacea_InWS4_Yes]![SUBSPECIES]+" " & [Crustacea_InWS4_Yes]![AUTHORITY]) AS Taxa

Boyd Trimmell aka Hitechcoach
Microsoft Access MVP
Started with Access 2.0. Now using the latest version of Access.

[staceyo]

Hey HiTechCoach,

Don't worry, our Admin guy (who works at another company) dropped by today, so he's ammended the query to take out the extra space at the end if the Authority column is blank by using Trim around the whole query.

Thanks for all your help!

[staceyo]

sorry, I hadn't refreshed my page, so didn't see your new post!
Cheers!