Numbers within a text field

[mpnorris]

I have a field where I need to accept letters and numbers. I setup the datafield as text. Is there a way that I can evaluate what was entered in the field as to whether it is letters or numbers?

The scenrio is if letters are entered I want to trigger one thing, but if the numbers that are entered fall within various ranges something else is triggered.

I know how to do this with just numbers, but the combination is what I am unsure of.

Any help will be greatly appreciated.

 

[Tranman]

Here's an idea...
You could declare two global boolean variables--blnLetter and blnNumber.

In the gotfocus event of the textbox, you could set both booleans to false.

Then put some code like this in the keypress event of the textbox:

Private Sub ID_KeyPress(KeyAscii As Integer)
'The following takes care of upper or lower case
'(Just in case)
Select Case Asc(UCase(Chr(KeyAscii)))
Case 65 To 90
blnLetter = True
Case 48 To 57
blnNumber = True
End Select
End Sub

Then in the lostfocus of the textbox (or whatever you could use to tell when you were done entering text), you could do something like:

if blnLetter then
<whatever you do for letters>
end if

if blnNumber then
select case val(txtwhatever)
case 1 to 50
do whatever1
case 51 to 99
do whatever2
case is > 99
do whatever3
end select
end if

Of course, this assumes that either all letters or all numbers are entered. If a mix of both were entered, you would have to do something like:

If blnLetter and blnNumber Then
<whatever>
end if

Anyway, I think that's what I might do...

Tranman

 

[mpnorris]

Thank you for the quick reply. I will try it out and let you know.

Thanks again.

 

[MichaelRed]

