Filter based on the date range

[dwight1]

Hi,

I want to filter records based on the date range by setting a parameter.

I tried doing

=between @fieldname and @fieldname.

Any suggestions

Dwight

 

[cmmrfrds]

Is this from a Form? an Sproc? Dynamic SQL? Other? What have you done so far?

 

[dwight1]

Cmmrfrds,

This is based on SP. I have different fields one among them is the date field. I want to set the parameters so that the user would input the start and end date and he would get the records for that specified date range.

Hope this clarifies.

Dwight

 

[cmmrfrds]

Yes, but depending on how you are call the SP the parameters will be loaded differently. A Form or Report has an Input Parameters Property. Executing from vba code you could load the parameters with a Command Object, or you could build then in dynamic SQL.

 

[dwight1]

Cmmrfrds,

I am sorry, I was not clear enough.

Actually, I just want the user to input the date parameter. So i went ahead and placed under criteria of the sp >@date.

This would pop up the field to input the date (greater than) by the user.

If the user inputs 09/01/2005 it generates all the records greater than that date.

This all works fine.

I want to provide range in sp such as
between @>date and @<date. How to do this?

Hope this helps.

Dwight

 

[cmmrfrds]

The way I handle parameters is to put them as public variables in the standard module and have the user enter the variables on a Form.

In standard module.
Public pubBegDate as Date
Public pubEndDate as Date

Public Function ReturnBegDate() as Date
ReturnBegDate = pubBegDate
End Function
Public Function ReturnEndDate() as Date
ReturnEndDate = pubEndDate
End Function

On Form where user enters dates.
Put a Button and use OnClick event.

In OnClick event after user enters the dates.
pubBegDate = textboxBegDate
pubEndDate = textboxEndDate

Dim DocName As String
Dim LinkCriteria As String

DocName = "MyForm"
DoCmd.OpenForm DocName, , , LinkCriteria

Under the data tab of myform
Record Source = mysproc
Input Parameters =
@begdate=ReturnBegDate(),@enddate=ReturnEndDate()

The names @begdate and @enddate are the parameter names in the Sproc.

 

[dwight1]

Thank you .