Having trouble using mid function in access 2013

[PakerBaker]

Hi there
I have a 20 character string and I want to get the numbers from position 10 to 14 when they are equal to 5600
I did my access query in design mode as follows but I keep getting syntax errors
NumbA: Mid([fieldname],9,4)
under criteria I put 5600

Could someone please help and tell me what is wrong with that ?

[hvsummer]

== you have wrong criteria..

to get 10 to 14 from string:

mid(string, 10, 4)
 

but if you want to trap condition, you have to use iif

iif(mid(string,10,4) like "5600", 5600, string)
 

number inside string is string, so you if put 5600 under criteria, that'll lead you to nowhere since criteria's wrong and data's type wrong.

[zmbd]

If Paker is only interested in "5600" and not values like "5601" etc, then instead of the Like operand I would use the Equality as the match is specific in nature and will be much faster in data returned.

The only other tweak here is to return either the Null or empty string as the result when the value isn't found at that position as OP only mentioned returning the "5600" value when such is found at that position.

H. didn't mention it; however, you would also use H's solution as a calculated field. The criteria field would not be used with this method.

[jforbes]

If this is a query, I would go about it a little different to avoid using an iif():

SELECT * FROM SomeTable 
WHERE fieldname LIKE '?????????5600*'

It's not elegant, but I think it will run better/faster.

[PakerBaker]

Thank you all for the suggestions. I will try them out today

[zmbd]

J, good point and it makes sense if PakerBaker wants the records in their entirety instead of the indicated desired return value of "5600" in the OP. I should have asked about that!

[NeoPa]

So, there are a number of issues to understand and deal with :

1. Using Mid() to get the tenth character onwards requires a second parameter of 10 rather than 9.
2. The returned value of this calculation would be a string, so criteria would have to be a string too.
3. This can be done using Like in the criteria instead of the Mid() calculation.
4. A new one - Character positions ten to fourteen would return a five-digit value not a four-digit one.

I'm sure if you deal with all of these issues it'll work fine for you.