Convert String to Number on Entry in Form (1 Viewer)

[SalisburyLad]

Hi All,

I have a form that takes input to the first, indexed, field from a barcode reader (and keyboard at a pinch). The data coming in has always been numeric so I have several checks in place to ensure it is numberic (field specification) and also that it lays in a predefined range. All works well! I've now been asked to enable this to work with a single letter prefix e.g. T1234567. As there are many forms which use this same field I'd like to keep it numeric and as the prefix won't change I can add it later using a query. Is there a way to programatically strip the first letter from the string and use CLng() to convert to numeric using the on entry Event Procedure? All my other validation is here but I can't see a way to do this. Failing that, can I use an unbound text box to take the entry and set the index field?

 

[John Big Booty]

To strip the first character off use;

[URL="http://www.techonthenet.com/access/functions/string/right.php"]Right[/URL](YourField, [URL="http://www.techonthenet.com/access/functions/string/len.php"]Len[/URL](YourField)-1)

You can then use the CLng() to convert that result to a number.

 

[SalisburyLad]

John, thank you for your reply. I can see how the code works but not sure where to put it. Do I need an unbound field in the form to input the string and then use the code to transfer the number? I tried adding this code to the before update property but access still tells me it needs a number:

Private Sub AssetNum_BeforeUpdate(Cancel As Integer)
Me.AssetNum = Right(Me.AsstNum, Len(Me.AssetNum) - 1)
Me.AssetNum = CLng(Me.AssetNum)
End Sub

I'm sure I can't treat the fields as variables (as above) but this must be on the right track?

TIA

 

[John Big Booty]

I'm not currently on a machine with Access, so can't test, but I believe that;

Private Sub AssetNum_BeforeUpdate(Cancel As Integer)
     Me.AssetNum = CLng(Right(Me.AsstNum, Len(Me.AssetNum) - 1))
End Sub

Should do what you are after.

Your other option is to have an unbound field and use a similar approach to "massage" the entered data into the required field. Something along the lines of;

 Me.AssetNum = CLng(Right(Me.YourUnboundFiled, Len(Me.YourUnboundFiled) - 1))

IN the form's Before Update event.

 

[SalisburyLad]

John, try as I might, I cannot get this to work. I really would like to be able to change the field content to a number by stripping the preceeding letter which would leave all of the existing validation in place. Am I asking too much of Access?

 

[SalisburyLad]

I can make this work if I change the field to text but if I leave it as numeric an event catches the string and fires the message box for invalid for this field i.e. not numeric. I'll keep working on having to use a text field instead of number field and apart from using validation as "L0000000" I'll see if I can validate the numeric part in the VBA.

Still open to suggestions ;-)

 

[pr2-eugin]

SalisburyLad, just that you know I think the best way for you to go is use Text field.. Because using Clng function will most probably not return the result that you want.. Just a quick test..

? Right("L000001", Len("L000001")-1)
000001

? Clng(Right("L000001", Len("L000001")-1))
 1

Unless, the data start with a number other than zero after the First String, you will not end up with the result you wish to see..

? Right("L100001", Len("L100001")-1)
100001

? Clng(Right("L100001", Len("L100001")-1))
 100001

 

[SalisburyLad]

Paul, the data will start with a number. The problem I face, and I'm trying to resolve it now, is that I have several forms that have on event code that validates the data and all of them are set up to receive a number!! If I could just find an easy way to strip the preceeding letter all would be well. It just seems as soon as I press the tab key to move from the textbox, access fires a "you've done something wrong" box at me before I get chance to do something about it!!

 

[pr2-eugin]

Are you sing the code JBB provided in Post#4 ? That would not fire the error as this event is fired before the value is actually updated.. Could you show what you have placed in the BeforeUpdate event?

 

[SalisburyLad]

Paul, I have tried the code that JBB suggested but I have a simple code in the before update event just to check the principal:

Private Sub AssetNum_BeforeUpdate(Cancel As Integer)
Me.AssetNum = 99
End Sub

I've also tried the code to strip the first character but this only works if the entry is numeric to begin with i.e. 1234567 will change to 234567 but F1234567 will fire a "The value you entered isn't valid for this field" message.

Everything works so long as the original entry was numeric. It seems access checks for this ahead of everthing else!

 

[pr2-eugin]

Okay this might be a bit weird thing to do.. Create an UNBOUND control and type the value in that, and update the particular field in the UNBOUND before update event maybe?

 

[SalisburyLad]

Thanks Paul but I think changing to a text field is preferable to adding an unbound control. As I say, I do have a variety of forms that ultimately feed into that particular table. If what I'm trying to do isn't possible (and why should access allow me to type a letter into a numeric field), then the solution must be to change the field to text.

 

[SalisburyLad]

In fact, this doesn't seem to work on the before update but does work in the lostfocus event.

 

[SalisburyLad]

OK, one final update to say that as I start to test the solution (I bow to your knowledge and accept using another field to feed the original asset number).... I suddenly realize that if you enter the string with the prefixed letter in the numeric field, access just strips the letter anyway - Doh!!!! It simply filters anything that's not numeric. Simple when you know how!