By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,696 Members | 1,462 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,696 IT Pros & Developers. It's quick & easy.

filtering on a date field in Access using VB6

P: 9
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.....

Expand|Select|Wrap|Line Numbers
  1.    FY = InputBox("Financial Year starting:-  Jul, ", "Enter Financial Year")
  2.    st = "DateIn > '30/06/" & CStr(FY) & "' and DateIn < '1/07/" & CStr(FY + 1) & "'"
  3.    rsDiv.Filter = st
  4.  
I have tried using CDate(constructed 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
Aug 13 '12 #1
Share this Question
Share on Google+
10 Replies


Expert 100+
P: 634
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
Expand|Select|Wrap|Line Numbers
  1.    FY = InputBox("Financial Year starting:-  Jul, ", "Enter Financial Year") 
  2.    st = "DateIn > #" & CStr(FY) & "/06/30# and DateIn < #" & CStr(FY + 1) & "/07/01#" 
  3.    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
Aug 13 '12 #2

Rabbit
Expert Mod 10K+
P: 12,365
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.
Aug 13 '12 #3

Expert 100+
P: 634
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
Aug 13 '12 #4

P: 9
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
Aug 13 '12 #5

P: 9
Hi all,

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

Expand|Select|Wrap|Line Numbers
  1.   Do While Not rsDiv.EOF
  2.      If Month(rsDiv!DateIn) > 6 And Year(rsDiv!DateIn) = FY or Month(rsDiv!DateIn) < 7 And Year(rsDiv!DateIn) = FY+1 Then
  3. '   do stuff here
  4.  
many thanks to all who replied.
cheers
Phil
Aug 14 '12 #6

Expert 5K+
P: 8,434
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 misinterpretation 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.
Aug 14 '12 #7

P: 9
Good point, thank you.
Aug 14 '12 #8

Expert 100+
P: 634
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)
Expand|Select|Wrap|Line Numbers
  1. Sub TestFilter()
  2.     Dim cn As ADODB.Connection
  3.     Dim rs As ADODB.Recordset
  4.  
  5.     Dim Yr As String
  6.     Dim dFromDate As Date
  7.     Dim dToDate As Date
  8.     Dim strFilter As String
  9.  
  10.     Set cn = New ADODB.Connection
  11.     cn.Open "File Name=H:\My Data Sources\CES Timesheets DB DEV.udl"
  12.  
  13.     Set rs = New ADODB.Recordset
  14.     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
  15.  
  16.     MsgBox rs("WE_Date") & " : " & rs.RecordCount
  17.  
  18.     Yr = "12"
  19.     dFromDate = DateSerial(Yr, 6, 1)
  20.     dToDate = DateSerial(Yr + 1, 5, 30)
  21.     strFilter = "WE_Date >= #" & dFromDate & "# AND WE_Date <= #" & dToDate & "#"
  22.  
  23.     MsgBox strFilter
  24.  
  25.     rs.Filter = strFilter
  26.  
  27.     If rs.EOF Then
  28.         MsgBox "No Date found (" & dFromDate & ")"
  29.     Else
  30.         MsgBox rs("WE_Date") & " : " & rs.RecordCount
  31.     End If
  32.  
  33.     rs.Filter = ""
  34.     MsgBox rs("WE_Date") & " : " & rs.RecordCount
  35.  
  36.     rs.Close
  37.     cn.Close
  38.     Set rs = Nothing
  39.     Set cn = Nothing
  40.  
  41. 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
Aug 14 '12 #9

Expert 5K+
P: 8,434
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.
Aug 15 '12 #10

P: 9
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
Aug 15 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.