I have two text boxes where the user enters the dates to search between. The SQL statement finds all the records and then displays them on a seperate form. My problem is that it finds all the records, but it only displays the first and last record, not all the records in between the search parameters. Again I'll show you the code here, and perhaps you guys can show me where I'm screwing up. I have tried to debug this and am not getting any satisfactory results. Thanks in advance for your help
here's the code:
[vb]
Private Sub cmdFind_Click()

'if the user fails to enter a valid date range and clicks on the Find It button
'display a friendly reminder
If txtDate1.Text = "" Or txtDate2.Text = "" Then
    MsgBox "You Must Enter A Valid Date Range", vbOKCancel, "Date Fields Error"

    txtDate1.SetFocus


End If

Dim strFirstDate As String
Dim strSecondDate As String
strFirstDate = CDate(txtDate1.Text)
strSecondDate = CDate(txtDate2.Text)




'declare integer to hold array index
    Dim z As Integer
    z = 0

'since I dont know what date the user is going to pick I declare an array and assign
'a database field to the index of the array
'declare an array and populate
    Dim PayArray(0 To 13) As Date
        PayArray(0) = adoVAT.Recordset.Fields("SCHD_DT1").Value
        PayArray(1) = adoVAT.Recordset.Fields("SCHD_DT2").Value
        PayArray(2) = adoVAT.Recordset.Fields("SCHD_DT3").Value
        PayArray(3) = adoVAT.Recordset.Fields("SCHD_DT4").Value
        PayArray(4) = adoVAT.Recordset.Fields("SCHD_DT5").Value
        PayArray(5) = adoVAT.Recordset.Fields("SCHD_DT6").Value
        PayArray(6) = adoVAT.Recordset.Fields("SCHD_DT7").Value
        PayArray(7) = adoVAT.Recordset.Fields("SCHD_DT8").Value
        PayArray(8) = adoVAT.Recordset.Fields("SCHD_DT9").Value
        PayArray(9) = adoVAT.Recordset.Fields("SCHD_DT10").Value
        PayArray(10) = adoVAT.Recordset.Fields("SCHD_DT11").Value
        PayArray(11) = adoVAT.Recordset.Fields("SCHD_DT12").Value
        PayArray(12) = adoVAT.Recordset.Fields("SCHD_DT13").Value
        PayArray(13) = adoVAT.Recordset.Fields("SCHD_DT14").Value



'declare a string variable to hold SQL statement
    Dim pstrSQL As String
'declare a string variable to hold spaces for display purposes
    Dim myString
        myString = Space(10)

         pstrSQL = "SELECT * FROM Students WHERE " & PayArray(z) & " BETWEEN #" & strFirstDate & "# AND #" & strSecondDate & "#"

         adoVAT.RecordSource = pstrSQL

         If adoVAT.Recordset.EOF Then

            adoVAT.Recordset.MoveFirst

            'refresh the information in the flexgrid
            HFlexGrid1.Refresh
        End If


    strNames = adoVAT.Recordset.Fields("FName").Value & " " & adoVAT.Recordset.Fields("LName").Value & myString & adoVAT.Recordset.Fields("SSNumber").Value & myString & PayArray(z) & myString & "$" & adoVAT.Recordset.Fields("MNTHLY_PMT") & myString & vbNewLine

     Dim j As Integer
        For j = 0 To adoVAT.Recordset.RecordCount - 1

             frmReport.lblReport.Caption = strNames & adoVAT.Recordset.Fields("FName").Value & " " & adoVAT.Recordset.Fields("LName").Value & myString & adoVAT.Recordset.Fields("SSNumber").Value & myString & PayArray(z) & myString & "$" & adoVAT.Recordset.Fields("MNTHLY_PMT") & myString & vbNewLine

              'move to the next record in the recordset
              adoVAT.Recordset.MoveNext

        Next


        'send the information fromt the SQL to a report form and show it

        frmReport.Show



End Sub
[/vb]

I think.....therefore..I code

__________________
I think.....therefore..I code

I need a bit more time to look at the code. Not sure if I can make the time. But, quickly I see some problems right off the bat.

You are using this code to validate you date parameters.

'if the user fails to enter a valid date range and clicks on the Find It button
'display a friendly reminder
If txtDate1.Text = "" Or txtDate2.Text = "" Then
    MsgBox "You Must Enter A Valid Date Range", vbOKCancel, "Date Fields Error"

    txtDate1.SetFocus

End If

This will not ensure that you have a valid date or date range. You can easily modify your validation routine to ensure a valid date range is entered.

' Check the dates individually to identify the problem more explicitly

' Validate date range
If IsDate(txtDate1.Text) = False Then
     ' The Date is not valid

End If
If IsDate(txtDate2.Text) = False Then
     ' The Date is not valid

End If

' The user did enter a date
' Now ensure that date2 is greater than date1
Dim dtmStartDate As Date
Dim dtmEndDate As Date

dtmStartDate = CDate(txtDate1.Text)
dtmEndDate = CDate(txtDate2.Text)
If dtmStartDate >= dtmEndDate Then
     ' The date range is invalid
     ' Do some error handling

Else
     ' The dates are valid

End If

I would even write this to a function, pass the values by reference the and convert the dates from the text box all in one step.

Just as an example of a point of failure in your code. I could have a space (just hit the space key) entered in the text box. Your code will consider it to be a valid date and then functions will fail. The same if I entered an ending date that preceded a start date.

I know this will not resolve your issue. But, it will make your code more solid and more efficient.



Larry Asher

hi there..

if you can avoid using the recordcount property.. it not always work the way it should..

use instead a loop going trough all the rows something like

do while not adoVAT.Recordset.eof
    .
    .
    .
    adoVAT.Recordset.movenext
loop

also.. in the for section you are populating always the same label!
that's why you always see the same..

i think you have missed this
frmReport.lblReport.Caption = frmReport.lblReport.Caption & ...

HTH...

Gonzalo Bianchi

:DThx to both of you for your keen insight. I'll try your suggestions and get back to you here.
Larson

I think.....therefore..I code