Another syntax error in SQL

[aikidokid]

I am getting the above error with the following:

strSQL = "SELECT * FROM tbl_Finances"
strSQL = strSQL & " WHERE Finance_Date > #" & dtDateOne & "#"
strSQL = strSQL & " AND < #" & dtDateTwo & "#"
strSQL = strSQL & " ORDER BY Finance_Date"

I cannot see anything wrong with the output below!

debug.Print strsql
SELECT * FROM tbl_Finances WHERE Finance_Date > #01/06/2007# AND < #30/06/2007# ORDER BY Finance_Date
SELECT * FROM tbl_Finances WHERE Finance_Date > #01/06/2007# AND < #30/06/2007#
SELECT * FROM tbl_Finances WHERE Finance_Date > #01/06/2007#
SELECT * FROM tbl_Finances

[GaryMazzone]

If you are not using between then you need to put the field name in twice like this:

strSQL = "SELECT * FROM tbl_Finances"
strSQL = strSQL & " WHERE Finance_Date > #" & dtDateOne & "#"
strSQL = strSQL & " AND Finance_Date < #" & dtDateTwo & "#"
strSQL = strSQL & " ORDER BY Finance_Date"

[aikidokid]

Thanks Gary.

How would the SQL look if I was using BETWEEN and which is best to use?

[Hack]

WHERE Finance_Date BETWEEN #" & dtDateOne & "# AND " &  dtDateTwo & "#"

[GaryMazzone]

As Hack showed. I perfer the Between but that is just my choice.

[Hack]

As Hack showed. I perfer the Between but that is just my choice.

And I agree. I prefer using the Between as well.

Both ways work, but I just think it is cleaner using BETWEEN.

However, as Gary points out, it is a individual, personal, preference, not Gospel.

[aikidokid]

Thanks both of you

Don't know if this should be in a new thread or not .....

I have the following now and sometimes it returns the correct records, sometimes nothing, although it should be.
Can you see why?

    If blnSelectedDates Then
        'reset boolean
        blnSelectedDates = False
        
        strSQL = "SELECT * FROM tbl_Finances"
        strSQL = strSQL & " WHERE Finance_Date BETWEEN #" & dtDateOne & "# AND " & dtDateTwo & "#"
    Else
        Select Case NumOfRecordsDisplayed
            Case 0
                'display last 30 records
                'yet to do
                strSQL = "SELECT * FROM tbl_Finances"
                
                strSQL = strSQL & " ORDER BY Finance_Date"
            Case 1
                'display last 60 records
                'yet to do
                strSQL = "SELECT * FROM tbl_Finances"
                
                strSQL = strSQL & " ORDER BY Finance_Date"
        End Select
        
    End If
End If

rs.Open strSQL, cn, adOpenForwardOnly, adLockPessimistic, adCmdText

With FLX
    'remove all rows except any headers, and the first data row (cannot be removed)
    .Rows = .FixedRows
    lngRow = 1  'one because fixed row = 0
    
    'Turn off screen updates (much faster to fill the data)
    blnOldRedraw = .Redraw
    .Redraw = False
 
      'Check if there is any data
    If rs.EOF Then
        'if there is no data, only allow the required blank row, and Hide it (height=0)
      .AddItem ""
      '.RemoveItem .FixedRows
      .RowHeight(.FixedRows) = 0
    Else
        Do While Not rs.EOF
            'Add the row (empty)
            .AddItem ""
                      
            'set values one cell at a time
            .TextMatrix(lngRow, 0) = lngRow
            
            .TextMatrix(lngRow, 1) = rs!Finance_Date.Value
            
            If rs!Finance_Cheque.Value = 0 Then
                .TextMatrix(lngRow, 2) = ""
            Else
                .TextMatrix(lngRow, 2) = rs!Finance_Cheque.Value
            End If
            
            .TextMatrix(lngRow, 3) = rs!Finance_Transaction.Value
            
            If rs!Finance_Expenditure = 0 Then
                .TextMatrix(lngRow, 4) = ""
            Else
                .TextMatrix(lngRow, 4) = Format(rs!Finance_Expenditure.Value, "#####0.00")
            End If
            
            dblExpenditure = dblExpenditure + Val(rs!Finance_Expenditure.Value)
            
            If rs!Finance_Income.Value = 0 Then
                .TextMatrix(lngRow, 5) = ""
            Else
                .TextMatrix(lngRow, 5) = Format(rs!Finance_Income.Value, "#####0.00")
            End If
            
            dblIncome = dblIncome + Val(rs!Finance_Income.Value)
            dblTotal = (dblIncome - dblExpenditure)
            
            If dblTotal < 0 Then
                .TextMatrix(lngRow, 6) = Format(dblTotal, "#####0.00")
                .Row = lngRow
                .Col = 6
                .CellForeColor = vbRed
            Else
                .TextMatrix(lngRow, 6) = Format(dblTotal, "#####0.00")
            End If
            
            .TextMatrix(lngRow, 7) = rs!Finance_ID.Value
         
            'Increment our row counter
            lngRow = lngRow + 1
            
            'Move to the next row of data
            rs.MoveNext
        Loop
        
          'Remove the blank row we Left at the top
          '.RemoveItem 1
     
        End If
     
        'Re-enable screen updates (if was previously enabled)
        .Redraw = blnOldRedraw
     
        'Force a redraw of the grid
        .Refresh

End With

[CompositeID]

You are not handling the Case Else portion of the method. You are checking if NumOfRecordsDisplayed is equal to 0 or 1; however, how do you intend to handle it if it is any other number? Essentially, it NumOfRecordsDisplayed equals 2 you are passing an empty query, which is the reason you aren't getting any records.

[aikidokid]

Thanks for the reply CompositeID

I haven't get to the ESLE part of the code yet, I am still working on the part before.

As for NumOfRecordsDisplayed, this is the value of an option button in the Options form.