Hep with querying between two dates

  • Thread starter Thread starter GMan
  • Start date Start date
G

GMan

I'm an Access neophyte! I am working on learning but I have also inherited
many databases and I need to learn as I go.

I have a table that contains LastName, FirstName, Member Number, DateAdded,
DateCompleted, DateRemoved.

I want a form that allows the user to specify any date range and, once acted
on, will display all records where either date completed or removed falls
within the range specified. In the footer, I want to total the number of
records that have DateRemoved and DateCompleted

Thanks
 
Try this in the query for the form --
SELECT LastName, FirstName, Member Number, DateAdded, DateCompleted,
DateRemoved
FROM YourTable
WHERE (DateCompleted Between [Enter start date] And [Enter end date]) Or
(DateRemoved Between [Enter start date] And [Enter end date]);

In a textbox in the footer put =Count(DateRemoved) and in another
textbox put =Count(DateCompleted)
 
GMan,
There are several ways to do that... let's try a "parameter" query.
Say that the table you refer to is named tblTransactions.
Use a query with all the fields from your tblTransactions as the
RecordSource for your form... instead of the table itself.
Using the query design grid, in the Completed and Removed columns...
Completed Removed
Between [Start] and [End]
Between [Start] and [End]

When the form opens, because Access does not know what the [Start] or
[End] values are, it will prompt you for their values. You will eneter a
Start Date and End Date, and only records matching that criteria will be
returned by the form's recordset.
**Notice... that in the query, the Completed criteria is on the first line
of the Completed query criteria, and the Removed criteria is on the second
line of the Removed criteria. This establishes an OR criteria bewteen the
two values.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Al and Carl Both

I just didn't know I could create an OR relationship between two different
fields like that. I had used it to show multiple criteria in a single field,
but not for DIFFERENT fields. Cool.

OK. The Query works. Great. Thanks a lot.

Next, I want a form that shows all information and the ability to specify
Start and End in the Form, not the query. It would be nice if the form first
showed all information and had a place to specify start and end date.

On the form, would I create an unbound Box Named Start and another named
End, and a button that sees that information? What would the On Click of the
Button look like? Am I on the right track. I got the query to work. Now I
just want to move that query criteria to the form



WHERE (DateCompleted Between [Start] And [End]) Or
(DateRemoved Between [Start] And [End]);

Al Campagna said:
GMan,
There are several ways to do that... let's try a "parameter" query.
Say that the table you refer to is named tblTransactions.
Use a query with all the fields from your tblTransactions as the
RecordSource for your form... instead of the table itself.
Using the query design grid, in the Completed and Removed columns...
Completed Removed
Between [Start] and [End]
Between [Start] and [End]

When the form opens, because Access does not know what the [Start] or
[End] values are, it will prompt you for their values. You will eneter a
Start Date and End Date, and only records matching that criteria will be
returned by the form's recordset.
**Notice... that in the query, the Completed criteria is on the first line
of the Completed query criteria, and the Removed criteria is on the second
line of the Removed criteria. This establishes an OR criteria bewteen the
two values.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."



GMan said:
I'm an Access neophyte! I am working on learning but I have also
inherited
many databases and I need to learn as I go.

I have a table that contains LastName, FirstName, Member Number,
DateAdded,
DateCompleted, DateRemoved.

I want a form that allows the user to specify any date range and, once
acted
on, will display all records where either date completed or removed falls
within the range specified. In the footer, I want to total the number of
records that have DateRemoved and DateCompleted

Thanks
Click to expand...
Click to expand...
 
Al and Carl Both

I just didn't know I could create an OR relationship between two different
fields like that. I had used it to show multiple criteria in a single field,
but not for DIFFERENT fields. Cool.

OK. The Query works. Great. Thanks a lot.

Next, I want a form that shows all information and the ability to specify
Start and End in the Form, not the query. It would be nice if the form first
showed all information and had a place to specify start and end date.

On the form, would I create an unbound Box Named Start and another named
End, and a button that sees that information? What would the On Click of the
Button look like? Am I on the right track. I got the query to work. Now I
just want to move that query criteria to the form
Click to expand...

If you're using controls on the same form, you might find it easier to use the
Form's Filter property. Do put two textboxes named txtStart and txtEnd on the
form; you don't need a button though. Instead put code in the AfterUpdate
event of the two unbound textboxes:

Private Sub txtStart_AfterUpdate()
Dim strFilter As String
If Not IsNull(Me!txtStart) Then
strFilter = "[datefield] >= #" & Format(Me!txtStart, "mm/dd/yyyy") & "#"
If Not IsNull(Me!txtEnd) Then
strFilter = strFilter & " AND [datefield] <= #" & _
Format(Me!txtEnd, "mm/dd/yyyy") & "#"
End If
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
End Sub

Untested air code, may need some tweaking.
 
Back
Top