entering in manipulated data

  • Thread starter Thread starter Daniel M
  • Start date Start date
D

Daniel M

I have an access form with a field that i need modified before it get
entered into the database.

If i enter 02-123456 into the form, i want to enter 123456 into the table.
can someone help me out on how to do this? We are only logging the number
after the "-" but we enter the full number because it is scanned on a bar
code.

Thanks.
 
If you always need the rightmost six digits you could have code such as:
Me.ShortField = Right(Me.LongField,6)
If there is a variable number of digits that always follow a hyphen:
Me.ShortField = Right(Me.LongField,Len(Me.LongField) -
InStr(Me.LongField,"-")
See Help for more information about the Right, Len, and InStr functions.
If there is some other rule or set of rules that govern the number format,
you will need to provide details.

This code can be run from a command button, in the After Update event of
another control, or in any number of other places. From your brief
description it is not possible to suggest anything definite.
 
Ok this partically gets me what i need. now i face a new issues.

The control i am entering in the data too has a control source of a table
column. this table column is a number field. Because of this it will not let
me put in the '-' even though the code takes it out. is there a way to do
this?

alternately if i change the field to text instead of number i have an issue.
some serial numbers are 02-001234 and some are 02-203213. if it just strip
off the "02-" then 001234 is entered into the table, but i only want 1234. if
this is a number the leading 00 is stripped off.

So here is what i need.
entered/scanned data data put in table
02-001234 1234
02-205678 205789
1235 1235
 
If you want the hyphen you will need to use a text field. In the example
below, LongField is text and ShortField is number. You can use CInt, which
converts text to number (Integer):
Me.ShortField = IIf(InStr(Me.LongField,"-") = 0, CInt(Me.LongField),
CInt(Right(Me.LongField,6))

I doubt you should store the calculated value, although the details of your
situation aren't quite clear. A better approach would be a query that
includes LongField, with a calculated ShortFiled column:
ShortField: IIf(InStr(Me.LongField,"-") = 0, CInt(Me.LongField),
CInt(Right(Me.LongField,6))

Base a form or report on the query, and bind a text box to ShortField.
 
Back
Top