Filtering dates displays wrong results (format issue?)

[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!

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

 

[Maver1ck666]

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

 

[Maver1ck666]

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

 

[Andy Hull]

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

 

[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"