Filtering dates displays wrong results (format issue?)

[Guest]

I have a form which the recordset is a query based on 3 tables displaying
different types of fields. The results are then displayed in the detail
section of the form which is set to continuous records.

In the Form Header, are a number of combo boxes all set as filters with the
following properties:

Format: Short Date (UK date standard dd/mm/yyyy)
Row Source Type: Table/Query
Row Source: Sql statement finding unique records corresponding to the
relevant field in the form deatil from the original table (used in the query
above)
Tag: Is set to the corresponding field name
Event (after update) is set to the following (this one is used for a date
field):

Private Sub Filter2_AfterUpdate()

'sets the filter2
DoCmd.ApplyFilter , "" & Me.Filter2.Tag & " = #" & Me.Filter2 & "#"

End Sub

Now this is where its gos a bit screwy. The results I get from my searches
vary depending on the date I enter. For example,

If I enter 07/09/2007 (or any other dates where the day is before the 12th),
then the database will either return:

09/07/2007. This hasn't changed to US format, it just searches the wrong
date, obviously mixing up the dd and mm somehow.

or

The database wont find any records and will return a blank screen.

If I enter a date after the 12th so for example the 13/09/2007, the database
finds the correct records and displays them correctly.

This will happen to any month! I have checked the data itself and it is fine
so im at a loss here.

If you have any suggestions, that would be most fantastic!

Cheers,
Maver1ck666

 

[Pieter Wijnen]

You have to apply the filter either using US or as Military Date
I always use the military format to avoid any confusion
ie
Format(DateField,"\#yyyy-mm-dd\#")

Pieter

 

[Guest]

Thanks for that Pieter. One questions though, where is the best place to put
the code please?

 

[Pieter Wijnen]

instead of #" & Me.Filter2 & "#"
use Format(Me.Filter2.Value ,"\#yyyy-mm-dd\#")

Pieter

 

[Guest]

Ok, Im obviously doing something complete stupid here because after my code
reads:

DoCmd.ApplyFilter , "" & Me.Filter2.Tag & " = Format(Me.Filter2.Value
,"\#yyyy-mm-dd\#")

Im getting a compile error: expected: expression

 

[Guest]

Hi

Often it is more difficult to describe changes to a line of code than to
just post the whole correct line! I believe the following is what Pieter is
suggesting...

DoCmd.ApplyFilter, "" & Me.Filter2.Tag & " = " & Format(Me.Filter2.Value,
"\#yyyy-mm-dd\#")

 

[Van T. Dinh]

I think you want:

DoCmd.ApplyFilter , Me.Filter2.Tag & " = " & _
Format(Me.Filter2.Value,"\#yyyy-mm-dd\#")

 

[Pieter Wijnen]

Nah,
Me.Filter = Me.Filter2.Tag & " = " &
Format(Me.Filter2.Value,"\#yyyy-mm-dd\#")
Me.FilterOn = True

<g> Pieter

 

[Van T. Dinh]

Same difference, isn't it?

--
Cheers
Van T. Dinh
MVP (Access)




"Pieter Wijnen"

 

[Pieter Wijnen]

yes & no
DoCmd applies to the form or report (hWnd) which happens to have the focus,
which can be another form or report (or none).
Also it has sideeffects (tends to fire Form_Activate)
Therefore I always use the "direct" approach whenever I have a choice

Pieter

 

[Wolfgang Kais]

Hallo Maver1ck666.

I have a form which the recordset is a query based on 3 tables
displaying different types of fields. The results are then displayed
in the detail section of the form which is set to continuous records.

In the Form Header, are a number of combo boxes all set as filters
with the following properties:

Format: Short Date (UK date standard dd/mm/yyyy)
Row Source Type: Table/Query
Row Source: Sql statement finding unique records corresponding to the
relevant field in the form deatil from the original table (used in
the query above)
Tag: Is set to the corresponding field name
Event (after update) is set to the following (this one is used for
a date field):

Private Sub Filter2_AfterUpdate()

'sets the filter2
DoCmd.ApplyFilter , "" & Me.Filter2.Tag & " = #" & Me.Filter2 & "#"

End Sub

Now this is where its gos a bit screwy. The results I get from my
searches vary depending on the date I enter. For example,

If I enter 07/09/2007 (or any other dates where the day is before
the 12th), then the database will either return:

09/07/2007. This hasn't changed to US format, it just searches
the wrong date, obviously mixing up the dd and mm somehow.

or

The database wont find any records and will return a blank screen.

If I enter a date after the 12th so for example the 13/09/2007, the
database finds the correct records and displays them correctly.

This will happen to any month! I have checked the data itself and it
is fine so im at a loss here.

If you have any suggestions, that would be most fantastic!

In the Filter property, use the same date format as in a SQL query,
the US format (#mm/dd/yyyy#).
As you can see when entering a date for criteria in query design view
and comparing this to the SQL view, Access has a built in capability
to build criteria correctly from data entered in a format matching
the regional settings from the OS.
You can use this capability for your own purpose through the
BuildCriteria method:

With Me.Filter2
DoCmd.ApplyFilter , BuildCriteria(.Tag, dbDate, CStr(.Value))
End With

 

[Van T. Dinh]

Since the O.P. wrote in the original post that the filter was applied with
the ApplyFilter, I think we can safely assume that the active Data Object is
the appropriate one so I don't think we have to worry that ApplyFilter may
happen on thw wrong Object ...

I am not aware of the Activate Event firing ... Will have to do some tests
to see what happens.
(I confess that I can't recall using DoCmd.ApplyFilter in my databases.)

--
Thanks & cheers
Van T. Dinh
MVP (Access)




"Pieter Wijnen"

 

[Pieter Wijnen]

I do. <g>
Back when there wasn't an alternative (Access 1 & 2)

Pieter

 

[Van T. Dinh]

Don't say "I do" too often.

I made a mistake once and that's enough to last for a life time ... <BG>

--
Cheers
Van T. Dinh
MVP (Access)




"Pieter Wijnen"