? IsNumeric(&quot;0123&quot
True
? IsNumeric(&quot;123a&quot
False
? IsNumeric(&quot;777.325&quot
True
? IsNumeric(&quot;$123.45&quot
True

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[mpnorris]

Michael;

I don't understand where you were going with your response. I am not a programmer, I need things explained. Thanks.

 

[MichaelRed]

the finctoin 'isnumeric' is included in all vb / vba packages. it simply returns a boolean [True | False] based on an evaluation of the supplied argument. if the supplied argument can be cast (or coereced) to represent a number, it returns True, if otherwise it returns false. I was merely 'illustrating' it's use with a small set of arguments. The use of this function is not really &quot;programming&quot;, as it may easily be used in many different situations, such as the control source of a option button of check box, however you can also use it in a programming context.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[mpnorris]

Tranman - I need more help. I am trying to work with your code and I have a few problems. I understand the code that you suggested, but I am trying to come up with my next steps and I am having a hard time. Hopefully I can explain my situation clearly enough.

When I first put this entry screen together I had a text box for the user to enter a batch number (txtBatch_Num) and a dropdown list where the user chose a location (cmbLocation_ID).

I now want to use some logic in the entry screen to prevent the user from having to choose the location from the dropdown list. The two manual steps of entering a batch # and choosing a location opened the user up for error. The batch number drives what location needs to be chosen.

1. I have a text field (txtBatch_Num) that in some instances could except 5 letters, 5 numbers or a combination of them both.

2. If the txtBatch_Num is letters or a combination of letters and numbers, then cmbLocation_ID should equal 1 (New York). (Should I still use a combo box? How can I visually show the location that is in a table?)

3. If the txtBatch_Num is numbers, then different scenerios determine what should be in cmbLocation_ID. For example: If txtBatch_Num is numbers and the range is 00000 - 19999, then cmbLocation_ID is equal to 2 (Delaware). If txtBatch_Num is numbers and the range is 20000 - 29999 then cmbLocation_ID is equal to 3(Electronic). I think you get the point. In a Location table I have Location_ID & Location_Name. I would like to show the location name on the form based on what the batch number says.

I have other variables that will paly into my scenerios but I want to take one step at a time.

As you can tell by my questions I am not a programmer. I understand it when I see it but creating the code is where I fall short. This little database I thought I was creating has gotten bigger then expected.

Any help you can supply would be greatly appreciated.

Thank you in advance for your help.

 

[Tranman]

mp,
You have users that make keying errors??? I can hardly believe it

Let's start with the assumption that you were able to create the global variables blnLetter and blnNumber, and are setting them to false in the GotFocus event of txtBatch_Num.

Your rule 2 simplifies this logic quite a bit, because any letter (blnLetter = true) means that the location is 1/New York.

BTW, the answer about the combo box is no. If you are only going to display one piece of data, a textbox is fine.

So, in the LostFocus event of txtBatch_Num, you need some logic like:

Private sub ??????(the LostFocus event of txtBatch_Num)
Dim strLoc_Id as String

Select case blnLetter
Case True 'at least one letter in textbox
txtLocation_Name.SetFocus
txtLocation_Name.Text = &quot;New York&quot;
txtLocation_Id.SetFocus
txtLocation_Id.Text = 1
Case False 'no letters present
Select case val(txtBatch_Num.Text
Case 0 to 19999
strLoc_Id = 2
txtLocation_Id.SetFocus
txtLocation_Id.Text = 2
GetLocName(strLoc_Id) 'Go get the location name
Case 20000 to 29999
txtLocation_Id.SetFocus
txtLocation_Id.Text = 3
strLoc_Id = 3
GetLocName(strLoc_Id)
Case 30000 to 39999
strLoc_Id = 4
txtLocation_Id.SetFocus
txtLocation_Id.Text = 4
GetLocName(strLoc_Id)
.
.
Case Else
msgBox &quot;Error-Bad Location&quot;,vbokonly
txtLocation_Id.SetFocus
End Select
End Select
End Sub



Then you need another subroutine:

Private Sub GetLocName(Loc as String)
dim rs as recordset
Set rs = CurrentDB.OpenRecordset(&quot;Select Location_Name from Location where Location_ID = '&quot; & Loc & &quot;';&quot;
Select case rs.EOF
Case True
MsgBox &quot;Location &quot; & Loc & &quot; Not in Location Table&quot;
case False
txtLocation_Name.SetFocus
txtLocation_Name.Text = rs.Fields(&quot;Location_Name&quot
End Select
rs.Close
Set rs = Nothing
End Sub

This should start you on your way to a solution. I assumed Access 97. If not, you will need some different recordset code in the GetLocName sub.

Feel free to write back if you have problems with this (or if you are not using Access 97) and I will try to make a little database and form to replicate what you are doing. It is a little bit hard to make sure that you are remembering all of the things you need to do without being able to run the code and see what happens...

By the way, I understand what you mean about understanding the code when you see it, but having trouble writing it from scratch. Lots of people suffer the same problem.

Also the exponential growth of a &quot;little&quot; project. They can sometimes get a life of their own.

Good Luck. Let me know if this doesn't work and I'll try to straighten it out.

Tranman (Paul)

 

[mpnorris]

Thank you for your response. I was reading your code andI have a feeling I may have left out a bit of information that may effect the way the code is structured.

When I spoke about the dropdown box, I need to store the value of that dropdown box in the database for later use in pulling reports. I am unsure in your code where this is happening. Perhaps if you explain the first select case a little more I may understand better.

I could also send you a copy of my database if it would be clearer to see what I am trying to accomplish.

OR if you would find it easier to send me a sample that would be fine as well.

My email is mpnorris@optonline.net.

Once again thank you for your help.

 

[mpnorris]

Tranman - Haven't heard back from you. I could still use the help. If you don't have the time please let me know and I will go elsewhere.

 

[mpnorris]

I have received an error message when I try to enter some data into the entry screen.

Once I tabbed oof of the txtBatch_Num field, I received an error hat says I have not defined the txtLocation_ID variable. When I was reading over your suggested code, this is where I got a little confused.

If I understand this correctly, you are trying to set the Location_ID field in my database to the correct number which represents a location (1= New York, 2 = Delaware, etc.) You reference it as txtLocation_ID but it is not a textbox on my entry screen. It is only a field in my main table. I am not sure why you labeled it &quot;txtLocation_ID&quot;

I was hoping you or someone could explain the code a little bit. I was wondering if I explianed it incorrectly previously.

I hope we can continue a dialog. I need to get this finished and I am concerned that I won't get clarity in time.

Please respond as soon as you can. I appreciate any help that can be given.

Mike