Field Comparison Troubles 1

[awelch]

I have a tab delimited file that I am reading that now looks like this( I have managed to strip out all the rubbish):

0230    0233
544     546
0001    0050
A367    0865

I am stuck on how to compare field1 against field2. If field2 is larger than Field1 I need to print field1 through field2. ie:
0230
0231
0232
0233
544
545
546


Another issue is the Alpha character that is thrown in. If either field has an alpha charater(only the first character of either field will be) then I need to print field1 then field2 with an *.

A367*
0865*

This has been driving me crazy for 3 days! After the great help last time, figured I could muddle, but enough is enough...

Any help would be greatly appreciated.

Thanks in advance.

Andrea

 

[CleoMan]

Concerning the alpha character problem, could'nt you load the value of field 1 and field 2 into variables and then test for numeric?

Ex:

Dim Field1 as string, Field2 as string

'get the values for field1 and 2

If isnumeric(Field1) OR isnumeric(Field2) then
'the fields need no formating
else
Field1 = Field1 & "*"
Field2 = Field2 & "*"
end if

Maybe I didnt understand the question...
Anyway hope this helps

 

[poltergeist]

It's a little confuse:

First you will sort all values in a alphanumeric order and then you mark all not numeric values with a star. Is this right ??? Or non numeric values aren't member of the sorted column.


peterguhl@yahoo.de

 

[fredericofonseca]

Picking up on CleoMan code

Just a "NON WORKING" example that should give you enough to work with. Note that you have not mentioned what to do if field 2 is NOT greater than field 1.

Sub sample()
Dim a1 As Integer
Dim a2 As String
Dim field1 As String
Dim field2 As String


If IsNumeric(field1) Or IsNumeric(field2) Then
Call numeric_field(field1, field2)
Else
field1 = field1 & "*"
add_item (field1)
field2 = field2 & "*"
add_item (field2)
End If
End Sub
Sub numeric_field(field1 As String, field2 As String)
Dim iField1 As Integer
Dim iField2 As Integer
Dim i As Integer

iField1 = CInt(field1)
iField2 = CInt(field2)

If iField2 > iField1 Then
For i = iField1 To iField2
Do
Call add_item(CStr(i))
Next i
End If

End Sub
Sub add_item(item As String)
listitem(Index) = item
Index = Index + 1
End Sub



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[poltergeist]

Private Sub Form_Load()
Dim test(1, 3)
Dim i As Integer
test(0, 0) = "0230"
test(1, 0) = "0233"
test(0, 1) = "544"
test(1, 1) = "546"
test(0, 2) = "0001"
test(1, 2) = "0050"
test(0, 3) = "A367"
test(1, 3) = "865"

For i = 0 To 3
If Not IsNumeric(test(0, i)) Then test(0, i) = test(0, i) & "*"
If Not IsNumeric(test(1, i)) Then test(1, i) = test(1, i) & "*"
Next i
End Sub

Looking your example you don't sort your values. If field1 is minor of field2 you are define a Range because

0230 0233

you transform into

0230
0231
0232
0233

OK this is possible but this you only can do with numeric values so what do you do if some Value isn't numeris

For i = 0 to 3
on error goto NoNumeric
If val(test(0,0)) < val(test(1,0)) then
for y = val(test(0,0)) to val(test(1,0))
debug.print y
next y
end if
NoNumeric:
next i

peterguhl@yahoo.de

 

[poltergeist]

Hope that is you need
Private Sub Form_Load()
Dim test(1, 3)
Dim i, y As Integer

