We help IT Professionals succeed at work.

how to put values in array with semicolon?

i am trying to create a files
but i am little bit confuse that how to put values in an array with semicolon?
and if any field is empty then write 0 value for that field.

so far my little approach is just as follow.
Dim csvflname As String
Dim I
If Rs.State = 1 Then Rs.Close
Rs.Open "Select * from BD where bdate between CDATE('" & DTPicker1.Value & "') and CDATE('" & DTPicker2.Value & "')", con, adOpenKeyset, adLockOptimistic
If Rs.RecordCount > 0 Then
    With Rs
        For I = 0 To Rs.RecordCount
         '''here i wanted to create an array and put rs
         '''value in it with semicolon and if any fields 
         '''is empty then put default value 0
 
            csvflname = Rs.Fields(0) & ".csv"
        .MoveNext
        Next
    End With

Open in new window

Comment
Watch Question

I'm really confused as to what you want to accomplish. Are you trying to build a .CSV (comma separated value) file?

If so you'd want to loop through the recordset and append each value to the end of the previous and except for the last value, append a comma to the end of them. Then when that whole record is through output that to a file and go to the next record.

You'd need to check to see if the value is numeric, IsNumeric(), or IsDate(), IsNull or else must be a string.
Strings need to be surrounded by "" in a CSV, numbers do not.

If you want to create an array you should create one where the number of elements in one dimension is at least the count of fields long (if 1 based) or count of field long - 1 if 0 based.

The other dimension would be rs.recordcount.  Then you just test IsNull() I believe and if so put a 0.
It's been a while since I've used vb6, but I think that is right.
Altaf PatniSoftware Developer

Author

Commented:
<<<Are you trying to build a .CSV (comma separated value) file?>>
Yes but semicolon separated file.

following code is adding all fields in array (csvdata)
now i am ready to build a file.

csvdata = Rs.Fields(J) & ";" & Rs.Fields(1) & ";" & Rs.Fields(2) & ";" & Rs.Fields(3) & ";" & Rs.Fields(4) & ";" & Rs.Fields(5) & ";" & Rs.Fields(6) & ";" & Rs.Fields(7) & ";" & Rs.Fields(8) & ";" & Rs.Fields(9) and till Rs.Fields(45)

before i build a file i want to check any field is empty or not, if empty then how to put 0 for that filed in array..?
What do you mean by empty? Null?  If so, a function like this

Assuming VB6

function FixNulls(CurrentValue as variant) as variant
       if IsNull(CurrentValue) then
            FixNulls=0
       else
            FixNulls=CurrentValue
       end if
end function

Then for each field while you are building them, call the function....

example:


FixNulls(Rs.Fields(J)) & ";" & FixNulls(RsFields(1)    etc

Altaf PatniSoftware Developer

Author

Commented:

Ok  I didn't test your code but so far i create this ( Please check and suggest me which one is best and fast procedure )
 
Rs.Open "Select * from BD where bdate between CDATE('" & DTPicker1.Value & "') and CDATE('" & DTPicker2.Value & "') order by BD.BNo", con, adOpenKeyset, adLockOptimistic
If Rs.RecordCount > 0 Then

    With Rs
        For I = 0 To Rs.RecordCount
                    If I = Rs.RecordCount Then
                        MsgBox "DONE"
                        Exit Sub
                    End If
            csvflname = Rs.Fields(0) & ".csv"
                For J = 0 To 56
                    If Rs.Fields(J) & "" = "" Then
                        Rs.Fields(J) = 0
                    End If
                    
                Next
                
    csvdata = Rs.Fields(0) & ";" & Rs.Fields(1) & ";" & Rs.Fields(2) & ";" & Rs.Fields(3) _
    & ";" & Rs.Fields(4) & ";" & Rs.Fields(5) & ";" & Rs.Fields(6) & ";" & Rs.Fields(7) _
    & ";" & Rs.Fields(8) & ";" & Rs.Fields(9) & ";" & Rs.Fields(10) & ";" & Rs.Fields(11) _
    & ";" & Rs.Fields(12) & ";" & Rs.Fields(13) & ";" & Rs.Fields(14) & ";" & Rs.Fields(15) _
    & ";" & Rs.Fields(16) & ";" & Rs.Fields(17) & ";" & Rs.Fields(18) & ";" & Rs.Fields(19) _
    & ";" & Rs.Fields(20) & ";" & Rs.Fields(21) & ";" & Rs.Fields(22) & ";" & Rs.Fields(23) _
    & ";" & Rs.Fields(24) & ";" & Rs.Fields(25) & ";" & Rs.Fields(26) & ";" & Rs.Fields(27) _
    & ";" & Rs.Fields(28) & ";" & Rs.Fields(29) & ";" & Rs.Fields(30) & ";" & Rs.Fields(31) _
    & ";" & Rs.Fields(32) & ";" & Rs.Fields(33) & ";" & Rs.Fields(34) & ";" & Rs.Fields(35) _
    & ";" & Rs.Fields(36) & ";" & Rs.Fields(37) & ";" & Rs.Fields(38) & ";" & Rs.Fields(39) _
    & ";" & Rs.Fields(40) & ";" & Rs.Fields(41) & ";" & Rs.Fields(42) & ";" & Rs.Fields(43) _
    & ";" & Rs.Fields(44) & ";" & Rs.Fields(45) & ";" & Rs.Fields(46) & ";" & Rs.Fields(47) _
    & ";" & Rs.Fields(48) & ";" & Rs.Fields(49) & ";" & Rs.Fields(50) & ";" & Rs.Fields(51) _
    & ";" & Rs.Fields(52) & ";" & Rs.Fields(53) & ";" & Rs.Fields(54) & ";" & Rs.Fields(55) _
    & ";" & Rs.Fields(56)

''''create csv file
iFileNo = FreeFile
Open csvflname For Output As #iFileNo
    Print #iFileNo, csvdata
Close #iFileNo
        
'''''''''''''''''''''''''''''''
        .MoveNext
        Next
    End With
End If

Open in new window

OK, below

               For J = 0 To 56
                    If Rs.Fields(J) & "" = "" Then
                        Rs.Fields(J) = 0
                    End If

You are actually modifying the recordset. Is that what you intended?  As long as you don't update it I guess it is fine.

If you are trying to avoid the function call-which I think would be minimal time....
you could do something like (see code snippet)


csvdata = ""
for i=0 to 56
   if i<>0 then
      'prepend ; for all but the first element
      csvdata=csvdata & ";"
   end if

   if IsNull(Rs.Fields(i) then
      csvdata=csvdata & "0"
   else
      csvdata=csvdata & RsFields(i)
   end if
next i

Open in new window

Altaf PatniSoftware Developer

Author

Commented:
Excellent :-)

i don't like to update actual record.


for accurate answer,  you deserve more 100 points. :-)

Thanks for helping me.
Altaf PatniSoftware Developer

Author

Commented:
Thanks
You are very welcome!