String manipulation in order to run code if character exists in string 3

[kjv1611]

I would think it simple, but just haven't quite come accross the appropriate function(s) for this operation. Here is some psudocode for what I am trying to do:

Private Sub txtDNUM_AfterUpdate()
If txtDNUM has "-" in it, then
   txtDesk# = string after "-"
   txtDNUM = string before "-"
   txtName.SetFocus
End If
End Sub

Basically, if there is a "-" in the string, then I want to split the two values, setting one text box equal to the second value, and the original text box equal to the first half of the string.

Any ideas?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[RoyVidar]

Hi!
[tt]If instr(txtDNUM,"-")>0 then
[txtDesk#] = split(txtDNUM,"-")(1)
txtDNUM = split(txtDNUM,"-")(0)
txtName.SetFocus
End If[/tt]

but perhaps declare an array of string datatype, and perform the split only once?

Roy-Vidar

 

[cLFlaVA]

Here's a very similar way--

Dim pos as Integer
pos = InStr(theString, "-")
If pos > 0 Then
    MsgBox Left(theString, pos - 1)
    MsgBox Mid(theString, pos + 1)
Else
    MsgBox "no - in string"
End If

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[SkipVought]

Stephen

   a = Split(txtDNUM, "-")
   For i = 0 To UBound(a)
     select case i
       case 0
         txtDNUM = a(i)
       case 1
         txtDesk# = a(i)
       case else
         msgbox "more than 1 dash"
     end select
   Next

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[kjv1611]

Thanks, RoyVidar, here's a star! That is exactly what I was looking for. The particular application would allow a user to copy and paste the "DNUM" and "Desk#" together from another database and paste in one field, and allow access to put the correct values in the correct locations. It is fast and accurate. Thanks a ton!

Skip, I tried your code first, b/c it looked like it had a little more in the way of correcting in case the user entered too many dashes, but I was getting errors: something about expected an array with the UBound function. I dimmed a as String and i as Integer.

cLFlaVA, I did not get around to trying your code, but I may go back and try part of it later along with RoyVidar's possibly for covering for someone somehow entering more than one dash.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[SkipVought]

   Dim a
   a = Split(txtDNUM, "-")
   For i = 0 To UBound(a)
     Select Case i
       Case 0
         txtDNUM = a(i)
       Case 1
         txtDesk# = a(i)
       Case Else
         MsgBox "more than 1 dash"
     End Select
   Next

a cannot be defined as String or Integer since it is a Variant Array.

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[kjv1611]

Aaahh! So, if I have "Option Explicit" in my module, then I would need to dim that as Variant instead of String?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[SkipVought]

See previous post.

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[kjv1611]

Well, I went back and tryed Skip's code, dimming a as Variant instead of String, and it worked perfectly. Thanks for all the posts, guys! A star to Skip as well - don't know which one I'll end up going with, but they both work.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[RoyVidar]

- or, if the declaration part was aimed at my first reply, what I meant was:

[tt]dim a() as string[/tt]

- declare an array of datatype string;-)

Roy-Vidar

 

[kjv1611]

Thanks again! The whole idea is great, now I will use the Split() function and inStr() function in my beforeupdate event of the same control (but not with the same code) in order for my checking for duplicates. I already have the duplicate code, just need to update in order to cover this idea. Thanks a ton for all your assistance, ya'll!

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PHV]

dim a() as string

I disagree.
The Split function returns a variant.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Then you disagree with the VBA help files:
"Returns a zero-based, one-dimensional array containing a specified number of substrings."

I haven't the foggiest what happens behind the scenes, you might as well be correct. But declaring an array of datatype string has not produced any errors (yet), so I'll stick with that;-)

Roy-Vidar

 

[PHV]

RoyVidar
My apologies, works perfect like you said :~/

 

[sterobw]

I was hoping someone could help me with a very simple question. Is there a way to have multiple commands on a singleline. Below is the code that I would like to somehow use rather than listing each command on a separate line. However, the comma isn't an approved seperator as I keep getting a "Compile error Expected: end of statement". The function is being passed a number (in string) designating a quarter (of the year) used for employee evals. The idea behind the code is to prevent users from entering data in any other quarter than what they are evaluating.

Public Sub QuarterLock(qtrOn As String)

If qtrOn = 1 Then
c11q2.Enabled = False, c112q3.Enabled = False, c11q3.Enabled = False _
c11q2.Enabled = False, c112q3.Enabled = False, c11q3.Enabled = False _

Thank you,
Steve

 

[PHV]

The statement separator for VBA code is the colon :

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sterobw]

thank you for the quick response PHV!

I know it is a simple thing but you really made my morning a lot easier and more productive!

Steve