Criteria for all numbers or letters

[BWAT]

Hello, I am a new member...thanks for any help anyone can provide.
I have a table with a column in which the fields will come in with various lengths and combinations of characters. I need to look up certain values from this field, and assign a value to another field based on that. For example if field1 begins with an "H*", then field2 will equal "INS".

My problem is that I need to write a statement saying something like if field1 starts with "H" and is followed by only numbers for any length, then assign field2 "blank"

so I cant use "H*" because that includes letters...and Ive tried criteria like "H[!a-z]*" but cant get anything to work(i know the last example only looks at the space after H) Does anyone have any ideas?

 

[KenHigg]

Looks like you'll have to test the first char 'and' the 2nd char...

If you have several of these combos to test for I'd suggest writing a function to convert it...

 

[CraigDolphin]

In the textbox on your form where you enter the data for field1 (or tweaked to work in a public function that you call from a query), you could use something like

Private sub txtField1_AfterUpdate()
dim Remnant as String

Remnant= Nz(Right(txtField1,Len(txtField1)-1),"")

Select Case Left(txtField1,1)
Case "H"
  If Len(Val(Remnant)) <> Len(Remnant) then 'there must be a non-numeric character 
    txtField2 = "INS"
  Else
    txtField2 = Null
  End if
Case Else
  txtField2 = "Something other than H was the first character"
End Select
End Sub

 

[DALeffler]

Expr1: IIf(Left([Field1],1)="H" And IsNumeric(Mid([Field1],2)),"",[Field1])

Assigns Expr1 as zero length string if first character of Field1 is "H" and Field1 from the 2nd character to the end of Field1 can be evaluated as a number, otherwise Expr1 = Field1.

 

[BWAT]

Thank you all...I will try the above suggestions