IIf Statment and Left, Synax Error

[Webee]

Hi

Can some one please tell me why the following does not work. Message comes up saying:-

synax error (comma) in query expression 'IIf(Left([Product],InStr(1,[Product],"mSpec")-1 Like "*[! 1-24]*"),(Left([Product],InStr(1,[Product],"mSpec")-1),1212))'.

Thank you

 

[Elvaar]

In may help if we knew exactly what your code was supposed to do.

But I do not believe that this part of your code is correct:

InStr(1 & [Product] & " mSpec")

Proper syntax of InStr function is:

InStr( [start], string_being_searched, string2, [compare] )

 

[Webee]

Hi

Can some one please tell me why the following does not work. Message comes up saying:-

synax error (comma) in query expression 'IIf(Left([Product],InStr(1,[Product],"mSpec")-1 Like "*[! 1-24]*"),(Left([Product],InStr(1,[Product],"mSpec")-1),1212))'.

This is the correct code. what I am trying to do is take out the number before "mSpec" but not all of the record have a number before it so for them records it displays an error. So I want to use IIf function so if there is a number it displays the number if there is no number it needs to display "1212".

Thank you

 

[raskew]

Hi -

Not sure I understand what you're after, but if the intent is to test the character immediately preceding "mspec", suggest using the Mid() and IsNumeric() functions.

Here's an example from the debug (immediate) window. I've used 'x' and 'y' to represent [product]:

x = "1234mspec" 
y = "1234xmspec"

[B]Testing the IsNumeric() function:[/B]

? isnumeric(mid(x, instr(x, "mspec")-1, 1))
True
? isnumeric(mid(y, instr(y, "mspec")-1, 1))
False

[B]Adding the Iif() function:[/B]

? iif(isnumeric(mid(x, instr(x, "mspec")-1, 1)), mid(x, instr(x, "mspec")-1, 1), "1212")
4
? iif(isnumeric(mid(y, instr(y, "mspec")-1, 1)), mid(y, instr(y, "mspec")-1, 1), "1212")
1212

[B]The Left() vs. the Mid() functions[/B]

? Left(x,InStr(1, x ,"mSpec")-1)
1234
? mid(x, instr(x, "mspec")-1, 1)
4

HTH - Bob

 

[Webee]

Hi

Thank you for your help but not all of the feilds have "mSpec" some are totally different so it would not even be able to find these.

I found another away to sort this problem but it now diplays error and not the true value but does display the false one.

IIf(Nz(Left([Product],InStr([Product],"mSpec") Not Like "[! 1-24!]")),0,Left([Product],InStr([Product],"mSpec")-1))

Thank you

 

[raskew]

Hi -

How about posting the entire query SQL so we can get a better feel for your data.

In particular, I'm interested in "[! 1-24!]"? What is that? Is that a field name?

Bob

 

[Webee]

Hi

here is the code



SELECT DISTINCTROW Peek.Product, Peek.*,
IIf(Nz(Left([Product],InStr([Product]," mSpec") Not Like "[!1-24]")),0,(Left([Product],InStr([Product]," mSpec")))) AS HPLR,
FROM [Peek gifts]
WHERE (((Peek.Product) Not Like "* and *"));


"[! 1-24]" is the range the number should be in between



Thank you

 

[MSAccessRookie]

Hi

here is the code



SELECT DISTINCTROW Peek.Product, Peek.*,
IIf(Nz(Left([Product],InStr([Product]," mSpec") Not Like "[!1-24]")),0,(Left([Product],InStr([Product]," mSpec")))) AS HPLR,
FROM [Peek gifts]
WHERE (((Peek.Product) Not Like "* and *"));


"[! 1-24]" is the range the number should be in between



Thank you

The first thing that I noticed is that you are using the Instr() Function in a different way than I am used to seeing it used. The Instr() Function Syntax (as I have used it) is as follows:

[B]Instr([I][COLOR=green]StartingPosition[/COLOR][/I],[/B] [B][I][COLOR=green]StringToSearch[/COLOR][/I], [I][COLOR=green]StringToSearchFor[/COLOR][/I])[/B]

Your call appears to include the String to search and the String to search for, but not the Starting Position. I have never seen it used that way, so try adding a starting position (I usually start at 1)

The second thing I noticed is a possible misplaced parenthesis in the IIf() Statement.

