I am trying to decode some set data (in codes) to a description. In Excel I would use a VLookup, but am struggling to write the expression in Access. I have built this into a column in a query, but Access is saying that the 'expression is too complex'. Any help on overcoming this would be greatly appreciated. - SSADESC: (IIf([SSA2011] Like "01.3*","Health and Social Care",(IIf([ssa2011] Like "01.5*","Child Development and Well Being",(IIf([SSA2011] Like "04.1*","Engineering",(IIf([SSA2011] Like "04.3*","Transportation Operations and Maintenance",(IIf([SSA2011] Like "05.2*","Building and Construction",(IIf([SSA2011] Like "07.4*","Hospitality and Catering",(IIf([SSA2011] Like "15.*","Business Administration",(IIf([SSA2011] Like "01.4*","Public Services",(IIf([SSA2011] Like "07.3*","Service Enterprises",(IIf([SSA2011] Like "08.1*","Sport, Leisure and Recreation,(IIf([SSA2011] Like "13.2*","Direct Learning Support",""))))))))))))))))))))))
thanks in advance.
3 2070
I would say that the approach here is fundamentally flawed. It looks like you're trying to implement a lookup table using a nested IIf structure.
The way to do this in Access is to create a lookup table with the codes in one column, and the corresponding program descriptions in a second column. Once you have this, you would build it into the record source for your report using a JOIN.
It will be counterproductive for you to continue trying to make the IIf statement work. Let us know what you think; if this seems like a rational approach to you then we'll refine the solution some more.
Pat
zepphead! many thanks! I built a table with all the possible variations of the codes and linked it to the main data via a joined query. It now works a treat!! Thank you! :-)
That sounds great, and it's really no problem - it's what we're here for. Let us know if any other issues crop up.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ciprian Ilie |
last post by:
Hi there,
If you look at the code below, you will see that I am using a template
in order to display some photos on my website.
I also have "previous" and "next" buttons/link which increment the...
|
by: Print On Demand |
last post by:
Hi All,
We are transfering our newsletter list from a manual system into a
more automated one. I'm trying to add a list of names and email
addresses into a database table. The problem I'm...
|
by: brett |
last post by:
Here is my SQL string:
"SELECT to_ordnum, to_orddate," _
& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *
(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ON...
|
by: x muzuo |
last post by:
Hi guys,
I have got a prob of javascript form validation which just doesnt work
with my ASP code. Can any one help me out please.
Here is the code:
{////<<head>
<title>IIBO Submit Page</title>...
|
by: kirill_uk |
last post by:
Help with extracting please folks.!
Hi. I have this:
a variable like: <a href="http://www.some_html.com/text.html" >some
text</a><br>
I heed to extract the "http://www.some_html.com/text.html "...
|
by: seema_coma |
last post by:
Hi all,
Can somebody explain what this statement does,
if ((tempFD = open(tempFileName, O_RDWR | O_CREAT | O_EXCL)) < 0)
Thanks in advance
Seema
|
by: A Jafarpour |
last post by:
Hi everyone, hope someone can tell if there is any way to,
dynamically, build an statement and then call some
functions to execute the statement. I know examples always
help, so here what I am...
|
by: newuser |
last post by:
Please help.
Please help me create a statement that combines these columns to one name
Column 1
Column 2
Column 3
Column 4
Column 5
then I need it to give me the totals and show as top 5
|
by: ZaphodBBB |
last post by:
Windows XP Pro , Access2003.
Hi I have a form called frmCheckSheet and a table called tblCheckSheet.
On the form there is a combobox cboSerialID which is multicolumn and when a serial number is...
|
by: jasone |
last post by:
Hi all,
im nearly there with this one and im sure it shouldnt be hard to solve, i just cant seem to find the solution.
ive got records being displayed, the user can then tick what records to...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |