filtering on a date field in Access using VB6

[Phil Frankel]

Hi all,

I am having trouble with VB6 (running in windows7) talking to an Access database, and was hoping someone
could help me locate the error.

the database contains a "DateIn" field of type "Date" (short Date)
I am reading the table into a recordset called "rsDiv"

I am trying to read only records for the financial year, between [1 Jul (name the year)] to [30 Jun] the next year.

my code is as follows.....

   FY = InputBox("Financial Year starting:-  Jul, ", "Enter Financial Year")
   st = "DateIn > '30/06/" & CStr(FY) & "' and DateIn < '1/07/" & CStr(FY + 1) & "'"
   rsDiv.Filter = st

I have tried using CDate(construct ed date), CStr(DateIn), even #constructed date#.
so far, the filter allows all records through.

any sugggestions, apart from "give it away" will be appreciated.

Many thanks
Phil

[MikeTheBike]

Hi

Just recently I have a similar problem with inconsistent filtering of a recordset.

However, I work almost exclusively in VBA (Excel and Access) and a little in .Net or stand alone exe programms, and always use ADO objects, so this may invalidate my post?

In code you need to format the date as 'America' ie. mm/dd/yy. This has always worked fine when creating record sets, but not filtering it would seem, hence the inconsistent results.

My solution was to use the 'universal' date format yyyy/mm/dd.

Therefore I would suggest you try this

   FY = InputBox("Financial Year starting:-  Jul, ", "Enter Financial Year") 
   st = "DateIn > #" & CStr(FY) & "/06/30# and DateIn < #" & CStr(FY + 1) & "/07/01#" 
   rsDiv.Filter = st

You will need to validate the 'year' entered by the user is a four digit year or work out which century it should be and add the relevant 19/20 to the two digit year!?

HTH


MTB

[Rabbit]

In Access VBA, asside from setting the filter, you also have to turn the filter on. I don't know if that is also the case for VB6.

[MikeTheBike]

Hi

I believe that ADO (and DAO?) recordsets don't have a FilterOn property (this is Form property); to remove the filtering you just use .Filter = ""

I also think that ADO objects are the same in VB6 and VBA, but I haven't used VB6 for about 10 years.


MTB

[Phil Frankel]

Thank you gentlemen, but your idea didn't work either. My system date is set to Australia, so I don't usually have to worry about American date.
I agree with Mike about the FilterOn property.

many thanks for your input though.
Phil

[Phil Frankel]

Hi all,

Thanks for the input, but I have created a "work-around" as follows.

  Do While Not rsDiv.EOF
     If Month(rsDiv!DateIn) > 6 And Year(rsDiv!DateIn) = FY or Month(rsDiv!DateIn) < 7 And Year(rsDiv!DateIn) = FY+1 Then
'   do stuff here

many thanks to all who replied.
cheers
Phil

[Killer42]

Glad to see you found a workaround.

I'd just like to point out, in SQL you'd generally use the Between clause for a date range like that.

Another handy tip to prevent issues with misinterpretati on of dates is to use an unambiguous format. For example "01/07/2012" could mean either of two dates depending on the Regional settings. On the other hand, "01-Jul-2012" can't easily be misinterpreted. And it's simple to produce using VB's Format function.

P.S. Perhaps you need to ReQuery or something after setting a filter? (I've never used filters much). Even though you've found a way around the problem, it's useful to try and get a solution on record for the benefit of anyone else who comes searching.

[Phil Frankel]

Good point, thank you.

[MikeTheBike]

Hi

I've been experimenting with this, and I also figured that BEWTEEN is the more normal criteria, but it doesn't work when filtering ('auguments are of the wrong type,are out of acceptable range, or are in conflict with one another' is the error message).
This all seemed to work OK without any formatting (my computer is set to UK date)

Sub TestFilter()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Dim Yr As String
    Dim dFromDate As Date
    Dim dToDate As Date
    Dim strFilter As String
    
    Set cn = New ADODB.Connection
    cn.Open "File Name=H:\My Data Sources\CES Timesheets DB DEV.udl"
    
    Set rs = New ADODB.Recordset
    rs.Open "SELECT WE_Date, WKNo FROM tblTimesheets WHERE WE_Date > #2012/03/31# GROUP BY WE_Date, WKNo ORDER BY WE_Date", cn, adOpenStatic, adLockReadOnly
    
    MsgBox rs("WE_Date") & " : " & rs.RecordCount
    
    Yr = "12"
    dFromDate = DateSerial(Yr, 6, 1)
    dToDate = DateSerial(Yr + 1, 5, 30)
    strFilter = "WE_Date >= #" & dFromDate & "# AND WE_Date <= #" & dToDate & "#"
    
    MsgBox strFilter
    
    rs.Filter = strFilter
    
    If rs.EOF Then
        MsgBox "No Date found (" & dFromDate & ")"
    Else
        MsgBox rs("WE_Date") & " : " & rs.RecordCount
    End If
    
    rs.Filter = ""
    MsgBox rs("WE_Date") & " : " & rs.RecordCount
    
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    
End Sub

I assume as the date is created in code (as apposed to being read from say a spreadsheet when I encountered previous inconsistancies ) that it just knows the date!

Agian this is written in Excel VBA, but there is nothing in this code that is Excel specific, and, I believe, should run in VB6 with change.

MTb

[Killer42]

I don't understand what you mean about a date being created in code, or when you say VBA "just knows the date". When you move a value directly from a date variable into a string (rather than using something like the Format function), it would most likely be formatted using your Windows date format.

If BETWEEN didn't work, I'd say it's likely that you didn't quite get the syntax right. But as long as your code works, the end result's the same so who's worrying?

By the way, your latest code is using a range of June to May, rather than (as stated) July to June. Just thought I'd mention that, in case it was accidental.

Oh, one more thing. Naturally we're happy to help out wherever possible. But keep in mind that our real experts in VBA, Access and Excel tend to hang out over in the Access / VBA forum.

P.S. Whoops! Didn't realise until after writing my response that the latest message was from a different person to the original poster.

[Phil Frankel]

Thank you both. The BETWEEN syntax works fine as part of a SQL statement, but in VB6 (using DAO), it doesn't seem to be accepted as part of a Filter.
Anyway, I have resolved it to my satisfaction now, and once again, thanks for your input and thought provoking ideas.
cheers
Phil