VB/Module - If StrComp

[Moxioron]

Hello all.

I am importing a textfile and everything is working great except I fear there may be a limitation to the number of variables I have in my string:

If StrComp(Mid(strFileData, 33, 2), "70", vbBinaryCompare) = 0 Or StrComp(Mid(strFileData, 33, 2), "50") = 0 .... ..... Then

It works fine and then the code wraps and it quits working. Is there a limit to the number variables you are considering?

Thanks for your help.

 

[Norie]

What are you actually trying to do there?

Are you sure you need the StrComp function?

If you have a lot of these comparisons where the only change seems to be the number in quotes you might want to look at some other method.

 

[boblarson]

I'm with Norie on this. Why not be able to do this instead:

Select Case Mid(strFileData, 33, 2)
Case 50, 70
   Do whatever here
Case Else
   Do if it doesn't match here
End Select

Or if text:

Select Case Mid(strFileData, 33, 2)
Case "50", "70"
   Do whatever here
Case Else
   Do if it doesn't match here
End Select

You can also use ranges if necessary:

Case 50 To 70, 73, 77, 90 To 110

etc.

 

[Moxioron]

So basically what I am trying to accomplish is to import any data on the same row as the strFileData that I select in column 1 and is five wide.


If StrComp(Mid(strFileData, 33, 2), "70", vbBinaryCompare) = 0 Or StrComp(Mid(strFileData, 33, 2), "50") = 0 Then
strOfficerOne = Mid(strFileData, 1, 5)
End If

What I have above works for 70 and 50. However, I have like 15 of them. When I was entering them, the like wrapped and I got an error.

Hope that made sense. Thanks for your help.

 

[boblarson]

What I have above works for 70 and 50. However, I have like 15 of them. When I was entering them, the like wrapped and I got an error.

Hope that made sense. Thanks for your help.

Yeah, it makes sense and I hope my answer about using the Select Case statement makes sense because it is a whole lot more efficient than your long code.

 

[Norie]

I'm afraid I'm not quite catching on.

Is this what you want to do?

If characters 33 & 34 of strFileData match "50", "70",... then set strOfficerOne to the first 5 characters of strFileDate.

strTest = Mid(strFileData, 33, 2)
 
Select Case strTest
      Case "50", "70", "80" ' etc
             strOfficerOne = Left(strFileData, 5)
      Case Else
             ' do nothing or something else, eg strOfficerOne = "N/A"
End Select

Apologies to Bob for 'borrowing' his code.

 

[Moxioron]

I have never used Case, so please excuse my ignorance on the subject.

Thank you for your help. It appears to have worked.

 

[Moxioron]

Well I got it to work somewhat. I had an issue with one of the fields.

I had a date field that I had the Data Type set as Date/Time. The import would stop at the point where that particular field had no data in the text file.

I changed the Data Type to Text and it worked. But wish the table could/would accept the information in the proper Data Type instead of all text.

 

[Moxioron]

Worked great. Thank you so much.

Question for you though. I put a MsgBox at the beginning and the end of the code.

-The operator is prompted "Are you sure you want to convert file'
-If Yes is selected the conversion takes place and then 'conversion complete' is displayed ... no issues
-If No is selected, 'conversion cancelled' is displayed, but after the operator selects Okay, 'conversion complete' still pops up. It doesn't convert the file, but I don't want the MsgBox to pop up.

Where am I going wrong with this code? Thanks for your help.

Loop Until EOF(FH)
Else
MsgBox "KS0734 Text File conversion cancelled", vbOKOnly
End If
Close #FH
Close #FH2
MsgBox "KS0734 Text File File conversion complete."
End Sub

 

[boblarson]

Add this part in red:

Loop Until EOF(FH)
Else
MsgBox "KS0734 Text File conversion cancelled", vbOKOnly
Exit Sub
End If