date filter not working

chucher

I'm trying to put a filter into some forms or filters but the filter
don´t work properly.

I'm using the format dd-mm-yyyy. The forms and the tables always shows
how dates in that format. But when i put filters i don´t get the
expected res result in fact some times i don´t get any result.

I al ready try this filters

date = #08/05/2006#

date <= #08/05/2006#

date between #07/05/2006# and #09/05/2006#

i al ready try use # format(08/05/2006, "dd-mm-yyyy") # in each filter
and still don't work.
I hope you can help me
thanx

May 23 '06 #1

Rick Brandt

chucher wrote:

I'm trying to put a filter into some forms or filters but the filter
don´t work properly.

I'm using the format dd-mm-yyyy. The forms and the tables always shows
how dates in that format. But when i put filters i don´t get the
expected res result in fact some times i don´t get any result.

I al ready try this filters

date = #08/05/2006#

date <= #08/05/2006#

date between #07/05/2006# and #09/05/2006#

i al ready try use # format(08/05/2006, "dd-mm-yyyy") # in each
filter and still don't work.
I hope you can help me
thanx

Date literals in Access queries/filters must use US format, ISO format, or an
unambiguous format where the month uses alpha characters. Your regional
settings and internal format settings do not matter.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

May 23 '06 #2

Rick Wannall

There's not nearly enough information here to come to a decision on what's
wrong. What data type field is used to store the dates. Are you storing
dates in a table? How are you trying to filter? More info please.

May 23 '06 #3

chucher

I change it to US format and it seems to work fine thanx

this is the complete code I´m using to open another form and and
filter the information

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "invoice"
stLinkCriteria = ""

If (Not IsNull(txtDate) And IsNull(txtEndDate)) Then
'format(txtDate, "mm-dd-yyyy")
If (stLinkCriteria <> "") Then
stLinkCriteria = stLinkCriteria & " And "
End If
stLinkCriteria = stLinkCriteria & "date = #" & Format(txtDate,
"mm-dd-yyyy") & "#"
End If
If (IsNull(txtDate) And Not IsNull(txtEndDate)) Then
If (stLinkCriteria <> "") Then
stLinkCriteria = stLinkCriteria & " And "
End If
stLinkCriteria = stLinkCriteria & "date <= #" &
Format(txtEndDate, "mm-dd-yyyy") & "#"
End If
If (Not IsNull(txtDate) And Not IsNull(txtEndDate)) Then
If (stLinkCriteria <> "") Then
stLinkCriteria = stLinkCriteria & " And "
End If
stLinkCriteria = stLinkCriteria & "date BETWEEN #" &
Format(txtDate, "mm-dd-yyyy") & "# And #" & Format(txtEndDate,
"mm-dd-yyyy") & "#"
End If
If (Not IsNull(cmbClient)) Then
If (stLinkCriteria <> "") Then
stLinkCriteria = stLinkCriteria & " And "
End If
stLinkCriteria = stLinkCriteria & "idClient = " & cmbClient
End If

Debug.Print stLinkCriteria

DoCmd.OpenForm stDocName, , , stLinkCriteria

May 23 '06 #4