only want to write the 1st 7 characters

[krberube]

users are using a hand held scan gun to enter data into a field. if the barcode is 12 characters long, I only want the 1st 7 to be written to the table.

I was thinking a validation rule using TRIM or LEN but can't seem to get it to work.

Any ideas?
Thanks

 

[trucktime]

Barcode

You can use the LEFT function.

=Left([yourfield],7)

 

[krberube]

almost there

I thought I just forgot the = sign. but i think i missed something else.

would this go in the validation rule box of the table, or do I put this somewhere else.
If i put this is the validation rule of the table, when a user scans data that is over 7 characters i get an error.

 

[krberube]

almost again

This is the last item keeping me from posting my DB as complete ( which makes this even more frustrating!)

is this an expression that should go in a form or table? in an event procedure or a validation rule?

Thanks in advance

 

[The_Doc_Man]

The question is, how are you getting data into the field?

If you are directing the info raw from your barcode scanner to a table, you have too much data and Access doesn't like too much data.

If you are doing this through a form, the question is, how do you know that you've gotten the data from the scanner? Whatever event triggers the data storage action can include a bit of VBA code that trims the field.

The trick is, when you get 12 characters of input, you CANNOT DISCARD IT when it comes from a raw device. You have to take what you get and trim it after the fact. If you are doing this through a form, that is easy. If you are doing this any other way, you've got a problem.

 

[krberube]

yes this is being done trough a form ( maybe a DAP in the future)

Kevin

 

[krberube]

I have been toying with this:

in a form,
scan the data to an unbound control
then move the data to my bound control with --- =left(unbound,6)

This works on the form, but doesn't write the data from the bound field to the table.

Thoughts?

 

[The_Doc_Man]

It isn't enough to put the data into a bound control. Your next step must be to tell Access to store this as a new record.

If you just store the leftmost 6 or 7 characters from your scanner when you catch the "scanned" signal, then do it again, then do it again, all that looks like is that you have changed your mind a few times about the value from the field. Which Access is explicitly designed to allow if you don't take special action.

I'm not saying you have to have a separate manual action to store the record. That can be automated with a DoCmd to save the current record. But you just cannot assume that storing a new value in a field automatically updates the underlying record. It does not.