IIf(Nz(Left([Product],InStr([Product]," mSpec") Not Like "[!1-24]"))

Should possibly be
​

IIf(Nz(Left([Product],InStr([Product]," mSpec")) Not Like "[!1-24]"),
​

 

[raskew]

MSAccessRookie

The first thing that I noticed is that you are using the Instr() Function in a different way than I am used to seeing it used.

The Start part is Optional and, under most circumstances, defaults to 1. So, in most cases, to show a 1 is redundant and unnecessary. Here's from the Help File:

start Optional. Numeric expression that sets the starting position for each search. If omitted, search begins at the first character position. If start contains Null, an error occurs. The start argument is required if compare is specified

.
--------------------------------------------------------------
Webee,

"[! 1-24]" is the range the number should be in between

I have never personally seen this used, nor can I find an example. Would you please post a working example.
ADDED: Delete the above thought. Found examples. Sorry about that.

If ...
y = "1234mspec"
... then, using a portion of your formula
? Nz(Left(y,InStr(y,"mSpec")))
1234m

This is the same as using Left(y, 5)

To return all the characters preceding "mSpec", you'd need:
? Left(x,InStr(1, x ,"mSpec")-1)
1234
--------------------------------------------------------------------------

With the 1 - 24 qualifier, are you saying there should only be two digits preceding "mSpec"?

Would you post a couple of [Product] examples, one that would trigger a True response and one triggering False.

Bob

 

[Brianwarnock]

Not Like "[!1-24]"

Is this a double negative? Shouldn't it just be
Like "[1-24]"))

Also I have found that when ! is used problems occur in the result if there are numbers >9, infact its wierd and unexplainable by me.

Brian

 

[raskew]

Hi Brian -

Sure is a double-negative.

Also I have found that when ! is used problems occur in the result if there are numbers >9, infact its wierd and unexplainable by me.

Had the same problem. Best as I can tell, Like works only with strings and apparently on the first character.

Something like this will work, however, where [Extension] is a string.

SELECT
    LastName
  , Employees.FirstName
  , Extension
FROM
   Employees
WHERE
   (((Val([Extension])) Between 400 And 4000))
ORDER BY
   Employees.LastName;

Hoping that Webee gets back soon with a little more info.

Best wishes - Bob

 

[Webee]

Hi

Here are the product examples:-

45 Mpower - false
Vsport - false
12 mSpec - True
7 mSpec - True

For the result which is true it should display "12" or "7" and for the false records "0"

Thank you

 

[raskew]

OK -

Try this:

SELECT Peek.*
FROM Peek
WHERE (((InStr([Product],"Mspec"))>0) AND ((Val(NZ([Product]))) Between 1 And 24));

The InStr([Product],"Mspec"))>0 ensures that "Mspec" is contained in the Product field.

The ((Val(NZ([Product]))) Between 1 And 24 ensures only leading digits between 1 - 24 [Product] are selected.

Bob

 

[Webee]

Hi

I tried what you recommeded but it still does not work it show -1 instead of bring up the leading digits.

Thank you for your help

 

[raskew]

...still does not work it show -1 instead of bring up the leading digits

That's interesting. The query provided is a direct
copy of a query I created against another table, in
which I added a [Product] field and populated it with
the examples you provided. I then changed only the
table name to Peek.

Just tried it again, using the code copied from my
posting (after rechanging the table name) and it
works as advertised.

Did you copy the posted query verbatim and test it
without any changes? The fact that you're displaying
-1 indicates a true/false criteria has worked its way
in, such as could occur if an Iif() statement was
included as part of the query.

Bob

 

[Webee]

Thank you for your help Bob,

Am still having problems as I copied and pased what you recomended it just not displays the products which contain "Mspec".

so i did put in an IiF statment and it shows -1 instead of the leading digits.

How can i get around this problem.

Sorry for the very late reply been on holiday.

 

[raskew]

Hi -

I think there's something going on that we're not seeing. Probably the thing would be for you to post a sample database.

Best wishes, Bob

Added: And please be sure to include the query that's not working for you.

 

[Brianwarnock]

I cannot find anywhere in this thread exactly what the poster is trying to do, ie what is the data and what are the outcomes.
I think that if the data does not contain "mspec" he wants "0"
If it contains "mspec" and starts with 1 to 24 , not sure if thereis a space or not after and whether it matters, he wants the "1" to "24"
Else he wants "1212".

If all of that is correct then a simple function should do it.

Brian

 

[raskew]

Brian -

I agree!

The fact that I can get it to work, and the OP apparently can't, is what leads me to believe that something else is going on that we're not seeing, either in the data or in the query that's being applied. Added: Perhaps similar to the Iif() statement that wormed its way in a few posts back.

Bob