# Access - if field is blank, copy field A, if not copy field B



## TQ001 (Feb 22, 2009)

I am working on a database that stores genus, species, and variety I want to sort the data with genus, then species, then variety.

Now the tricky part. Sometimes there is no species. When this is the case, I want the database to sort that record off of variety.

I have though of a way to do this, but need help with the code.

If I make a species2 field, I could create an expression that would look at the species field. If the species field is empty, it could copy the data from variety. If it is not empty, then copy the data in the species field.

Once the species2 field has the updated data, I could then do a sort

genus, species2, variety

The report would have genus, species and variety showing and species2 would be checked off to not show in the report.

I am using Microsoft Access 2000.

Any help would greatly be appreciated.


----------



## OBP (Mar 8, 2005)

You should not actually need another field to do this, just another column in your report's Query. Something like this as a Field header :-

dummySpecies: iif(isnull([species]), [genus], [species])

this should provide a column with the species in if it there and the genus if it is not, which you can use in place of your actual species field in the report for sorting.


----------

