Date filtering with spanish date format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a filter in a form that works fine when dates have the day number
greater than 12 (e.g. 18/04/2007) but it doesn't work when it's less than 12
(e.g. 03/04/2007)
The code:
-------------------------------------------------------------------------------
Dim strFilter As String
Const conDateFormat = "\#dd\/mm\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Primero se guardan los datos.

strFilter = ""

If (Me.txtFiltrarMonto <> "") Then
strFilter = "(([gastoAnio1]+[invAnio1])/[tipocambio2007]) > " _
& Me.txtFiltrarMonto
End If

If (Me.txtBuscarNombre <> "") Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "denominacion Like '*" &
Me.txtBuscarNombre & "*'"
End If

If (Me.cboFiltrarFechaComiteDesde = "") Then
If (Me.cboFiltrarFechaComiteHasta <> "") Then 'Fecha hasta,
pero desde.
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "([fechaComite] <= " & _
Format(Me.cboFiltrarFechaComiteHasta, conDateFormat) & ")"
End If
Else
If (Me.cboFiltrarFechaComiteHasta = "") Then 'Fecha desde,
pero no hasta.
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "([fechaComite] >= " & _
Format(Me.cboFiltrarFechaComiteDesde, conDateFormat) & ")"
Else 'Ambas fechas.
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "([fechaComite] Between " & _
Format(Me.cboFiltrarFechaComiteDesde, conDateFormat) & "
And " & _
Format(Me.cboFiltrarFechaComiteHasta, conDateFormat) & ")"
End If
End If

If (Me.chkSinADASistemas = -1) Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "isnull([ADASistemas])"
End If

If (Me.chkSinADANegocio = -1) Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "isnull([ADANegocio])"
End If

If (Me.chkInterregion = -1) Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "not isnull([idInterregion])"
End If

If (Me.cboFiltrarCuenta <> "") Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
If (Me.cboFiltrarCuenta <> 8) Then
strFilter = strFilter & "Cuenta =" & Me.cboFiltrarCuenta & ""
End If
End If

If (Me.txtIntercuenta <> "") Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "idIntercuenta1 Like '*" &
Me.txtIntercuenta & "*'" & " OR " & "idIntercuenta2 Like '*" &
Me.txtIntercuenta & "*'"
End If



If Len(strFilter) > 0 Then
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.FilterOn = False
End If

Me.Requery
 
Juan, literal dates in a SQL statement need to be in American format, not
your own format. Use:
Const conDateFormat = "\#mm\/dd\/yyyy\#"

More info:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Juan said:
I have a filter in a form that works fine when dates have the day number
greater than 12 (e.g. 18/04/2007) but it doesn't work when it's less than
12
(e.g. 03/04/2007)
The code:
-------------------------------------------------------------------------------
Dim strFilter As String
Const conDateFormat = "\#dd\/mm\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Primero se guardan los datos.

strFilter = ""

If (Me.txtFiltrarMonto <> "") Then
strFilter = "(([gastoAnio1]+[invAnio1])/[tipocambio2007]) > " _
& Me.txtFiltrarMonto
End If

If (Me.txtBuscarNombre <> "") Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "denominacion Like '*" &
Me.txtBuscarNombre & "*'"
End If

If (Me.cboFiltrarFechaComiteDesde = "") Then
If (Me.cboFiltrarFechaComiteHasta <> "") Then 'Fecha hasta,
pero desde.
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "([fechaComite] <= " & _
Format(Me.cboFiltrarFechaComiteHasta, conDateFormat) &
")"
End If
Else
If (Me.cboFiltrarFechaComiteHasta = "") Then 'Fecha desde,
pero no hasta.
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "([fechaComite] >= " & _
Format(Me.cboFiltrarFechaComiteDesde, conDateFormat) &
")"
Else 'Ambas fechas.
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "([fechaComite] Between " & _
Format(Me.cboFiltrarFechaComiteDesde, conDateFormat) &
"
And " & _
Format(Me.cboFiltrarFechaComiteHasta, conDateFormat) &
")"
End If
End If

