Problems with sorting dates

[David Garcia Sanchez]

Hi, I have a problem

I work with a subform and that I want is to sort two dates
stored in combobox, but when I do this somethin strange
occurs, I can only sort "years", if I choice for example
from 1/1/1997 to 7/1/1997 in the subform only appear the
1/1/1997 record, by the way if I choice from 1/1/1997 to
5/1/1998 in the subform appears all the records from 1997
and only the first month of 1998, and so on.

my code:

(the values of the dates are due two comboboxes, and other
values of statements are due checkboxs)

Option Compare Text
Option Explicit

Private Sub cmdFiltro_Click()

Dim strSQL As String
Dim blWhereIsUsed As Boolean
blWhereIsUsed = False

strSQL = " SELECT * FROM tblTarifas"

If Not Nz(Me.cboFecha, "") = "" Then
If Me.cboFecha > Me.cboFecha2 Then
MsgBox ("Favor de poner correctamente las fechas")
Else
strSQL = strSQL & " WHERE tblTarifas.fecha BETWEEN #"
& Me.cboFecha & "# AND #" & Me.cboFecha2 & "# "
blWhereIsUsed = True
End If
End If

If Me.Check24.Value = True Then
If Not blWhereIsUsed Then
strSQL = strSQL & " WHERE tblTarifas.Zona = '" &
Me.Label25.Caption & "'"
blWhereIsUsed = True
Else
strSQL = strSQL & " AND tblTarifas.Zona = '" &
Me.Label25.Caption & "'"
End If
End If

If Me.Check27.Value = True Then
If Not blWhereIsUsed Then
strSQL = strSQL & " WHERE tblTarifas.Zona = '" &
Me.Label28.Caption & "'"
blWhereIsUsed = True
Else
strSQL = strSQL & " OR tblTarifas.Zona = '" &
Me.Label28.Caption & "' AND tblTarifas.fecha BETWEEN #" &
Me.cboFecha & "# AND #" & Me.cboFecha2 & "#"
End If
End If

If Me.Check29.Value = True Then
If Not blWhereIsUsed Then
strSQL = strSQL & " WHERE tblTarifas.Zona = '" &
Me.Label30.Caption & "'"
blWhereIsUsed = True
Else
strSQL = strSQL & " OR tblTarifas.Zona = '" &
Me.Label30.Caption & "' AND tblTarifas.fecha BETWEEN #" &
Me.cboFecha & "# AND #" & Me.cboFecha2 & "#"
End If
End If

If Me.Check33.Value = True Then
If Not blWhereIsUsed Then
strSQL = strSQL & " WHERE tblTarifas.Zona = '" &
Me.Label34.Caption & "'"
blWhereIsUsed = True
Else
strSQL = strSQL & " OR tblTarifas.Zona = '" &
Me.Label34.Caption & "' AND tblTarifas.fecha BETWEEN #" &
Me.cboFecha & "# AND #" & Me.cboFecha2 & "#"
End If
End If

If Me.Check35.Value = True Then
If Not blWhereIsUsed Then
strSQL = strSQL & " WHERE tblTarifas.Zona = '" &
Me.Label36.Caption & "'"
blWhereIsUsed = True
Else
strSQL = strSQL & " OR tblTarifas.Zona = '" &
Me.Label36.Caption & "' AND tblTarifas.fecha BETWEEN #" &
Me.cboFecha & "# AND #" & Me.cboFecha2 & "#"
End If
End If

If Me.Check37.Value = True Then
If Not blWhereIsUsed Then
strSQL = strSQL & " WHERE tblTarifas.Zona = '" &
Me.Label38.Caption & "'"
blWhereIsUsed = True
Else
strSQL = strSQL & " OR tblTarifas.Zona = '" &
Me.Label38.Caption & "' AND tblTarifas.fecha BETWEEN #" &
Me.cboFecha & "# AND #" & Me.cboFecha2 & "# "
End If
End If

If Me.Check39.Value = True Then
If Not blWhereIsUsed Then
strSQL = strSQL & " WHERE tblTarifas.Zona = '" &
Me.Label40.Caption & "'"
blWhereIsUsed = True
Else
strSQL = strSQL & " OR tblTarifas.Zona = '" &
Me.Label40.Caption & "' AND tblTarifas.fecha BETWEEN #" &
Me.cboFecha & "# AND #" & Me.cboFecha2 & "#"
End If
End If

Debug.Print strSQL

Me.sfmTarifas.Form.RecordSource = strSQL
Me.sfmTarifas.Form.Requery

End Sub

 

[Jeff Boyce]

David

When I looked over your SQL statements, I didn't see any ORDER BY clause --
if you want to "sort", you need to tell Access what to use to sort by.