5 Replies - 26700 Views - Last Post: 04 June 2011 - 09:10 AM

#1 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,789
  • Joined: 07-February 08

Using MID or INSTR functions with IIF statements in Access 2010

Posted 03 June 2011 - 11:28 AM

Ok, so I have the following code for MS Access 2010:

SELECT S.Description, 
S.ProductType, 
S.[Unit Price], 
S.MfgPart

FROM SHISoftwareImport as S
                  ,Products as P

Where S.Description = IIF(InStr(P.ProductName, 1,3) = 'MS', (InStr(P.ProductName,3), P.ProductName))



The error that I get is:

"Syntax error (comma) in query expression 'S.Description = IIF(InStr(P.ProductName, 1,3) = "MS", (InStr(P.ProductName,3), P.ProductName))'.

I swear I've done this before, but since I've been using SQL Server more then Access, I'm a bit rusty on Access syntax. Anyway, can someone help me clear the error, or tell me what I'm doing wrong?

This post has been edited by P4L: 03 June 2011 - 11:29 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Using MID or INSTR functions with IIF statements in Access 2010

#2 Blasterman007  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 49
  • Joined: 15-September 09

Re: Using MID or INSTR functions with IIF statements in Access 2010

Posted 03 June 2011 - 12:01 PM

Your instr function needs help.

look at this: http://www.techonthe...tring/instr.php
Was This Post Helpful? 1
  • +
  • -

#3 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,789
  • Joined: 07-February 08

Re: Using MID or INSTR functions with IIF statements in Access 2010

Posted 03 June 2011 - 12:08 PM

Scratch the INSTR part, and put MID in there. That part does not need help.

Where S.Description = IIF(Mid(P.ProductName, 1,3) = "MS", (Mid(P.ProductName,3), P.ProductName))


Was This Post Helpful? 1
  • +
  • -

#4 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 386
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Using MID or INSTR functions with IIF statements in Access 2010

Posted 03 June 2011 - 02:26 PM

InStr return an int, while Mid returns a string. You have to mix them up. Also add some result in case the condition fails(the brackets are not correct closed)
Where S.Description = IIF(Mid(P.ProductName, 1, 3) = "MS", 
                     Left(P.ProductName, 3),'here i have no idea what you try to do, I put something random
                     P.ProductName)


Was This Post Helpful? 0
  • +
  • -

#5 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,789
  • Joined: 07-February 08

Re: Using MID or INSTR functions with IIF statements in Access 2010

Posted 03 June 2011 - 10:18 PM

View PostIonut, on 03 June 2011 - 05:26 PM, said:

InStr return an int, while Mid returns a string. You have to mix them up. Also add some result in case the condition fails(the brackets are not correct closed)
Where S.Description = IIF(Mid(P.ProductName, 1, 3) = "MS", 
                     Left(P.ProductName, 3),'here i have no idea what you try to do, I put something random
                     P.ProductName)



Ionut, the "true" portion is the Mid(P.ProductName, 3) while the fail is the full field P.ProductName. The Tables look something like this for those 2 fields:

S.Description P.Product

Access 2010 MS Access 2010
Intelligent 2.0 Intelligent 2.0
SQL Server 2008 R2 MS SQL Server 2008 R2
Reflections 14.1 Reflections 14.1


So to join the tables, I would need the MS to be dropped from P.Product, and there are too many entries to manually remove them, and I don't feel like updating the table since this is a cluster of a db I inherited!
Was This Post Helpful? 1
  • +
  • -

#6 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 386
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Using MID or INSTR functions with IIF statements in Access 2010

Posted 04 June 2011 - 09:10 AM

Yes, just suggesting that you put the brackets wrong
Where S.Description = IIF( 
                          Mid(P.ProductName, 1,3) = "MS", 
                             (
                             Mid(P.ProductName,3), 
                             P.ProductName
                             )
                          )


You should delete the inner ones. It will think there is a single field, not two
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1