0, core.name, header.name) as name2
In the Access query builder, you can do the same thing but the syntax is slightly different. Instead of entering a field name, type:
name2: IIf(Len([core04pc.name])>0,[core04pc.name],[ddheader.name])
In 'SQL View,' you will see:
IIf(Len([core04pc.name])>0,[core04pc.name],[ddheader.name]) AS name2
...same as mysql or SAS but:
- LENGTH() is LEN()
- IF() is IIF()
- Square brackets are required around the field names or they get interpreted as character strings, I believe.">

Thank you for using the SHARE New Mexico web site! Quality of data and information is our utmost concern, and we rely on users to notify us about misinformation, updates and errors. We also count on your feedback to help us make site improvements.

Please fill out the following form to send us comments, questions or suggestions. We will respond as soon as we can.

Knowledgebase

Documents, videos, websites and more.

Access: Calculated Fields

Brief description or abstract:The Irvine Access Database has a query called "Master Data" where I combined the data for all variables. The Master Data has missing Name for 4 Eins, because Name comes from my core file, but Eins are pulled from the MasterEins file. So, basically there are 4 eins that are in the Header file or the orgPurpose or the RevExp file, but that are NOT in the Core file.
In sql, you want to write something like this:
IF( LENGTH( core.name) > 0, core.name, header.name) as name2
In the Access query builder, you can do the same thing but the syntax is slightly different. Instead of entering a field name, type:
name2: IIf(Len([core04pc.name])>0,[core04pc.name],[ddheader.name])
In 'SQL View,' you will see:
IIf(Len([core04pc.name])>0,[core04pc.name],[ddheader.name]) AS name2
...same as mysql or SAS but:
- LENGTH() is LEN()
- IF() is IIF()
- Square brackets are required around the field names or they get interpreted as character strings, I believe.