Expression too complex in query expression vba access

Question

Hi Friends from stackoverflow

I ran into an issue with access and sql, find below my query

update finaldata set [Area]=
IIF(InStr(1,[Name],'PoolSide',1)<>0,'PoolSide',
IIF(InStr(1,[Name],'Mountainside',1)<>0,'Mountainside',
IIF(InStr(1,[Name],'Garden Area',1)<>0,'Garden Area',
IIF(InStr(1,[Name],'Ground Floor',1)<>0,'Ground Floor',
IIF(InStr(1,[Name],'Annex Building',1)<>0,'Annex Building',
IIF(InStr(1,[Name],'Beachside',1)<>0,'Beachside',
IIF(InStr(1,[Name],'Beachfront',1)<>0,'Beachfront',
IIF(InStr(1,[Name],'Mountainside',1)<>0,'Mountainside',
IIF(InStr(1,[Name],'Executive',1)<>0,'Executive Level',
IIF(InStr(1,[Name],'Lakeside',1)<>0,'Lakeside',
IIF(InStr(1,[Name],'Mezzanine',1)<>0,'Mezzanine',
IIF(InStr(1,[Name],'Oceanfront',1)<>0,'Oceanfront',
IIF(InStr(1,[Name],'Overwater',1)<>0,'Overwater,
IIF(InStr(1,[Name],'Slope side',1)<>0,'Slope side',
IIF(InStr(1,[Name],'Tower',1)<>0,'Tower,'Delete'
)))))))))))))))

I have 100k records, I dont want to use recordset it takes too long i already tried it. I have to many inner joins and my database file it's getting larger.

what are my options to process the strings? I have a long long string stored in [Name] with many characteristics of a room. I am trying to decipher the name.

if my only option is a record set, what would be faster to process a nested if or select case?

Thank you for your time and help

For one, IIF(InStr(1,[Name],'Overwater',1)<>0,'Overwater, is missing a single quote causing the expression to get parsed incorrectly. Secondly, don't nest many IIFs, use SWITCH instead — Erik A

Lee Mac Lee Mac · Accepted Answer · 2019-12-24T11:04:38

As noted in the comments, your current code is missing a couple of single quotes to terminate the strings that you are testing, and would be better written using a single switch statement rather than multiple nested iif statements, e.g.:

update finaldata set [area] = 
switch
(
    instr([name],'PoolSide')<>0,'PoolSide',
    instr([name],'Mountainside')<>0,'Mountainside',
    instr([name],'Garden Area')<>0,'Garden Area',
    instr([name],'Ground Floor')<>0,'Ground Floor',
    instr([name],'Annex Building')<>0,'Annex Building',
    instr([name],'Beachside')<>0,'Beachside',
    instr([name],'Beachfront')<>0,'Beachfront',
    instr([name],'Mountainside')<>0,'Mountainside',
    instr([name],'Executive')<>0,'Executive Level',
    instr([name],'Lakeside')<>0,'Lakeside',
    instr([name],'Mezzanine')<>0,'Mezzanine',
    instr([name],'Oceanfront')<>0,'Oceanfront',
    instr([name],'Overwater')<>0,'Overwater',
    instr([name],'Slope side')<>0,'Slope side',
    instr([name],'Tower')<>0,'Tower',
    true,'Delete'
)

Where performance is concerned, rather than repeated use of the instr function, which is returning unnecessary positional data about the substring within the string, you may find it quicker to use the like operator, e.g.:

update finaldata set [area] = 
switch
(
    [name] like '*PoolSide*','PoolSide',
    [name] like '*Mountainside*','Mountainside',
    [name] like '*Garden Area*','Garden Area',
    [name] like '*Ground Floor*','Ground Floor',
    [name] like '*Annex Building*','Annex Building',
    [name] like '*Beachside*','Beachside',
    [name] like '*Beachfront*','Beachfront',
    [name] like '*Mountainside*','Mountainside',
    [name] like '*Executive*','Executive Level',
    [name] like '*Lakeside*','Lakeside',
    [name] like '*Mezzanine*','Mezzanine',
    [name] like '*Oceanfront*','Oceanfront',
    [name] like '*Overwater*','Overwater',
    [name] like '*Slope side*','Slope side',
    [name] like '*Tower*','Tower',
    true,'Delete'
)

And given that this is operating on the entire dataset and could be performing up to 15 comparisons per record when executed, it may be quicker to evaluate 16 separate queries, each with the following structure:

update finaldata set [area] = 'PoolSide' where [name] like '*PoolSide*'

Expression too complex in query expression vba access

1 Answers