Split out first 11 numbers in a field?

[KevinSlater]

Hi,

I have some fields within an access query, one of the fields is named: “COM_DATA_KEY”. This field is within a linked Oracle table named: EXT-COM_EXTRA_DATA_FIELDS

The problem is the whole field contains 11 numbers and the first eight characters of the number are a PO number and the last 3 numbers are an Item number. (example P0000100010)

What I would like to do is split out the first 11 numbers so that I can have just the PO number displayed in this filed or in a new field, and I’d also like to split out the last 3 numbers so that I can have just the item number in one new separate field in the query.

I’m not sure what function I need to do, any help would be great.


This is the SQL i currently have:

SELECT [EXT-COM_EXTRA_DATA_FIELDS].COM_DATA_TABLE, [EXT-COM_EXTRA_DATA_FIELDS].COM_DATA_KEY, [EXT-COM_EXTRA_DATA_FIELDS].COM_DATA_FIELD_NO, [EXT-COM_EXTRA_DATA_FIELDS].COM_DATA_VALUE
FROM [EXT-COM_EXTRA_DATA_FIELDS]
WHERE ((([EXT-COM_EXTRA_DATA_FIELDS].COM_DATA_FIELD_NO)=110) AND ((Trim([EXT-COM_EXTRA_DATA_FIELDS].[COM_DATA_TABLE]))="POP_ITEMS"));

 

[rainman89]

is it always 11 numbers and a letter? Or are there other letters in the order # also??

Take a look into the left, right, and mid functions.. they might give you an idea

 

[namliam]

On the access side look into the Mid/Right/Left/Len functions
In Oracle look into the Substr/Length functions

Good luck!

 

[MSAccessRookie]

...What I would like to do is split out the first 11 numbers so that I can have just the PO number displayed in this filed or in a new field, and I’d also like to split out the last 3 numbers so that I can have just the item number in one new separate field in the query.

As long as the field will have exactly eleven characters, you should take a look at the Left() Function to get the first eight characters to use as the PO Number, and the Right() Function to get the last three characters to use as the item Number.

 

[MSAccessRookie]

On the access side look into the Mid/Right/Left/Len functions
In Oracle look into the Substr/Length functions

Good luck!

Oracle also allows Left(), Right(), and Seg()

 

[KevinSlater]

its always a toal of 11 numbers. first eight characters are always the Order number and the last 3 numbers are always an Item number

 

[KevinSlater]

I dont want to modify anything in the oracle tables, only within the access query.

ok i have a look into the left and right functions, something such as TESTFIELD: Left([EXT-COM_EXTRA_DATA_FIELDS].[COM_DATA_KEY]) i guess

 

[MSAccessRookie]

I dont want to modify anything in the oracle tables, only within the access query.

ok i have a look into the left and right functions, something such as TESTFIELD: Left([EXT-COM_EXTRA_DATA_FIELDS].[COM_DATA_KEY]) i guess

The Syntax of the Left() Function is Left(StringtoRead, Number ofCharacters).

The Syntax of the Right() Function is Right(StringtoRead, Number ofCharacters).
​

Try them out and see if you can get them to give you what you are looking for.

 

[KevinSlater]

ok great, it seems like ive got it working now:

Purchase Order: Left([EXT-COM_EXTRA_DATA_FIELDS].[COM_DATA_KEY],8)

Thanks all for your quick replies to my post

 

[namliam]

Oracle also allows Left(), Right(), and Seg()

Not in the version we are using , but be some newer version, though we are running somewhat behind I think...

 

[MSAccessRookie]

Not in the version we are using , but be some newer version, though we are running somewhat behind I think...

Really ??? Those are such Basic Functions that I thought they always were there.

 

[KevinSlater]

This works
Purchase Order: Left([EXT-COM_EXTRA_DATA_FIELDS].[COM_DATA_KEY],8)

But this doesnt:
Item Code: Right([EXT-COM_EXTRA_DATA_FIELDS].[COM_DATA_KEY],3)

Theres no data in Item code, any ideas why? and how i could fix this?

Is it not looking at the data correctly, or looking at some blank spaces at the end instead perhaps,

 

[rainman89]

This works
Purchase Order: Left([EXT-COM_EXTRA_DATA_FIELDS].[COM_DATA_KEY],8)

But this doesnt:
Item Code: Right([EXT-COM_EXTRA_DATA_FIELDS].[COM_DATA_KEY],3)

Theres no data in Item code, any ideas why? and how i could fix this?

Is it not looking at the data correctly, or looking at some blank spaces at the end instead perhaps,

Do you have blanks at the end of the field?

 

[KevinSlater]

No I dont believe so, unless there is some blank data at the end but i cant actually see them within access.

The table the query looks at is an Oracle table (which i cant modify)

 

[rainman89]

If you increase this number

Item Code: Right([EXT-COM_EXTRA_DATA_FIELDS].[COM_DATA_KEY],3)

Does anything show up then?

 

[namliam]

How about adding trim or RTrim?? To make sure you remove and spaces....

Item Code: Right(trim([EXT-COM_EXTRA_DATA_FIELDS].[COM_DATA_KEY]),3)

 

[namliam]

Or offcourse use the Mid or Substr functions

Item Code: Mid([EXT-COM_EXTRA_DATA_FIELDS].[COM_DATA_KEY],9,3)
or
Item Code: Substr([EXT-COM_EXTRA_DATA_FIELDS].[COM_DATA_KEY],9,3)

** Disclaimer **
You may need to change 9 to 8, I am always messing that up. One way or another...

 

[KevinSlater]

Thanks,

Item Code: Right(trim([EXT-COM_EXTRA_DATA_FIELDS].[COM_DATA_KEY]),3)

did the trick fine