if/then query question

[crayfishgirl]

I'm hoping this will be an easy question for most people.

I have a query with three text fields of insect names (Genus species, Family, Order). In some cases, where the the insect could not be identified to the Genus species level, it was identified at the Family or Order level. I'm attempting to write a query that will result in a single field of names where the Genus species name will be listed where its available, but if that is null, it will list the Family name. If the Family name is also null I want it to list the Order name. I thought I could do this in a second qry if I could get the first step done but have been unsuccessful so far.

The SQL expression I have now is below but its only listing the Genus species name (which I already have as its own field).

taxa: IIf([BMI_GenusspeciesMerge_AllData_qry]![Genusspecies] Is Null,[BMI_GenusspeciesMerge_AllData_qry]![Family],[BMI_GenusspeciesMerge_AllData_qry]![Genusspecies])

I'm a self-taught ACCESS user and am pretty okay with most things but am SQL and syntax challenged. Help (and thanks!)!!

camille

 

[Jon K]

taxa: IIf([BMI_GenusspeciesMerge_AllData_qry].[Genusspecies] Is Not Null, [BMI_GenusspeciesMerge_AllData_qry].[Genusspecies], IIf([BMI_GenusspeciesMerge_AllData_qry].[Family] Is Not Null, [BMI_GenusspeciesMerge_AllData_qry].[Family], [BMI_GenusspeciesMerge_AllData_qry].[Order]))

.

 

[crayfishgirl]

Thanks Jon....but it still didn't work. The Genusspecies name is being returned but empty cells where Genusspecies is absent/null. Help again??

 

[neileg]

Are the fields really null? If they have had data and it's been deleted, you get a zero length string, not a null. But isn't the syntax wrong here, anyway?

taxa: IIf(IsNull([BMI_GenusspeciesMerge_AllData_qry]![Genusspecies]),[BMI_GenusspeciesMerge_AllData_qry]![Family],[BMI_GenusspeciesMerge_AllData_qry]![Genusspecies])

is how I would have written the original expression.

 

[crayfishgirl]

Thanks Neil...you might be right about the 0 length string because your code isn't working either (and I think it should actually give me what I want).

Is there a way to make the cells in the original table null?

camille

 

[Brianwarnock]

Check for zero lentgh fields by = ""

Brian

 

[neileg]

If the data is imported the blank cells might be a space which you would test with =" "

 

[crayfishgirl]

I'm afraid this still isn't working. I have 91,000 records in the table I want to so I created a new small table to test the theory that the empty cells aren't truly null. I made sure the cells where Genusspecies were missing were null by putting "" into those that were empty. That's correct, right?

This is the code that I have from NeilG (modified for my smaller test table) but it still just returns the the Genusspecies and leaves blank cells where that info is absent.

taxa: IIf(([species merge test1_qry]![Genusspecies]) Is Null,[species merge test1_qry]![Family],[species merge test1_qry]![Genusspecies])


Any other suggestions? Am I missing something or my database hate me? I really appreciate all the assistance I'm getting here!

 

[The_Doc_Man]

Consider (and look up) a UNION query.

SELECT [Genus] as TheName from bigtable where not ( IsNull( [Genus] ) or IsEmpty( [Genus] ) ) UNION
SELECT [Family] as TheName from bigtable where ( IsNull( [Genus] ) or ( IsEmpty( [Genus] ) ) AND not ( IsNull( [Family] ) or ( IsEmpty( [Family] ) )UNION
SELECT [IOrder] as TheName from bigtable where ( IsNull( [Genus] ) or ( IsEmpty( [Genus] ) AND ( IsNull( [Family] ) or IsEmpty( [Family] ) ) ;

You cannot use "Order" as a field name because ORDER is an SQL keyword. So I substituted IOrder.

If there are other fields you want besides the Genus, Family, or Order name, they of course go in the SELECT clauses.

 

[crayfishgirl]

Thanks Doc Man

This is where I am now. I tried the union query on my test table and changed "order" to "iorder". Unfortunately, it still only pulls the Genusspecies names regardless of any commands after that.

I've done the query in stages in the hopes I can get a hint as to where the issues are (ie. retrieving only Genusspecies, retrieving Genusspecies and Family, and retrieving all three names). I used the following code to retrieve just the Genusspecies name and got all the Genusspecies names and blank cells where this info was missing

select [Genusspecies] as taxaname from [species merge test1_qry] where not (isnull ([genusspecies])) or (isempty ([genusspecies]))


When I attempted to retrieve the Genusspecies and Family name using the command below, I got the Genusspecies names and no family names. However, I didn't get any empty cells. Does that mean anything as to how the data are set up or defined? As you can see, I'm grasping at straws here. Any other suggestions/ideas?


select [Genusspecies] as taxaname from [species merge test1_qry] where not (isnull ([genusspecies])) or (isempty ([genusspecies])) union
select [Family] as taxaname from [species merge test1_qry] where (isnull ([genusspecies])) or (isempty ([genusspecies]))

 

[Jon K]

I created a new small table to test the theory that the empty cells aren't truly null. I made sure the cells where Genusspecies were missing were null by putting "" into those that were empty. That's correct, right?

Wrong! Null is null. "" is "".

To test for Null, you need to use Is Null (or the IsNull() function).

To test for "", you need to use =""


If ="" still doesn't work, you can attached a database (preferably in Access 2000 format) with some records from your original table for us to have a look.
.

 

[crayfishgirl]

Thanks Jon

I think that's the problem.....the cells with missing information aren't null. How do I go about making them null (I thought I have done that with the "")?

camille

 

[Jon K]

First you can run a query to make sure the empty cells are really ""

SELECT [Genusspecies]
FROM [YourTableName]
WHERE [Genusspecies]=""


If the empty records are returned, then you can run an update query to change the "" into Null.

UPDATE [YourTableName] SET [Genusspecies] = Null
WHERE [Genusspecies]=""
.

 

[CraigDolphin]

Try this...

taxa: IIf([BMI_GenusspeciesMerge_AllData_qry]![Genusspecies] & "" = "",[BMI_GenusspeciesMerge_AllData_qry]![Family],[BMI_GenusspeciesMerge_AllData_qry]![Genusspecies])

...the equivalent works for me in this little test database (attached).

 

[The_Doc_Man]

Just be warned that there is a school of thought that says making a null field and a field with a null string (zero length string) have different meanings is very bad due to subtle complexities that cannot be easily separated by the old naked eyeball. I would also point out that making a record's field NULL again is sort of like making a woman virgin again. Easy to say, harder to do than one might think.

Whereas making all NULL fields contain Zero Length Strings (ZLS) is not very hard at all. AND it eliminates the need for that tricky little IsNull([field]) test that you need for every case. Simplifies matters tremendously.

 

[EMP]

One warning

The functionality of Null has been fully incorporated into Totals Queries. If you need to do Totals Queries, it is not advisable to change the Null values (which is Access' default).

^

 

[crayfishgirl]

Thanks much for all your assistance everyone! Using CraigDolphin's code the Taxa names are all in a single column and I can proceed with the analyses....and I don't think I changed anything in terms of making cells null? Regardless, I'm very appreciative. Have a great day...

camille