Open and Filter

[lightkeepr]

What VBA code would I use if I wanted to open a table called 'Payroll' and filter the field 'DateWorked' using dates stored in another table [SEDates].[StartDate] and [SEDates].[EndDate]?

I have tried the following:

DoCmd.OpenTable "Payroll"
DoCmd.SetFilter , [Payroll].[DateWorked] = "Between [SEDates].[StartDate] And [SEDates].[EndDate]"

and when I exectute the code, it pops a box asking for the values for [SEDates].[StartDate] and then [SEDates].[EndDate]

 

[Cronk]

Um, how does the code "know" what start and end dates are to be used in your filter?

That's why the input boxes appear.

 

[lightkeepr]

I have never done something like this before. I presume by your comment I need to store the values from [SEDates].[StartDate] and [SEDates].[EndDate] in the VBA code and use them instead of a reference to the actual table? If that is the case, how do I get the values to be fetched from the tables? Or what is this called so I can go figure it out on my own?

 

[Cronk]

Take an example. Your [Payroll].[DateWorked] field has dates from 1 Jan 2000 to today.

You seem to be wanting to see a subset of these dates. Say the users wants dates between 1 July 2012 to 30 June 2013 today and tomorrow wants dates between 1 April 2010 and 31 March 2011.

Where are these dates being input?

As before how does the code "know" what start and end dates are to be used in your filter?

 

[lightkeepr]

My ignorance shows my skill level, I apologize I didn't explain this further to start with. In the SEDates table there is two fields, StartDate and EndDate. There is only one record in the table that is changed as needed. I refer back to this set of dates many times during the bi-weekly use of the database so i created a table to hold the values so I didn't have to keep entering them for reports, etc.

 

[Cronk]

I understand now.

The code has to 'read' the values stored in the tables.

The DLookup function can do this.

Search Help and internet for syntax and examples. Come back if you can't get your filter to work.

 

[missinglinq]

...I presume by your comment I need to store the values...instead of a reference to the actual table...

No, you cannot reference a Table/Field like this in VBA code. As Cronk said, you can retrieve these Fields using the DLookUp Function. Since the Table only has a single Record, the syntax is quite simple:

vStartDate = DLookUp("StartDate", "SEDates")

vEndDate = DLookUp("EndDate", "SEDates")

But to be honest, experienced developers will tell you that doing this by working directly with a Table, like you're doing with "PayRoll," is considered to be a very poor practice. Doing so increases chance of data corruption.

You'd be much better off doing this sort of thing via a Query.

Linq ;0)>

 

[lightkeepr]

Okay, I have figured out how to use the DLookup feature (thank you greatly for the direction on that!) and that part is working correct. When I run the following code now, the correct table opens up and it shows it is being filtered but no data in the date range specified shows up. Where do I need to look next for what is causing this problem?



Dim SDate As Date
Dim EDate As Date
Dim strfilter As String

SDate = DLookup("StartDate", "SEDates", "ID=1")
EDate = DLookup("EndDate", "SEDates", "ID=1")

DoCmd.OpenTable "Payroll"
strfilter = "[DateWorked] Between " & SDate & " And " & EDate & ""
DoCmd.ApplyFilter , strfilter

 

[lightkeepr]

No, you cannot reference a Table/Field like this in VBA code. As Cronk said, you can retrieve these Fields using the DLookUp Function. Since the Table only has a single Record, the syntax is quite simple:

vStartDate = DLookUp("StartDate", "SEDates")

vEndDate = DLookUp("EndDate", "SEDates")

But to be honest, experienced developers will tell you that doing this by working directly with a Table, like you're doing with "PayRoll," is considered to be a very poor practice. Doing so increases chance of data corruption.

You'd be much better off doing this sort of thing via a Query.

Linq ;0)>

I understand what you are saying and I had thought about doing a query to control the data easier but I could not edit the data from the query. I searched around looking for a reason as to why the query behaved this way and couldn't find a reason since I don't know the right terms to find the correct response. (Kind of like the DLookup issue.)

 

[lightkeepr]

Okay, I have figured out how to use the DLookup feature (thank you greatly for the direction on that!) and that part is working correct. When I run the following code now, the correct table opens up and it shows it is being filtered but no data in the date range specified shows up. Where do I need to look next for what is causing this problem?


Dim SDate As Date
Dim EDate As Date
Dim strfilter As String

SDate = DLookup("StartDate", "SEDates", "ID=1")
EDate = DLookup("EndDate", "SEDates", "ID=1")

DoCmd.OpenTable "Payroll"
strfilter = "[DateWorked] Between " & SDate & " And " & EDate & ""
DoCmd.ApplyFilter , strfilter

 

[Cronk]

Good that you have the DLookup under control.

Now the next two issues. Firstly VBA is UScentric. Dates need to be in month day year order. Secondly, the filter needs to have cross hatches to designate the values as dates.

So your filter string needs to be something like
DateWorked Between #09/01/2013# And #09/30/2013#

ie strfilter = "[DateWorked] Between " & format(SDate,"mm/dd/yyyy") & "# And #" & EDate & "#"

Add the line below to show the filter about to be applied
Debug.print strFilter

 

[CJ_London]

Justy out of curiosity - why are you opening a table and not using a query?