If (Me.chkSinADASistemas = -1) Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "isnull([ADASistemas])"
End If

If (Me.chkSinADANegocio = -1) Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "isnull([ADANegocio])"
End If

If (Me.chkInterregion = -1) Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "not isnull([idInterregion])"
End If

If (Me.cboFiltrarCuenta <> "") Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
If (Me.cboFiltrarCuenta <> 8) Then
strFilter = strFilter & "Cuenta =" & Me.cboFiltrarCuenta &
""
End If
End If

If (Me.txtIntercuenta <> "") Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "idIntercuenta1 Like '*" &
Me.txtIntercuenta & "*'" & " OR " & "idIntercuenta2 Like '*" &
Me.txtIntercuenta & "*'"
End If



If Len(strFilter) > 0 Then
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.FilterOn = False
End If

Me.Requery

---------------------------------------------------------------------------------------------

Can someone give me a clue as to how it can be solved?

Thanks in advance
Click to expand...
 
Thanks a lot for your quick response!!!
Actually, I ment to use "\#mm\/dd\/yyyy\#" not "\#dd\/mm\/yyyy\#"..., how
silly.

Thanks again.

Allen Browne said:
Juan, literal dates in a SQL statement need to be in American format, not
your own format. Use:
Const conDateFormat = "\#mm\/dd\/yyyy\#"

More info:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Juan said:
I have a filter in a form that works fine when dates have the day number
greater than 12 (e.g. 18/04/2007) but it doesn't work when it's less than
12
(e.g. 03/04/2007)
The code:
-------------------------------------------------------------------------------
Dim strFilter As String
Const conDateFormat = "\#dd\/mm\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Primero se guardan los datos.

strFilter = ""

If (Me.txtFiltrarMonto <> "") Then
strFilter = "(([gastoAnio1]+[invAnio1])/[tipocambio2007]) > " _
& Me.txtFiltrarMonto
End If

If (Me.txtBuscarNombre <> "") Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "denominacion Like '*" &
Me.txtBuscarNombre & "*'"
End If

If (Me.cboFiltrarFechaComiteDesde = "") Then
If (Me.cboFiltrarFechaComiteHasta <> "") Then 'Fecha hasta,
pero desde.
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "([fechaComite] <= " & _
Format(Me.cboFiltrarFechaComiteHasta, conDateFormat) &
")"
End If
Else
If (Me.cboFiltrarFechaComiteHasta = "") Then 'Fecha desde,
pero no hasta.
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "([fechaComite] >= " & _
Format(Me.cboFiltrarFechaComiteDesde, conDateFormat) &
")"
Else 'Ambas fechas.
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "([fechaComite] Between " & _
Format(Me.cboFiltrarFechaComiteDesde, conDateFormat) &
"
And " & _
Format(Me.cboFiltrarFechaComiteHasta, conDateFormat) &
")"
End If
End If

If (Me.chkSinADASistemas = -1) Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "isnull([ADASistemas])"
End If

If (Me.chkSinADANegocio = -1) Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "isnull([ADANegocio])"
End If

If (Me.chkInterregion = -1) Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "not isnull([idInterregion])"
End If

If (Me.cboFiltrarCuenta <> "") Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
If (Me.cboFiltrarCuenta <> 8) Then
strFilter = strFilter & "Cuenta =" & Me.cboFiltrarCuenta &
""
End If
End If

If (Me.txtIntercuenta <> "") Then
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "idIntercuenta1 Like '*" &
Me.txtIntercuenta & "*'" & " OR " & "idIntercuenta2 Like '*" &
Me.txtIntercuenta & "*'"
End If



If Len(strFilter) > 0 Then
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.FilterOn = False
End If

Me.Requery

---------------------------------------------------------------------------------------------

Can someone give me a clue as to how it can be solved?

Thanks in advance
Click to expand...
Click to expand...
 
Back
Top