test(0, 0) = &quot;0230&quot;
test(1, 0) = &quot;0233&quot;
test(0, 1) = &quot;544&quot;
test(1, 1) = &quot;546&quot;
test(0, 2) = &quot;0001&quot;
test(1, 2) = &quot;0050&quot;
test(0, 3) = &quot;A367&quot;
test(1, 3) = &quot;865&quot;
For i = 0 To 3
If Not IsNumeric(test(0, i)) Then test(0, i) = test(0, i) & &quot;*&quot;
If Not IsNumeric(test(1, i)) Then test(1, i) = test(1, i) & &quot;*&quot;
Next i
For y = 0 To 3
If Val(test(0, y)) < Val(test(1, y)) And IsNumeric(test(0, y)) And IsNumeric(test(1, y)) Then
For i = Val(test(0, y)) To Val(test(1, y))
Debug.Print Format(i, String(Len(test(0, y)), &quot;0&quot)
Next i
End If
Next y
End Sub

peterguhl@yahoo.de

 

[awelch]

Clarity, I hope follows:

I have a VERY large tab delimited file. I have stripped out all the garbage and kept the 2 fields that I need. Lets call them LOW, and HIGH. ( I cheated and did this by importing into a db and did my cleaning there.)But need the final input and output files to be txt...

I am unclear on how to get the Line Input to stop at the first Field(LOW). Put LOW into a variable then grab the second Field(HIGH). Then print LOW , fields between, and High.

Maybe once I can do this I can figure out how to validate that the fields are numeric.

 

[poltergeist]

Sub readfile()
Dim Fnum As Integer
Dim Youfile As String
Dim i
Dim Readstring, readline

Dim test
i = 0
Fnum = FreeFile
Open Yourfile For Input As Fnum
While Not EOF(Fnum)
ReDim Preserve test(1, i)
Input #Fnum, Readstring
readline = Split(Readstring, vbTab)
test(0, i) = readline(0)
test(1, i) = readline(1)
i = i + 1
Wend
Close #Fnum
' now you have a array (test) with your values
End Sub


peterguhl@yahoo.de

 

[awelch]

Thanks for the help Poltergeist.

I have added your code and am getting the following error.

Run-time error '9' Subscript out of range.

I added this:

For i = readline(0) to readline(1)
Print #2, readline(i)
Next

the debugger is highlighting the Print statement.

i does = 200 (this is missing the first 0)
readline(0)= 0200
readline(1)= 0999

Any ideas.

Thanks again..

 

[awelch]

Figured out the error.

I am getting output, but where the fields that begin with zero are losing that first character in the output. ie.

0200 0999

is translating to

200
201
202
etc....

How can I get it to keep the input field &quot;intact&quot;?

Thanks again, Poltergeist. Here is a star for the main issue resolution.

Andrea

 

[poltergeist]

Yes show this line

Debug.Print Format(i, String(Len(test(0, y)), &quot;0&quot)

It will preserve your value format


peterguhl@yahoo.de

 

[awelch]

I am still not getting the 0 to appear in front of certain numbers. I didn't mention before, but there could be multiple 0's as well.

Here is part of the code, can you show exactly where to format to keep the whole string?

Do While Not EOF(1)
   ReDim Preserve Test(1, i)
   Line Input #1, readstring
   readline = Split(readstring, vbTab, 4)
   
   Test(0, i) = Val(readline(0))
   Test(1, i) = Val(readline(1))
   
     For i = Val(readline(0)) To Val(readline(1))
        Print #2, i
        i = i + 1
     Next
   Loop
  
  Close #2
  Close #1

Thanks again.

 

[poltergeist]

Do While Not EOF(1)
ReDim Preserve Test(1, i)
Line Input #1, readstring
readline = Split(readstring, vbTab, 4)

Test(0, i) = Val(readline(0))
Test(1, i) = Val(readline(1))

For i = Val(readline(0)) To Val(readline(1))
Print #2, format(i,string(len(readline(0)),&quot;0&quot
i = i + 1 '<- you are sure to need this
Next
Loop

Close #2
Close #1


peterguhl@yahoo.de

 

[awelch]

I know this may be pesky but....


How do I spit out any fields that start with a alpha character instead of a number?

Andrea

 

[poltergeist]

What do you mean with split out?
- you will elimate the alpha character
- eliminate the whole value y replace with any other value

With the function isnumeric() you must make a function that manipluate this value like you want.

like
If MyValue is not numeric then
MyValue eliminateChar(MyValue)
end if

function EliminateChar(MyOld as String) as string
dim i as integer
for i = 1 to len(MyOld)
If isnumeric(Mid(MyOld,i,1)) then
EliminateChar = EliminateChar & Mid(MyOld,i,1)
end if
next i
end function

peterguhl@yahoo.de

 

[awelch]

By spit it out I mean Print it like it is.....

if readline(0) or readline(1) not numeric then
print #2, readline(0);&quot;&quot;;&quot;*&quot;
print #2, readline(1);&quot;&quot;;&quot;*&quot;
End if


I am just missing how to validate that readline(0) or readline(1) are not numeric.

Andrea

 

[poltergeist]

If not isnumeric(readline(0)) then
print ....


peterguhl@yahoo.de

 

[awelch]

I figured it out.

Thanks again for all your trouble...