Need Help please

[AndyShuter]

I have a field that collects data about someone's house name or number. It is set to text but will obviously store numbers as well.

What I need to do is to be able to determine whether the inputted value is either a HOUSE NO or a HOUSE NAME

For example

If input = "The Gables" Then "HOUSE NAME"
If input = "29" (or even 29A) then HOUSE NUMBER

One thing I thought would seriously narrow the options down would be to set LEN to >4 for house name, but I just wondered if anyone had any other suggestions

 

[Brianwarnock]

Could you test if the first,or should that be second, character is numeric using the Val function?

brian

 

[AndyShuter]

Dont know mate! Never used that function before!

 

[Brianwarnock]

I've assumed that your numbers / names are in quotes and thus you need to test the 2nd character, therefore have used Mid rather than Left.
The following shows a simple test.

IIf(Val(Mid([flda],2,1))=0,"Text","Numeric")

hope it helps

Brian

 

[AndyShuter]

Sorry mate, Im a complete novice at this. Here's my code would u be kind enough to help me sort it?


If (the second character is a number) of Me.Text1454 Then
Me.Text80 = "House Number"

ElseIf (the second character is a letter) of Me.Text1454 Then
Me.Text80 = "House Name"
End If

(You know what I mean!!!)

 

[Bat17]

assuming that you are not actualy storing quotes the

if isnumeric(left(Me.Text1454,1)) then
Me.Text80 = "House Number"
else
Me.Text80 = "House Name"
End If


Peter

 

[Brianwarnock]

Hum thought I'd better do a test, couldn't get the code to work, however if I put the test in the contol source of my equivalent oftext80 its ok
thus try

=IIf(Val(Mid([Text1454],2,1))=0,"House Name","House Number")

In the contol source of Text80.

Still wonder if its character 1 or 2 easy to alter tho

Brian

 

[sonny]

What happens if the user enters NE 29th Ave?

 

[Brianwarnock]

What happens if the user enters NE 29th Ave?

Yes I suppose the question is why is Andy doing this?

Got hung up on the Val forgot isnumeric

brian

 

[Bat17]

What happens if the user enters NE 29th Ave?

that would be a street name not house name/number so would go in another field.

Peter

 

[Brianwarnock]

Had the same problem with isnumeric as with Val when using VBa, flagged up as argument not optional on Left ie did not like the construct

isnumeric(Left(etc..

Got it to work by coding
mystr=left(Me.fldname),1)
If isnumeric(mystr) Then etc



brian

 

[Bat17]

str = "42A"
Debug.Print IIf(IsNumeric(Left(str, 1)), "Number", "String")

Should work OK

 

[Bat17]

Or
str = "a42A"
If IsNumeric(Left(str, 1)) Then
Debug.Print "Number"
Else
Debug.Print "String"
End If

Peter

 

[AndyShuter]

Thanks guys - it's cracked now - just one prob - "Flat 23" is required in the House number field!!!

 

[Bat17]

you need to define a rule for us to work on then does any number anywhere in the field mean that it will be a house number?
I.E. A house name cannot contain a number?

Peter

 

[Brianwarnock]

What I need to do is to be able to determine whether the inputted value is either a HOUSE NO or a HOUSE NAME

I would like to repeat my question as to why you want to Know this.

Brian