Substr equivalent (1 Viewer)

gmatriix · Apr 9, 2018

Hey guys,

I and trying to convert some mainframe query to Access.

I am working with:

Code:

HEX(SUBSTR(EXTRA_IN,1,3)) AS FKT_AMG1

Obviously, Access does not have a substr function...so I tried to use Left or Right....I got some results with Mid but some time I got nothing....

I tried:

Code:

HEX(MID(EXTRA_IN,1,3)) AS FKT_AMG1

Is there a better way to be doing this?

Thanks!

plog · Apr 9, 2018

Mid is the SUBSTR of access.

The reason you got nothing is because the string didn't contain anything or it wasn't a number (since you hexed it)

gmatriix · Apr 9, 2018

Thanks!

I am actually getting a error....so I did a

Code:

HEX(VAL((MID(EXTRA_IN,1,3)) AS FKT_AMG1

because I am thinking its trying to return a numeric value....and I just get a 0

What do you think?

plog · Apr 9, 2018

I think you subtract things from your expression to find out whats wrong, not add.

Create a new query:

Code:

SELECT EXTRA_IN, MID(EXTRA_IN)1, 3) AS MID3, IsNumeric(Mid(EXTRA_IN, 1,3)) AS MID3_Number
FROM YourTableNameHere
ORDER BY  IsNumeric(Mid(EXTRA_IN, 1,3))

That will show all your values, the first 3 characters of them and if those first 3 characters are numeric. The ones that aren't numeric are your culprits.

gmatriix · Apr 9, 2018

This query is getting a error about missing syntax...I will try to see why

plog · Apr 9, 2018

Missing a left parenthesis in MID3

jdraw · Apr 9, 2018

I think the syntax issue is here
SELECT EXTRA_IN, MID(EXTRA_IN)1, 3) AS MID3, IsNumeric(Mid(EXTRA_IN, 1,3)) AS MID3_Number
FROM YourTableNameHere
ORDER BY IsNumeric(Mid(EXTRA_IN, 1,3))

replace the ")" with ","

Good luck.

Substr equivalent (1 Viewer)

gmatriix

Registered User.

plog

Banishment Pending

gmatriix

Registered User.

plog

Banishment Pending

gmatriix

Registered User.

plog

Banishment Pending

jdraw

Super Moderator

Similar threads

Users who are viewing this thread