Filter Subforms via Main Form Date Range Entry

[KLahvic01]

I have a form that has a subform which is based on a query, not linked to the main form. Here is the query:

SELECT tblSalesDetail.SaleDetailID, tblSalesDetail.SalesPrice, tblSalesDetail.PostedVouchers, tblSalesDetail.DateTimeStamp
FROM tblSalesDetail
WHERE (((tblSalesDetail.PostedVouchers)=False));

What I want to have happen is on two unbound text boxes allow users to enter a date range and have the subform filtered to only show records of the date range entered in the main form boxes.

Main Form: VoucherMain
SubForm: VoucherDetail Subform
Field in Subform to Filter: DateTimeStamp Default Value: =Date()
Fields in Main Form used for Range: BeginDate & EndDate
Button to Apply Filter on Main Form: FilterTrans

I have searched for a working solution for my issue to no avail, so any help is appreciated.

Thanks,

KJ

 

[vbaInet]

Use the subform's Filter property like this:

With Me.[COLOR=Red]SubformControlName[/COLOR].Form
    .Filter = "[[COLOR=Red]DateField[/COLOR]] BETWEEN #" & Me.[COLOR=Red]BeginDate [/COLOR]& "# AND #" & Me.[COLOR=Red]EndDate [/COLOR]& "#"
    .FilterOn = True
End With

That code will go in the Click event of the button that should fire the code. Amend the red parts.

 

[KLahvic01]

Thank you for the quick reply, but now that I have entered this code in the click event, I get the following error:

Syntax error in date in query expresssion '[DateTimeStamp] BETWEEN
#08/15/2012# AND #'.

???

Thanks,

KJ

 

[vbaInet]

You can't leave the EndDate textbox blank. And if you did enter something, you need to move the cursor away from the textbox for the value to be set.

 

[KLahvic01]

Awesome, that worked, I have one more question, is there something to change the code to include the end date in teh results. As it is right now, it only offers the dates in between the range but I would like it to include the end date too.

 

[vbaInet]

It's an inclusive criteria so the start and end dates should be included in the returned dataset.

 

[KLahvic01]

Ok, so if I enter begin date of 08/15/2012 and an end date of 08/17/2012, I should receive records from 08/15, 08/16, and 08/17, right. When I enter it here, I do not receive 08/17/2012, only the dates prior. I would like it to return the end date as well.

 

[vbaInet]

Alright, let's format your dates:

.Filter = "[[COLOR=Red]DateField[/COLOR]] BETWEEN " & Format(Me.[COLOR=Red]BeginDate[/COLOR], "\#mm\/dd\/yyyy\#") & " AND " & Format(Me.[COLOR=Red]EndDate[/COLOR], "\#mm\/dd\/yyyy\#")

 

[KLahvic01]

Still doesnt include the end date...

I mean, it makes sense because 'between' in my opinion means just that...between

I dont know, I suppose it is just a minimal detail that can be lived with.

 

[vbaInet]

That's how it is in English, but this isn't English. This is programming. BETWEEN is inclusive.

Since you're filtering a subform, your subform must be linked to your parent form and those set of records do not include that date or there must be another filter set somewhere else.