Filter Querry on Subform using calender control 
Author Message
 Filter Querry on Subform using calender control

I have a form which has the calender control and a subform which is
bound to a querry. There is also a command button that when pressed
takes the date value from the calender and filters the subform to show
all records after that date.

I read in a book where the code should be something like this:

Private Sub cmdOrders_Click()
special.subform.RecordSource = _
        "Select * from qryforsearchf Where Date = #" _
        & calender0.Value & "#"
End sub

In their example they wanted records = to that date whereas I wanted
after that date.

Anyone have any suggestions? I can't get it to work.



Mon, 07 Feb 2005 00:28:04 GMT  
 Filter Querry on Subform using calender control


Quote:
> Private Sub cmdOrders_Click()
> special.subform.RecordSource = _
>         "Select * from qryforsearchf Where Date = #" _
>         & calender0.Value & "#"
> End sub

> In their example they wanted records = to that date whereas I wanted
> after that date.

Change the comparison operator to Greater_Than:

  WHERE DateValue(DateBegin) > CDate(Calendar0.Value)

A couple of points: please don't call column names "Date" as you or VBA
_will_ get confused between the VB function Date() and the SQL data type
DATE.

Second, remember that any time values that slip into the date field will
invalidate equalities, so it's helpful to use DateValue to make sure you're
only dealing with midnight values.

Finally, I can't remember what datatype the Calendar.Value returns, but
simply casting it into a string with "#" & .Value & "#" will get you into
some very hard-to-track bugs if you move out of the US mm/dd/yyyy area.
Using an explicit CDate will get round that completely safely.

HTH

Tim F



Mon, 07 Feb 2005 03:28:48 GMT  
 Filter Querry on Subform using calender control
Thank you for your help. I am a beginner at VBA. I've been reading
books trying to learn. Thanks for the pointers
Quote:



> > Private Sub cmdOrders_Click()
> > special.subform.RecordSource = _
> >         "Select * from qryforsearchf Where Date = #" _
> >         & calender0.Value & "#"
> > End sub

> > In their example they wanted records = to that date whereas I wanted
> > after that date.

> Change the comparison operator to Greater_Than:

>   WHERE DateValue(DateBegin) > CDate(Calendar0.Value)

> A couple of points: please don't call column names "Date" as you or VBA
> _will_ get confused between the VB function Date() and the SQL data type
> DATE.

> Second, remember that any time values that slip into the date field will
> invalidate equalities, so it's helpful to use DateValue to make sure you're
> only dealing with midnight values.

> Finally, I can't remember what datatype the Calendar.Value returns, but
> simply casting it into a string with "#" & .Value & "#" will get you into
> some very hard-to-track bugs if you move out of the US mm/dd/yyyy area.
> Using an explicit CDate will get round that completely safely.

> HTH

> Tim F



Mon, 07 Feb 2005 22:17:51 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Answer Paramater Querry Using Calender Control

2. Applying a filter on a form filters subforms, too

3. Filter By Form Using Subform Fields

4. How to create the calender in MDI form without using vbBuilt in Calender Function

5. Help: saving filter as form to existing querry

6. Using a main form to control the edit properties of a subform using VBA

7. Filtering in Outlook calender

8. Using the calender control

9. Control name of subform from code behind subform

10. Setting the Filter property on subform

11. Form goes to 1st record when filter applied to subform

12. filter subforms

 

 
Powered by phpBB® Forum Software