Deleting Characters from Numeric Strings (97)

Topic

New Reply

Hi, in a query I am trying to write an expression which deletes the last two digits from a variable length numeric string – I know there’s a function out there somewhere, but can find it……….. Can anyone help??? Thanks!

Reply | Quote

Replies

Well if is a true string then you can do this:

strNumber = left$(strNumber, len(strNumber)-2)

Reply | Quote

Hi thanks very much for help. I’m sorry to be obtuse…. its that Friday afternoon feeling….(well, daily feeling in my case!) What I am trying to do is this – I have an inventory id number field which may vary from 3 to however many in length. Taking away the last two digits of this ID gives me another code which I need to use as a link to another table. I have been trying Left(([Inventory_Id],4)) but obviously this doesn’t allow for the variable length of the string. Can you tell me how I would fit the Len function into this scenario?

Reply | Quote

Based on the fact that you [] around the Field name, it looks like you are doing this in a Query, correct?

If so, you will need to use this:

TrimmedInvNo: Left$([Inventory_Id],Len([Inventory_Id])-2)

If you are doing it in VBA code then you will need to use this:

lngTrimmedID = cLng(left$(cStr([Inventory_ID]), len(cStr([Inventory_ID])) -2))

The cStr converts a Numeric value to a string, so you can use string manipulation on it.
The cLng converts the final trimmed string back to a Long Integer.

Reply | Quote

Hello again – having slapped myself around head very hard indeed, now see what you were trying to tell me! thanks very much worked a treat!

Marion

Reply | Quote