What is hapening?

[Ray C]

I have an Unbound Text Box called "Find_SerialNo" for the user to enter the
Serial No of the equipment they want to find in the table. Because some
serial numbers are Numeric and Some Serial Numbers at Alpha numeric, I have
designated the Serial Number field in the Table to be a String.
If I enter "12abc345" in the "Find-SerialNo" text box the rest of the code I
have written finds the record. No problem. If I enter "1234" the code I have
written will not find the Record Serial No "1234" because I entered a Numeric
value. OK makes sense to me so the answer is to force the numeric input to be
a string

Private Sub Find_SerialNo_AfterUpdate()
Dim FieldInfo As String

FieldInfo = Find_SerialNo
FieldInfo = Str(Find_SerialNo)
'Find_SerialNo = Right(Find_SerialNo, Len(Find_SerialNo) - 1)
' Find_SerialNo = Format(Find_SerialNo)
If IsNumeric(FieldInfo) Then Debug.Print ; "Number"
If Not IsNumeric(FieldInfo) Then Debug.Print ; "String"

I have tried everything I can to convert the numeric input into a string
(as above) but the test "If IsNumeric(Field Info) always Prints "Number"

Anyone know what is going on?

Thanks and regards RayC

 

[John Spencer]

Is numeric will check a string to see if it can be converted to a
number. So, IsNumeric("12345") will return true, while
IsNumeric("12345a") will return false.

I think there must be some other cause for your problem. Try posting
the remainder of your code and try changing the search criteria to
LIKE "1234*" to see if you have any success.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[missinglinq via AccessMonster.com]

ID "Numbers" like phone numbers and SSNs should always be strings! You should
really only use numeric data types if you're going to be doing mathematical
operations with the data. As to your current problem, John is correct is
saying that that you have some other cause for your problem. A string is a
string is a string! The simplest thing to do would be to use a wizard
generated combobox to retrieve your records, using the third option "Find a
record based on the value I selected in my combobox."

 

[Ray C]

Thanks for the response. As this response is jointly to John and yourself,
please advise if I need to reply to John seperately or if he will receive
this.

Before I go on to find the data, I am testing the contents of what will
become the "Criterion" in my Query (the contents of "Find_SerialNo"). The the
test :-

If Isnumeric(Find_SerialNo) then
Debug.Print; "Number"
Else
Debug.Print; "String
EndIf
returns "Number" if the entry is "1234" and String if the entry is "A1234".

I am sorry but I have spent so much time on this that I am becoming obsessed
with trying to find out what stupid mistakes I must be making.

to further expand on my frustration I print out some code that seems quite
correct to me but returns the wrong answer. I know that there are much
shorter ways of writing this but I have expanded the code to try and see what
is going on. "FieldInfo" is the contents of the "Find_SerialNo" Text Box. My
comments are in the Far left.

If IsNumeric(FieldInfo) Then
Debug.Print FieldInfo; " = Number"
Else
Debug.Print FieldInfo; " = String"
End If
This is the pre proccess test and returns "Number" if "FieldInfo" contains
"1234" and "String" if "Field Info" contains "A1234"

if IsNumeric(FieldInfo) Then
FieldInfo = Str(FieldInfo)
"FieldInfo" contains " 1234" (leading Space as would be expected)
FieldInfo = Right(FieldInfo, Len(FieldInfo) - 1)
"FieldInfo" contains "1234" (no leading Space as would be expected)
End If

If IsNumeric(FieldInfo) Then
Debug.Print FieldInfo; " = Number"
Else
Debug.Print FieldInfo; " = String"
End If
This is the post proccess test and returns "Number" if "FieldInfo" contains
"1234" and "String" if "Field Info" contains "A1234"

In my mind the contents of FieldInfo at the start (shown as "1234" when I
step through the code) if a number, should have been converted to a String by
the centre piece of code (and again is shown as "1234" when I step through
the code). However, the test still says that "1234" is a Number.

Frustrated RayC

 

[John Spencer]

IsNumeric does not test the Date Type of a variable, it tests whether or not
the contents of the variable can be interpreted as a number (that can be
used in a math operation). So the string "1234" and the number value 1234
will both return True when tested with IsNumeric.

Here is a small sample (first column is the result T or F and second column
is the value being tested)
- TestValue
F "a"
F Null
T 1
T "1"
T " 1 "
F "1 1"

Beyond that I cannot help you, since I don't know what you are doing with
your query. I can only guess that you are introducing an unwanted space at
the beginning of the comparison string when you use Str("1234") as that
returns a string of the numbers with a leading space. Unless you trim the
leading space off - Trim(FieldInfo) -, you will end up searching for "
1234" instead of searching for "1234". Those are two different values (note
the leading space on the first one.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ray C]

John
Thank you for this, you have answered something that has been driving me
nuts. I must have something else wrong in the way I am searching my Table. I
will check that out but what you are sauing here, I think, is that it does
not matter what is in the Criterion (String or Number) those contents should
work to find information that is stored in a table where the field being
searched is held as a String.

Thank you both for you kind support, I realy appreciate the information and
background you guys provide.

RayC

 

[John Spencer]

No, I am not saying that it doesn't matter what is in the criterion. IF the
field that is being searched is a text or memo field then it must be a
string in the criterion. What I was saying was that you did not seem to
understand how the IsNumeric function worked.

Just because you have a string that is all number characters, does not mean
that the string is anything other than a string.

Sorry to belabor the point.

If you care to post more of your code or the SQL statement you are having
problems with, perhaps someone can suggest a solution. But without further
information, I have no further suggestions.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ray C]

John, I thank you for your valuable input and explanation of my
misunderstanding of IsNumeric. I have allowed my frustration to take me away
from the original problem I was having and I need to sit back and re-think
the original issues. Thank you all so much for your help.

Regards RayC