How to read first word from a text box

[Ashfaque]

Hi,

I have 2 text boxes in a form. In first box a sentence is appearing and second text box is unbound.

In second text box, I need to read only first word of a sentence appeared in a first text box.

How can I do it?

Please advise.


With kind regards,

Ashfaque

 

[stopher]

Use InStr to find the occurence of the first space in the string and then use Left to extract the characters to the left:

=Left(Forms!myForm!FirstTextBox,InStr(Forms!myForm!FirstTextBox," ")-1)

Personally I would put the calculation in the underlying query if that's possible (easy to write & test).

hth
Chris

 

[Ashfaque]

Thanks stopher,

Its fine code. The problem is only when it displays first record it shows #Error but I moves the mouse wheel or go to next record, it is finely working. Again it comes to first rec...produces #Error.

One more thing; if there is only word in first text box then it produces same #Error.

 

[Ashfaque]

Got it...

This worked our for me...

=IIf(Len([Ename])>0,(Left([ename],InStr([ename]," ")-1)),"")

Thanks

 

[Ashfaque]

What if the first word in sentence is a number

Hi,

How about if the first word in sentence is a number instead of set of characters? In this case it displays #Error.

There is solution for this. Whats that?

Please advice.

Ashfaque

 

[mresann]

The field that the text box is referencing may contain Null data. Try the following:

=Nz(IIf(Len([Ename])>0,(Left([ename],InStr([ename]," ")-1)),""),"")

This means that if your text box contains null data, you will not get the error and instead replace it with a zero-length string.

 

[Ashfaque]

Thanks mresann,

I tried with your code but still same.

One more thing, it produces error when there is only one word or number in sentence.

Please advice.

With kind regards,

Ashfaque

 

[WayneRyan]

Ashfaque,

You can use the Split function to break down the string into
array elements (even Nulls).

Dim varArray As Variant

varArray = Split(Forms!myForm!FirstTextBox, " ")

If IsNull(varArray(0)) Then
   MsgBox("There's not even 1 word.")
ElseIf IsNumber(varArray(0)) Then
   MsgBox("It's a number.")
Else
   MsgBox("It's some string.")
End If

You can also use Len(Forms!myForm!FirstTextBox) to avoid Null string
errors.

Wayne

 

[Ashfaque]

Thanks Wayne,

What I am concern about is when there is number (as first word in sentence) and when there is only ONE word in sentence.

Both text boxes are on same normal forms where first bound to Ename field.

In your code it says, Sub or Function not defined (IsNumber). Where to put this code?. 9I tested it keeping in current event of form.

Please advise.

 

[boblarson]

In your code it says, Sub or Function not defined (IsNumber)..

That SHOULD be IsNumeric

 

[Ashfaque]

You are Bob....

I hope it will work now...

 

[Ashfaque]

It works smoothly as long as there are record goes on but when it reaches to new rec, it produces Error '94' Invalid use of null at this Red line

Private Sub Form_Current()
Dim varArray As Variant

varArray = Split(ENAME, " ")

If IsNull(varArray(0)) Then
'MsgBox ("There's not even 1 word.")
Me.Text97 = " "
ElseIf IsNumeric(varArray(0)) Then
Me.Text97 = varArray
' MsgBox ("It's a number.")
Else
Me.Text97 = LCase(varArray(0) + "@xyz.com")
'MsgBox ("It's some string.")
End If
End Sub

Reply awaited...

 

[boblarson]

Test ENAME first before trying to assign it to the array:

If Not IsNull(ENAME) Then....
Put the rest of the code shown here
End If

 

[Ashfaque]

Thanks again Bob,

Below code works 99% correct.

Private Sub Form_Current()

If Not IsNull(ENAME) Then

Dim varArray As Variant

varArray = Split(ENAME, " ")

If IsNull(varArray(0)) Then
Me.Text97 = Null

ElseIf IsNumeric(varArray(0)) Then
Me.Text97 = varArray
Else

Me.Text97 = LCase(varArray(0) + "@xyz.com")
End If
Else

Me.Text97 = Null
End If

End Sub

EXCEPT when the first word is a number in ENAME field, it returns in Textbox (Text97) null value. This is I set at the end to keep null because when it was reaching to new records, the previous record's data was still there in Text97. To clear this I set Me.Text97 = Null beffore end.

Now last thing remain is if there is any integer or any other charater appears as first word in the sentence, it should appear in Text97 suffix rest i.e. "@xyz.com".

Extend your help please.

Ashfaque

 

[Moniker]

I don't get why you aren't getting this Ash...

IF IsNumeric(Left(Text97,1)) Then...

That tells you if it starts with a number.

Really, though, you're fishing like mad but using up everyone else's bait to catch a minnow. And a textbox named Text97? Do you never want to know what that textbox holds in two months?

There are some fundamentals you're not getting here. Naming conventions for one, what IsNumeric, IsNull, etc. are doing. I have a feeling you're just copy/pasting a lot of code without understanding what you're doing, which is a giant no-no. The answer to your latest question has been given multiple times in this thread, much less on this board.

It's weird to me, I suppose, that you're struggling this much with such a simple thing, considering the length of time you've been around and the number of posts you've made. Maybe I'm just being too critical, but what is it you're trying that isn't working? Where is the code you wrote that doesn't work, not the code you copy/pasted? What is it you're after besides an exact answer? Are you trying to learn?

 

[Ashfaque]

Thanks for your REPLY Moniker,

I think just counting number of posting don't justifies the talent.
I m aware of nameing conventions.

And a textbox named Text97? Do you never want to know what that textbox holds in two months?

What is wrong there if used Textbox97?

I suppose, that you're struggling this much with such a simple thing

This happens with everybody n not only with me.

I am always trying to understand the code and its flow. To test in proper way
I am checking thru F8 keys but still facing prob.

I hope somebody might have practically tried this code and know well why it is not working.

Anyhow thanks for your posting to my query.

Regards,

Ashfaque

 

[Moniker]

Again, it's a fundamentals issue. There's technically nothing wrong with a control name like Text97, but I recently made a post about this sort of thing. Naming conventions work. The point was that, in 2-3 months, when you haven't looked at this code in 90 days, how do you expect yourself to remember what Text97 means, much less expect any other programmer to know what it means at any given point in time?

As for the other part, it's true that everyone has to start somewhere. What's easy and simple to one is completely confusing and difficult to another. The point I was getting at is that the answer you're after has been given to you. My response gave you the answer, as did several before it.

I'm not trying to be mean here. I was just not understanding how when given the answer, the same question keeps coming back up. To me, it's reading like this: "I know that 2+2=4, but how can I add 2 and 2 together?"

 

[WayneRyan]

Ashfaque,

Your problem is here:

ElseIf IsNumeric(varArray(0)) Then
Me.Text97 = varArray <-- Should be varArray(0)

Wayne

 

[Ashfaque]

Thanks WayneRyan,

It works now with your reminder to me to put varArray(0).

I had added the code line hence I missed it.

Thank you very much.

With kind regards,

Ashfaque

 

[WayneRyan]

Ashfaque,

Glad to help.

Wayne